I have an Access DB which has a query to determine the numbers of receipts on hold within our system at any given time. The query counts the number of held receipts for each region of our State. In the past I have been running this query in a Query Reporter program (which uses sql) and exporting the data results to Excel. The results contain only two things, the region and the total count of receipts for that region. (Not the month)
The right column of the spreadsheet contains the names of all the regions and the top row of the spreadsheet has a column heading for the regions and then a column heading for each month which I run the query. I pass this information on to management to determine the success or failure of the workers in a region to reduce the number of held receipts. I also create a chart showing the progress each region is making over the months.
The old process is a little time consuming and Iím trying to figure out a way to do the same things in Access and/or Excel. I have looked at Pivot Tables and sending the data to Excel, but I just donít know the easiest way to do this. I have the query in Access now and have created an Access report which shows each region with the number of held receipts for each region. Iím trying to figure out how to create a chart and spreadsheet view showing the progress of each region over several months. In other words I want to compare the results that I get today if I run the query with the results that I may have run every month for the past 6 months. The Access query Iím now using is being run against a mainframe system using a linked table. I donít currently store any of the results that I have received from this query, I just run the query and print the report and then the data is gone.
Iím hoping to get several ideas on how to best do what I want done in the most automated and timely fashion. Any suggestions would be appreciated.