searching multiple tables

[bob urz]

I have a access database i have programmed and used for years. I have
divided it up to have company data for each year. So each year has a MDB
file with data table, multiple search queries, and a interface to click
on particular queries.

The data base has first name, last name,tracking number, date, and item
description. I the past, i have just clicked on this years file and
could search that year.

What i tried to do was move data tables from past years into a new MDB
file and put in a new search quires. Some of the names may be the same
as does the item type. The tracking number is unique and not duplicated.
So how can i use multiple data base tables in one common front end?
when i tried to built a query in form designer, it came up with a error
message that i could not use more than one table or such.
All the data columns are the same from year to year.
How can i do this short or combining the older data tables into one
large table? This is access 2003.

bob

 

[Tom van Stiphout]

On Sat, 13 Feb 2010 20:36:08 -0600, bob urz <[email protected]>
wrote:

Append all data to a single table.

-Tom.
Microsoft Access MVP

 

[John W. Vinson]

I have a access database i have programmed and used for years. I have
divided it up to have company data for each year.

Well... that was a mistake, and now you're paying for that mistake.

So each year has a MDB
file with data table, multiple search queries, and a interface to click
on particular queries.

The data base has first name, last name,tracking number, date, and item
description. I the past, i have just clicked on this years file and
could search that year.

Including a date field or a year field would let you do so with one database.
All it would need is a parameter query to select the year.

What i tried to do was move data tables from past years into a new MDB
file and put in a new search quires. Some of the names may be the same
as does the item type. The tracking number is unique and not duplicated.
So how can i use multiple data base tables in one common front end?
when i tried to built a query in form designer, it came up with a error
message that i could not use more than one table or such.
All the data columns are the same from year to year.
How can i do this short or combining the older data tables into one
large table? This is access 2003.

bob

Combining them into one large table would be best design, but - if you're
determined to keep it split up - you can use File... Get External Data... Link
to link to all your year tables, and create a UNION query to splice them all
together. See the online help for UNION or if you need help, post back with
more details about the queries you're trying to run.