Report Query Question

[Guest]

I'm creating a report that requires to do Sum & Count for the same
record. I wrote this query, which should union the matching columns
into 1. I've done this all the time in SQL Server, but I'm getting
different output from the Access report query.

Select Name, Count(howmany) as col1, Sum(howmuch) as col2, 0 as col3, 0
as col4
from mytable
where status = 'A'
group by name
union all
Select Name, 0 as col1, 0 as col2, Count(howmany) as col3, Sum(howmuch)
as col4
from mytable
where status = 'B'
group by name


The query does not merge records for the same name, but it show 2
records for the same name instead...

 

[Pat Hartman\(MVP\)]

This isn't a situation where you want a union. It is doing exactly as you
asked which is to make a list of all unique rows. You can run two totals
queries and join them on name.