I need to run reports based on tables structured like this:
Location Month Cash Check CreditCard MoneyOrder … Location1 month # # # # Location2 month # # # # Location3 month # # # # Location4 month # # # # Location5 month # # # # …
The reports need to look like this:
Location1 Location2 Location3 Location4 Location5 … Cash # # # # # Check # # # # # CreditCard # # # # # MoneyOrder # # # # # …
I need to be able to run these reports on a monthly basis, so I don't want to export the data to excel every time to transpose it and import it back into Access for the report. I have tried running a crosstab query, but it will only allow me to run the data for one column at a time so my best result looks like this:
Location1 Location2 Location3 Location4 Location5 … Cash # # # # #
I thought about running separate cross tab queries and then combining the results in a report, but then I would have to run at least 60 crosstab queries in order to get everything on the reports every month. Is there a way to automatically comabine several crosstab queries into one using VBA or something? Or is there another function in Access that will help me get the results that I need?
Please help me! I've been stuck on this problem for days. Thanks!