I am trying to create an automated report that will have a bit of information in it.
Basically I have a table full of customers energy and revenue on a 24 month period.
The values on the table are horizontal, rev1-rev24, energy1-energy24, with rev1 being the most current date and going backwards from there, ie rev1=May09,rev2=Apr09,rev3=Mar09,etc...
This is how the data is formatted and is updated monthly, no real way to change the formatting ie going from a vertical format to a horizontal (access friendly) format for the data in the imported table.
Now for each customers you will have multiple locations (different store addresses). I would like to create a report that has data and charts for each location on each page of the report. Some of the data portion is as follows:
Customer Address:xxx
Total Rev:xxx
Total Energy:xxx
Then I would like 2 charts per store(page).
One will be Rev vs Month (I would like to use column with the Months 1-12 and Months 13-24 data running parallel, ie May09 and May08 should be columns next to each other for data period "May" on the x-axis, and so on and so forth.)
One will be Energy vs Month (similar setup as rev vs month)
In my reading and testing I see there is a 6-field max in the access charts. Maybe I would have to create a crosstab query (not too sure how to do that). Also maybe I could embed a MSChart to circumvent this limitation, I am open to all ideas.
I have a header to be duplicated per page, would like to put the data in the detail portion, then put the graphs in the footer portion. I hope I was clear, please I could really use some help as I am new to access but not to vba, and even newer to reports.
Any help would be greatly appreciated.
Basically I have a table full of customers energy and revenue on a 24 month period.
The values on the table are horizontal, rev1-rev24, energy1-energy24, with rev1 being the most current date and going backwards from there, ie rev1=May09,rev2=Apr09,rev3=Mar09,etc...
This is how the data is formatted and is updated monthly, no real way to change the formatting ie going from a vertical format to a horizontal (access friendly) format for the data in the imported table.
Now for each customers you will have multiple locations (different store addresses). I would like to create a report that has data and charts for each location on each page of the report. Some of the data portion is as follows:
Customer Address:xxx
Total Rev:xxx
Total Energy:xxx
Then I would like 2 charts per store(page).
One will be Rev vs Month (I would like to use column with the Months 1-12 and Months 13-24 data running parallel, ie May09 and May08 should be columns next to each other for data period "May" on the x-axis, and so on and so forth.)
One will be Energy vs Month (similar setup as rev vs month)
In my reading and testing I see there is a 6-field max in the access charts. Maybe I would have to create a crosstab query (not too sure how to do that). Also maybe I could embed a MSChart to circumvent this limitation, I am open to all ideas.
I have a header to be duplicated per page, would like to put the data in the detail portion, then put the graphs in the footer portion. I hope I was clear, please I could really use some help as I am new to access but not to vba, and even newer to reports.
Any help would be greatly appreciated.
