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 tried to attach my database as well as a jpg that shows exactly
what report layout I want, but there is not upload feature.

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.

 

[George Nicholson]

This can be done, but its a tricky operation to learn. Trickyer to explain.

Take a look at
http://msdn2.microsoft.com/en-us/library/aa140083(office.10).aspx
Use UNION queries to combine dissimilar data into single fields.

I realize that your data is not that dissimilar, and your needs are not
exactly like those shown, but i think the fundamentals you need are
presented.

1) Create separate MTD and YTD crosstabs with Life, Annuties, etc across the
top.
2) build a UNION query that combines those 2 crosstabs (note: # of fields
must match, even if a field is an empty 'placeholder'). Remember that field
names in a UNION query are governed by the names in the first SELECT.

2) be sure to include a "flag" field in the UNION so that you can tell if a
given record is MTD data & or YTD data.

3) Your report will have a lot of textboxes with "Iif([Flag] = "YTD",
SomeValue,0)" controlsources. This will be a manual, laborious process. No
way around that.

4) (Not stated in article because it's data is different) You will probably
need to set up your "Bob", "Jim" lines as group footers that show the totals
of the *hidden* Detail section. (Example report, you probably only want to
see the "Total" line for John Doe, not the detail)

<sigh> Too bad the article publisher is such an obnoxious marketer that I
refuse to do business with them any more. <sigh> Too bad they also own Smart
Access now.

HTH,

 

[Marshall Barton]

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 tried to attach my database as well as a jpg that shows exactly
what report layout I want, but there is not upload feature.

I have made two seperate queries, one for MTD and YTD but I have not

Create another query that Joins those two queries:

SELECT M. Agent,
M.Life, Y.Life,
M.Annuities, Y.Annuities,
. . .
FROM qry MTD As M INNER JOIN qryYTD As D
ON M.Agent = Y.Agent