[AccessD] Union Query Solved

I solved my own problem.  For those who want to know...Create a wrapper/subquery.  I do them in Oracle but couldn't figure out why they wouldn't work in SQL Server until now.  SQL Server requires a table name where Oracle does not.  I guess owning a SQL Server book would be helpful after all.

-Paul

Select
t1.GrpId,
Sum(t1.Bills) Bills
from
(
select
t1.GrpId GrpId,
0 Bills
from
tblOpusGrp t1
where
t1.Grp = 'WtBreak'
union
select
t1.WtBreak GrpId,
Count(*) Bills
from
tblOpus1 t1
where
t1.CustNum = '84304389'
group by
t1.WtBreak
) t1
group by 
t1.GrpId

1	5
2	3
3	1
4	0
5	1

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Millard, Paul
--- Sr. Developer Analyst ---WGO
Sent: Wednesday, January 21, 2004 2:56 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Union Query


Hi

I'm having trouble merging two queries together in a union query.  The reason for developing query is I'm trying to force a group to show 5 items listed 1 through 5 and their record count and force a 0 when the group doesn't below in the data table (table2).  Below is the query, query result an desired result along with two tables.  The union I'm trying to perform right now does not merge together which I need.

Any help would greatly be appreciated.
Paul Millard
San Jose, CA


select
t1.GrpId GrpId,
0 Bills
from
tblOpusGrp t1
where
t1.Grp = 'WtBreak'
union
select
t1.WtBreak GrpId,
Count(*) Bills
from
tblOpus1 t1
where
t1.CustNum = '84304389'
group by
t1.WtBreak

query result.
1	0
1	5
2	0
2	3
3	0
3	1
4	0
5	0
5	1

desired query result.
1	5
2	3
3	1
4	0
5	1

table 1
Grp		GrpId	GrpDesc
WtBreak	1	L5C
WtBreak	2	M5C
WtBreak	3	M1M
WtBreak	4	M2M
WtBreak	5	M5M


table 2
WtBreak	Count
1		5
2		3
3		1
5		1

table 2 detail
Wt	WtBreak
908.0	2
629.0	2
137.0	1
224.0	1
11800.0	5
1610.0	3
379.0	1
258.0	1
793.0	2
192.0	1




**********************************************************
This message contains information that is confidential
and proprietary to FedEx Freight or its affiliates.
It is intended only for the recipient named and for
the express purpose(s) described therein.
Any other use is prohibited.
****************************************************************

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com