[AccessD] Union Query

No need for a Union Query.  You can just use a group query, with a Left
Join.  I may have your table names backwards, but here's the SQL:

SELECT tblOpusGrp.GrpID, Count(tblOpus1.WtBreak) AS CountOfWtBreak
FROM tblOpusGrp LEFT JOIN tblOpus1 ON tblOpusGrp.GrpID = tblOpus1.WtBreak
GROUP BY tblOpusGrp.GrpID;

Drew

-----Original Message-----
From: Millard, Paul --- Sr. Developer Analyst ---WGO
[mailto:Paul.Millard at freight.fedex.com]
Sent: Wednesday, January 21, 2004 4: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