Appending Tables:

[Eli Sidwell]

I have an Access DB that contains 5 tables for the last 5 years. All 5
tables have the same structure. I wanted to keep each year separate
for organizational purposes.

But, I want to query all 5 tables with one query for an application.

What would an query look like that uses all 5 tables as a source ?

Should I create a view that inserts all 5 tables into a recordset ?
(Wouldn't this be resource heavy ?)

Thanks

Sid.

[Rick Brandt]

"Eli Sidwell" <si******@alexian.net> wrote in message
news:ef**************************@posting.google.c om...

I have an Access DB that contains 5 tables for the last 5 years. All 5
tables have the same structure. I wanted to keep each year separate
for organizational purposes.

But, I want to query all 5 tables with one query for an application.

What would an query look like that uses all 5 tables as a source ?

Should I create a view that inserts all 5 tables into a recordset ?
(Wouldn't this be resource heavy ?)

What you're looking for is called a Union query (check help file). It basically
combines tables vertically rather than the more typical horizontal. This type
of query cannot be built in the query design grid, but rather has to built in
SQL view.

You would be better served long term to combine all the tables into one and add
a field to identify the year. It is MUCH easier to pull one desired year out of
a table with multiple year's data using a query than it is to combine multiple
tables with a Union. Plus a Union is automatically read only. Not a problem
for reporting, but you might want to do some editing across years at some point.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com