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.

Code:

           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.

Mitch

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.

HTH

mmcdonal

leehambly February 20th, 2007 10:23 AM

Mitch,

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

If you can get your source data into the format:

Fields:
Agent
Period (MTD, YTD)
Section (Life, Annuities, etc)
Value

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.

HTH

Lee


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.