Union Qry

[Corey]

Hello again.

If anyone can help me it would be greatly appreciated.
I’m not sure shy I’m having so much trouble with this, but I’m sure that this will be a great learning opportunity for me.

I’ll explain it two different ways, and that way hopefully it will make sense

First off I’m trying to take all the Companies I work with and get there Net Sales

Example:
Company 1 $1,625.25
Company 2 $6,782.12
ect

I have two queries

The first one is:
qrtySym_Global_ALL_and_SUBFILE_A3

The second one is:
FROM qrtySym_Global_ALL_and_SUBFILE_A4

Below is the Union Query I currently have, and it puts together both queries and shows the totals separately

I would like it to show the Company Name and next to that the Net Sales for that company


SELECT "Total" , Sum(NETSALES)
FROM qrtySym_Global_ALL_and_SUBFILE_A3
union All
SELECT "Total" , Sum(NETSALES)
FROM qrtySym_Global_ALL_and_SUBFILE_A4


Let me see if I can explain it better

The are three columns in the two queries that are the same.

Company Business/ Company Name/Net Sales

I’m trying to take all the

A: Company Business that has the same name from query 1 and query 2 and merge them.

B: Company Name that has the same name from query 1 and query 2 and merge them.

C: Then with the merging of Company Business and the Company Name, Sum the total for the Net Sales.

I really appreciate you helping, so let me know if this doesn’t make sense

Thanks

Corey

 

[uplate]

If I understand what you're trying to accomplish, you need to join your queries, not union them.

Try this:

SELECT Query1.CompanyBiz, Query1.CompanyName, Sum(Query1.Sales) AS Sales1, Sum(Query2.Sales) AS Sales2, [Sales1]+[Sales2] AS TotalSales
FROM Query1 INNER JOIN Query2 ON (Query2.CompanyName = Query1.CompanyName) AND (Query1.CompanyBiz = Query2.CompanyBiz)
GROUP BY Query1.CompanyBiz, Query1.CompanyName;