Reports Grouping Totals in details

[slatterie]

Hi - so glad I found this forum. Hope someone can help. I dont think my issue is all that difficult but I'm not sure of the syntax or what to use.

I am dealing with only one table that contains employee name,hire date, etc. (much like a spreadsheet) Each record in the table is a recording of a sale, so an employee can appear multiple times in this table with different sales dates. i know I should probably have separate tables with an employee list and sales and tie those together, but I am unable to do that.

I have been tasked to put a report of all sales recorded grouped by Month with the most recent record entered first. Easy. Now after each record listed I need to include the total number of sales for the employee. If I group by date then by employee, it will only show the total sales for that employee for the grouped month which makes sense. If I use running sum then it counts the all the records in the table not by employee. However I need the total number of sales for that employee listed right below the record.

so basically i need this:

Jan 2012
Employee A 1/25/12 other detail
Total Employee Sales made = count of record for employee A in table
Employee B 1/12/12 other detail
Total Employee Sales made = count of record for employee B in table Etc....

Dec 2011


Employee B 12/16/11 detail
Total Employee Sales made = count of record for employee B in table
Employee C 12/5/11 detail
etc...

Grand Total of Sales (count of all records - which I have)

How would i do it? Subreport? Fancy iif in properities or SQL statement? Eventually I think I would see this report only showing detail sales for the current or maybe last 3 months but I will still need to provide the overall total count for the employee listed.

Yes I could have a separate summary report that shows the most count of sales by employee but my boss doesnt want to deal with multiple reports - he wants the detail too. He also wants this extremely detailed information crammed onto 8.5 x 11 paper but that is another issue.

Thanks for any feedback.

[aytee111]

If I am understanding you correctly, the second and third lines in your report would have the same total? Your boss is not thinking this through!

However....

Make a query which provides that total - the total by employee.
In the record source for this report, join the table to the query by employee.
Now you can display the total from that query in the employee total fields on every line, regardless of date.