Select query against all tables in Access database - vba or union

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database containing an increasing number of mailing files (names and
addres.
ses). I often need to find when a certain customer was mailed. The only
way to do this is to search each table in the database in turn. A new
table is added to the database every few days.

There are good reasons why I dont want to append each new set of mailing
data to an existing table, so what is the best way to seach for a cetain
person in all tables?

I need to do this via a form ideally, to keep it simple for the users of the
system. I dont want them to have to update the sql of a query or even amend
on in design via.

Any ideas?
thanks
 
My suggestion would be to add a Mailings table that has the PrimaryKey of
the customer and the Date of the mailing and perhaps the description of the
mailing.

Then instead of adding a new table everytime you have a mailing, you would
just append records to the Mailings table

I can think of no good way to handle the search if you just keep adding
tables. You MIGHT be able to build a VBA routine to search through each of
the tables and append the results to another table or if you need just the
result for one customer return the last date.

If your imported tables were named consistently so you could identify which
ones to search, you could step though all the tables looking for the person,
getting the date and then comparing that to the date already found, and ....


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top