Combine results of multiple crosstab queries to prepare reports

[dm23]

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!

[June7]

Why 60 crosstabs? I see only 4 payment types.

A VBA procedure could resolve this more than 1 way. It could construct SQL statement and set RecordSource property of report or could write records to a temp table.

However, a UNION query might serve this situation. I see only 4 payment types in the rotation - are there more fields involved? Is Location unique? Consider:

SELECT Location, [Month], Cash AS Quantity, "Cash" AS Type FROM tablename
UNION SELECT Location, [Month], Check, "Check" FROM tablename
UNION SELECT Location, [Month], CreditCard, "CreditCard" FROM tablename
UNION SELECT Location, [Month], MoneyOrder, "MoneyOrder" FROM tablename;

Now use the UNION as source for CROSSTAB.

A UNION essentially rearranges data into a normalized structure, as the data should have been to begin with.
There is a limit of 50 lines in a UNION.
There is no wizard or designer for UNION, must type (or copy/paste) into the SQL View of query builder.


BTW, Month is a reserved word, should not use reserved words as names.

[dm23]

Thanks! the UNION Query helps a lot!

The example I gave above was just one of several tables that I needed for the reports and some of the other tables had more feilds. The field name for month was actually ReportMonth, I wrote that down wrong. Location is unique though and common throughout the tables, so I was able to use the UNION Query to create a Crosstab across several different tables at once. I also added a WHERE clause to the UNION query to return the desired month of data.

[June7]

Suggest not limiting the UNION with filter criteria. Just use it to combine all the data as a source for subsequent queries/forms/reports where you apply filter criteria.