I need something like an aggregrate union query?

[RandV]

I'm trying to combine some sales and purchase information into a single table grouped by item code. On both the sales and purchase side there is a Header table and a Detail table, all the info I want is in Detail but I need to join to the Header so I can filter by date. I want to combine the sales and purchase data into a single line according to item code, so I've been using a UNION query. A simplified version of the query looks like this:




P/S = Purchase/Sale
D/H = Detail/Header

SELECT P_D.ITEM as [ITEM], SUM(P_D.ORDQTY) as [P_QTY], SUM(0) AS [S_QTY]
FROM P_H INNER JOIN P_D ON P_H.P_NUMBER = P_D.P_NUMBER
WHERE P_H.P_DATE Between [Start Date] And [End Date]
GROUP BY P_D.CODE
UNION
SELECT S_D.ITEM as [ITEM], SUM(0) as [P_QTY], SUM(S_D.ORDQTY) as [S_QTY]
FROM S_H INNER JOIN S_D ON S_H.P_NUMBER = S_D.P_NUMBER
WHERE S_H.S_DATE Between [Start Date] And [End Date]
GROUP BY S_D.CODE;

The result I want would look like this:

ITEM	P_QTY	S_QTY
Item1	10	5
Item2	20	12

But the results I'm getting is:

ITEM	P_QTY	S_QTY
Item1	10	0
Item1	0	5
Item2	20	0
Item2	0	12

I can understand why I'm getting those results and I'm sure I've done this before but I can't quite recall how to get the results I want.

Any help would be appreciated, thanks.

[June7]

You have to do an aggregation of the UNION query:

SELECT Item, Sum(P_Qty) AS SumP, Sum(S_Qty) AS SumS FROM

(SELECT P_D.ITEM as [ITEM], P_D.ORDQTY as [P_QTY], 0 AS [S_QTY]
FROM P_H INNER JOIN P_D ON P_H.P_NUMBER = P_D.P_NUMBER
WHERE P_H.P_DATE Between [Start Date] And [End Date]
UNION
SELECT S_D.ITEM as [ITEM], 0 As [P_QTY], S_D.ORDQTY as [S_QTY]
FROM S_H INNER JOIN S_D ON S_H.P_NUMBER = S_D.P_NUMBER
WHERE S_H.S_DATE Between [Start Date] And [End Date])

GROUP BY Item;

Option:
Need a master table of all Items.
Build two aggregate queries then join those queries to the master.

[RandV]

You have to do an aggregation of the UNION query:

SELECT Item, Sum(P_Qty) AS SumP, Sum(S_Qty) AS SumS FROM

(SELECT P_D.ITEM as [ITEM], P_D.ORDQTY as [P_QTY], 0 AS [S_QTY]
FROM P_H INNER JOIN P_D ON P_H.P_NUMBER = P_D.P_NUMBER
WHERE P_H.P_DATE Between [Start Date] And [End Date]
UNION
SELECT S_D.ITEM as [ITEM], 0 As [P_QTY], S_D.ORDQTY as [S_QTY]
FROM S_H INNER JOIN S_D ON S_H.P_NUMBER = S_D.P_NUMBER
WHERE S_H.S_DATE Between [Start Date] And [End Date])

GROUP BY Item;

Option:
Need a master table of all Items.
Build two aggregate queries then join those queries to the master.

Okay so you can do that, figured it was something simple. This brings me to another question dealing with syntax. My code example is simplified for clarity, getting closer to the full messy code lets say what I'm selecting a few other fields one of them being for ITEM DESCRIPTION where the column name is "PHD_DESCRIPTION" for the purchase side and "SHD_DESCRIPTION" for the sales side.

SELECT P_D.PHD_DESCRIPTION as [DESCRIPTION], ...
UNION
SELECT S_D.SHD_DESCRIPTION as [DESCRIPTION]...

GROUP BY [DESCRIPTION];

And this gives me the error "You tried to execute a query that does not include the specified expression 'DESCRIPTION' as part of an aggregate function".

I haven't done Access for a while so I'm a little rusty, but if I rename the two aligned columns with as [<name>] then can I not use [<name>] in the GROUP BY? This was the reason I had my GROUP BY's in the single queries before and not the union.

[June7]

If you grouped each of the individual SELECT subqueries as you did in the original posted example, should work. My example does not aggregate the subqueries, only the outer query. Don't forget the parens wrapping the inner query.

[RandV]

[edit]

Oops I didn't look at how you wrapped you're example. Also needed to add the GROUP BY in the wrapped SELECT, but otherwise I got it working. Thanks!