same database-same tables-different records

[taylornow]

Hello everyone - here I go......
Have one database. A new table each year. Same fields - same reports - just different entries. How am I able to take info from each for a report showing the totals requested and approved each year.I have reports showing by each year, but need one combining all the years. The fields will always be the same every year. We are trying to build a history by year for comparison. Fields are:
Status,County,Name,Requested,Approved.(text,text,text,currency,currency) Need to show and total by county. I am clueless on a query combining all years. Am I making this harder that it should be?

Thanks in advance-
Taylor

 

[zerosandones]

Once you see the solution, you'll probable agree that its simpler than you thought.

i) First, lets establish some tables to represent e.g. that last three years of county data:

Tables
----------
County1999
County2000
County2001

NOTE: As you stated, the field names and data types are consistent across all the tables.

ii) Create a UNION query to combine results from more than one datasource:

SELECT County, "1999" As ReportYear, Status, Name, Requested, Approved
FROM County1999
UNION
SELECT County, "2000" As ReportYear, Status, Name, Requested, Approved
FROM County2000
UNION
SELECT County, "2001" As ReportYear, Status, Name, Requested, Approved
FROM County2001;

iii) Using the Report Wizard to simplify the initial report setup, create a new report with the UNION query from (step ii) as its record source. While still in the Wizard, group the report on the County and ReportYear fields, and when asked, request SUM totals at the County, ReportYear and Report levels.

The rest of the report is a matter of aesthetics and layout.

Hope this helps.

 

[taylornow]

Thanks so much. I had completely forgotten about Union Query. You are so right - I was going around my elbow to get to my thumb. Thanks again.

Until the next -

Taylor