p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   Month and Year to Date on a Report (http://p2p.wrox.com/showthread.php?t=54280)

Mitch February 19th, 2007 06:39 PM

Month and Year to Date on a Report

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

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.


mmcdonal February 20th, 2007 09:06 AM

I think there is a way to merge the queries, but I forgot how. Maybe another poster will see this and show you. If not, I can show you how to code this, OR you can make a table to hold this data instead of using a query to hold it, and try some append queries.



leehambly February 20th, 2007 10:23 AM


Not knowing exactly where your data comes from, I wil try to help...

If you can get your source data into the format:

Period (MTD, YTD)
Section (Life, Annuities, etc)

You should be able to UNION the two sets (ie: MTD and YTD) together. Now put a crosstab on the union to get your output in that format.

Alternatively, you could do a monthly (including the current month to date) report for the current year (to date) and simple remove all but the current month. Year to Date is simply a total field.



All times are GMT -4. The time now is 05:12 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.