Query Question 2

[Omnillas]

I have an instance of two tables that I would like to join some resulting counts by date:

Table A
-----------------
entry id date part #
-------- ------ ------
1 1/1/04 1
2 1/1/04 1
3 1/1/04 2
4 1/2/04 1
. . .
. . .

Table B
-----------------
entry id date part #
-------- ------ ------
1 1/1/04 2
2 1/1/04 1
3 1/1/04 3
4 1/2/04 2
. . .
. . .

What I would like to do is generate a combined table of statistics for a report that would have the form:

Combined Table
--------------------------------------
date cnt of part 1 sold on date cnt of ttl parts sold
------ -------------------------- ---------------------
1/1/04 4 6
1/2/04 0 2
. . .
. . .

 

[scriverb]

Save the following code as qryUNION_tables:

Select A.Date, A.[part #] 
FROM [Table A] as A 
UNION ALL 
Select B.Date, B.[part #] 
FROM [Table B] as B;

Save the following SQL as qryStatistics:

Select A.Date, Sum(IIF(A.[part #]=1,1,0)) as "cnt of part 1 sold", Sum(1) as "cnt of ttl parts sold" 
FROM qryUNION _tables as A 
GROUP BY A.Date
ORDER BY A.Date;

Run the query qryStatistics and you should get the stats you are looking for. I believe you example has an error in its final counts.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[PHV]

Something like this ?
SELECT A.Date, A.Part1, B.ttl
FROM (SELECT [Date],Sum([part #]) As Part1 FROM [Table A] GROUP BY [Date]) A
INNER JOIN (SELECT [Date],Sum([part #]) As ttl FROM [Table B] GROUP BY [Date]) B
ON A.Date = B.Date;

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