Previous 12 Month Rolling Query (Attached Files)

[bg18461]

I am trying to create 2 sets of graphs from my database to display in a report. I have 24 months worth of data and I need to create a bar graph that has one series for months 1-12, and a second series for months 13-24. These series should be side by side for each month. Please see the attached image.

My data for each month runs in columns for each customer with the most recent month in the field (MNTH1). So Debit1 to Debit12 would be the first series, and Debit13 to Debit24 will always be the second series. This info gets updated monthly, but the series fields will never change. I already normalized the data and created another query (qryNormalizedByAcct_Rev_Split) to split the months into two fields (each series) but i am having problems writing the query. The report i am using is in qWorkingEachCustQuery which has the graphs.

I will be doing this for the debits (revenue) and energy. If you could help me with one, i should be able to do the other query. Please help!

 

[bg18461]

I figured it out, I added this expression to my query DateDiff("m",[theMonth],Date()) and I made it a WHERE based expression, with my criteria for Months 1-12 be "<=12", and for Months 13-24 I had to create a seperate query with the criteria ">12".

The next problem is that I just found out you cannot have two different queries as two different series on the same graph. Is there a way I can combine these two different criterias for on the same query.

So for each month, there will be 2 fields of data, one field is 1-12 Months, and the second field is 13-24, any ideas so I can graph it like shown in the picture attached to this thread?

 

[DCrake]

If you two queries are identical apart from the filtering then you could construct a union query to sit one on top of the other and use this as the basis for your graph.

David

 

[bg18461]

I got it working, thanks, the union query helped.