Grouping In Queries

[mcongdon]

Hi all,
I think this is a pretty easy question. I've looked in a couple of beginner books and online and I can't quite find what I'm looking for.

I have a table tblBlotter. In this table I have fields RepName, Quantity, Comission, Date. Basically, I eventually want to create a report that will tell me every month what sales each sales representative made as commissions. tblBlotter has an entry for every sale each representative makes. I only want one summary line. For example, tblBlotter might contain the following:

RepName Quantity Commission Date
A36 40 40.00 12/26/2008
A36 10 10.00 12/30/2008
YZ4 90 90.00 12/31/2008
YZ4 10 10.00 12/31/2008

I want to make a query that i can create a report like this

December 2008
Representative A36
Quantity 50
Commission 50.00

Representative YZ4
Quantity 100
Commission 100

I can get the total numbers for the whole month, I just don't know how to tell it to group it by the representatives that made sales in that month. Any help would be greatly appreciated.
Thanks!

 

[dhookom]

This is generally done with both a totals query like:

SELECT RepName, DateSerial(Year([Date]),Month([Date]),1) as YrMth1, Sum(Quantity) as QtySum, Sum(Commission) as ComSum
FROM tblBlotter
GROUP BY RepName, DateSerial(Year([Date]),Month([Date]),1);

Then, in your report, set the first Sorting and Grouping level to YrMth1 with a Group Header. Then sort next by RepName with a group header. Place the QtySum and ComSum in the detail section of the report.


Duane
Hook'D on Access
MS Access MVP