Help creating a Union query 2

[sstump]

I've got two tables with identical column names. I need to create a final doc that shows a list for the 1st column and totals the 2nd column. Some names in column 1 will be duplicated, but I only need them listed once and then totaled in the 2nd column. Here's an example of what I have and what I'm trying to get to.

Table1:
Vendors Customer Reports
Vendor1 18

Table2:
Vendors Customer Reports
Vendor1 8
Vendor2 10
Vendor3 25


Need to come up with this:
Vendors Customer Reports
Vendor1 26
Vendor2 10
Vendor3 25

I was able to created the SQL Statement to build the list of Vendors, however that's where my SQL experience stops and I can't seem to workout how to gather the totals for Custumer Reports.

Any help would be greatly appreciated.

 

[lespaul]

paste this in the SQL view and fix the table names, should give you what you need:

SELECT Vendors, SUM(CustomerREports) FROM (SELECT Vendors, CustomerReports from Table1 UNION SELECT Vendors, CustomerReports FROM Table2) GROUP BY Vendors

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[stephenk1973]

To get the total you'll have to query on you union query, union query will look like...

select vendor, [customer reports] from table1
union
select vendor, [customer reports] from table2
union
select vendor, [customer reports] from table3

select vendor, sum([customer reports]) from qryUnion
group by vendor

Regards
s
tephen

 

[stephenk1973]

Should have pointed out you should be able to make it into one query by treating the union query a bit like a table, though of course query designer find this a bit unpalatable

select vendor, sum([customer reports])
from
(select vendor, [customer reports] from table1
union
select vendor, [customer reports] from table2
union
select vendor, [customer reports] from table3)
group by vendor

Regards

Stephen

 

[sstump]

Both ways worked great! Thanks to both of you.

 

[sstump]

Steph...after doing yours I went back to view the SQL and it changed the text to the follow with [%$##@_Alias] as the table name. I've never seen this before.

SELECT [%$##@_Alias].vendor, Sum([%$##@_Alias].customerreports) AS SumOfcustomerreports
FROM [select vendor, [customerreports] from qryvendor_count_1
union
select vendor, [customerreports] from qryvendor_count_2]. AS [%$##@_Alias]
GROUP BY [%$##@_Alias].vendor;

 

[sstump]

Scratch that...it appears if I enter through design view it populates the Table as [%$##@_Alias], then when I enter SQL and save it replaces the query and fails after that.

 

[PHV]

Try this in the SQL view pane and NEVER return in the buggy query grid for that query:
SELECT U.vendor, Sum(U.customerreports) AS SumOfcustomerreports
FROM (SELECT vendor, customerreports FROM qryvendor_count_1
UNION ALL SELECT vendor, customerreports FROM qryvendor_count_2
) AS U
GROUP BY U.vendor;

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

 

[sstump]

What's that? A Microsoft program that's buggy? That's unheard of!

Thanks again