Appending many tables together while creating a new field for pivot tables

[mdfx]

Hello, I received a database with many tables that are by year and region, with identical layouts. For example,

tblRegionA2001
tblRegionA2002
tblRegionA2003
tblRegionB2001
tblRegionB2002
tblRegionB2003

I'd like to combine these in a way that I can use a pivot table to summarize the data in these tables by year and region. What are my options? Can I run a query or some macro that simultaneously adds fields (region, year) and appends tables?

 

[mdfx]

A large union query worked great, and the key piece I needed was being able to do "RegionA" as RegionName, "2001" as DataYear ".

Select ..., "RegionA" as RegionName, "2001" as DataYear 
From tblRegionA2001
UNION ALL
Select ..., "RegionA" as RegionName, "2002" as DataYear 
From tblRegionA2002

I wrote a script in a language I'm more familiar with to assemble the full query from the naming conventions.

The only trouble with the union query is that Access throws a "too complex" error after a certain number of unions. Fortunately I didn't need to do every region and this worked.