[AccessD] Union Query

Wed Jan 21 18:08:24 CST 2004

Thanks Bruce and Drew for your responses.  Actually, this is a great way of doing things!

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
bruce_bruen at mlc.com.au
Sent: Wednesday, January 21, 2004 3:36 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Union Query

Suggest this ma be closer to what you are seeking rether than a UNION.

SELECT tblOpusGrp.GrpId, Count (T1.Wt) AS Bills
FROM (select * from tblOpus where custnum="84304389") AS T1 RIGHT JOIN
tblOpusGrp ON T1.WtBreak = tblOpusGrp.GrpId
WHERE (((tblOpusGrp.Grp)="WtBreak"))
GROUP BY tblOpusGrp.GrpId;

rgrds
Bruce

                      "Millard, Paul --- Sr.                                                                                                   
                      Developer Analyst ---WGO"        To:       "Access Developers discussion and problem solving"                            
                      <Paul.Millard at freight.fed         <accessd at databaseadvisors.com>                                                         
                      ex.com>                          cc:                                                                                     
                      Sent by:                         Subject:  [AccessD] Union Query                                                         
                      accessd-bounces at databasea                                                                                                
                      dvisors.com                                                                                                              

                      22/01/2004 09:56                                                                                                         
                      Please respond to Access                                                                                                 
                      Developers discussion and                                                                                                
                      problem solving                                                                                                          

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

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