union query? 2

[Pampers]

Hi everyone,
I'm not sure how to do this...
Say, I have two tables. tblRevenue2007 and tblRevenue2006. Both table have the same columns, CustomerName and Sales. I want to run a query that gives me all the CustomerNames (from both tables) and SumSales2006 and SumSales2007. I tried a union query, but then I cannot discriminate between SumSales2006 and SumSales2007. A left or right join will leave out customers from either table. So how to proceed?

Pampers
Keeping it simple can be complicated

 

[SgtJarrow]

Maybe (Typed, Not Tested):

SELECT
    CustomerName,
    Sum(Sales),
    '2006'
FROM tblRevenue2006
GROUP BY CustomerName

UNION ALL

SELECT
    CustomerName,
    Sum(Sales),
    '2007'
FROM tblRevenue2007
GROUP BY CustomerName

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Pampers]

Tnx mstrmage1768,
I'm gonna give it try. Keep you posted...

Pampers
Keeping it simple can be complicated

 

[Pampers]

Well, that almost did the trick.
I get three columns:
Naam (Customer)
Expr1001: with the totals of the sales (Factuurbedrag)
Expr1002: with the string '2007' in it..

here is my query

SELECT selqryOmzetANG.NAAM, Sum(selqryOmzetANG.FACTUURBEDRAG),'2007'
FROM selqryOmzetANG
GROUP BY selqryOmzetANG.NAAM

UNION ALL

SELECT selqryOmzetANG2006.NAAM, Sum(selqryOmzetANG2006.FACTUURBEDRAG),'2006'
FROM selqryOmzetANG2006
GROUP BY selqryOmzetANG2006.NAAM;

Pampers
Keeping it simple can be complicated

 

[SgtJarrow]

Are you wanting output like this???

NAAM        2006Revenue      2007Revenue
1234        xxxxx            yyyyyy
5678        rrrrr            ssssss
...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Pampers]

yep, that is right.

Pampers
Keeping it simple can be complicated

 

[PHV]

And what about this ?
SELECT U.NAAM, Sum(FACTUURBEDRAG2006) AS [2006Revenue], Sum(FACTUURBEDRAG2007) AS [2007Revenue]
FROM (
SELECT NAAM, 0 AS FACTUURBEDRAG2006, FACTUURBEDRAG AS FACTUURBEDRAG2007 FROM selqryOmzetANG
UNION ALL SELECT NAAM, FACTUURBEDRAG, 0 FROM selqryOmzetANG2006
) AS U
GROUP BY U.NAAM;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Pampers]

Tnx PHV,
I will give it try. Keep you posted.

Pampers
Keeping it simple can be complicated

 

[SgtJarrow]

Or my sample - but I think PHV's is more efficient:

SELECT [tbl2006].onum As "CustID", Sum([tbl2006].hours) AS SumOfhours, Sum([tbl2007].hours) AS SumOfhours1
FROM tbl2006 LEFT JOIN tbl2007 ON [tbl2006].onum = [tbl2007].onum
GROUP BY [tbl2006].onum;

UNION SELECT [tbl2007].onum, Sum([tbl2006].hours) AS SumOfhours, Sum([tbl2007].hours) AS SumOfhours1
FROM tbl2007 LEFT JOIN tbl2006 ON [tbl2006].onum = [tbl2007].onum
GROUP BY [tbl2007].onum;

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Pampers]

Works great PHV.
Mstrmage1768, I haven't put it to the test, yet... I will try later on.

Pampers
Keeping it simple can be complicated

 

[yan19454]

I tried that. It did not work. do not know why ? I do not know whether it need to write function to pass the value of selqryOmzetANG.NAAM

SELECT selqryOmzetANG.NAAM, Sum(selqryOmzetANG.FACTUURBEDRAG), functionA (selqryOmzetANG.NAAM)
FROM selqryOmzetANG
GROUP BY selqryOmzetANG.NAAM


function :

SELECT Sum(selqryOmzetANG2006.FACTUURBEDRAG)
FROM selqryOmzetANG2006 GROUP BY selqryOmzetANG2006.NAAM

having selqryOmzetANG2006.NAAM ='selqryOmzetANG.NAAM'