Month and Year to Date on a Report

[mistux]

I need a Report that shows the Month To Date and the Year To Date per each Category at the top and along the left all of the individual people.


Life Annuities Securities Group Total
Agent MTD YTD MTD YTD MTD YTD MTD YTD MTD YTD
Bob 25 50 0 0 10 30 0 0 35 80
Jim 0 50 10 10 15 45 100 110 125 215
Totals 25 100 10 10 25 75 100 110 160 295

I have attached my database as well as a jpg that shows exactly what report layout I want. The database will open the correct form at startup.
I have made two seperate queries, one for MTD and YTD but I have not been able to "merge" them to make the report that I need.
I even tried the Crosstab wizzard but that did not work for me either.

[OBP]

mistux, I can see from your Format that this might be hard to achieve, it may actually require the use of subreports or a Temporary Table, I will have a look at your database and let you know.

[OBP]

mistux, I can't open the jpg, in fact it crashes my computer, I will have to go on the layot that you show in your post, unless you can post an Excel representation of the report?

[OBP]

You can do it like this, have a query that combines the output of the MTD Group Query and YTD Group Query.
Then Have a Query/Sub Report for each CatID group.
Pull them together on to one report.
I have done 1 query/subreport on the main report (not formatted) to show how it done.
The other subreports will not need the Agent's Names.

[mistux]

Thanks, I will have a look.