How do I search more than one Table at once...

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

Guest

I have a data base that removes redundant records to a different table, but
it is possible that those records could at some time in the future become
'Unredundant'. I would like to be able to search both tables at the same time
to check all records. I have tried queries and macros but just dont seem to
be able to find an answer. I would be grateful for any advice at all.I run
Office 2003 but am keeping this data base as an Office 2000 version.
 
Hi,

Sounds like you want a Union query (enter this in SQL view of a query):

SELECT * FROM tbl1 UNION ALL SELECT * FROM tbl2;

This will work if your fields are the same in both tables and the ALL
predicate will not only make it run faster but also ensure all records
are returned (without the "ALL", duplicates will be removed from the result.
 
Suze

You have a db in which you take records out of a table and put them in
another table, but still need to "see" them, and ?may need to bring them
back. Why? (as in "why have you chosen to move them around and maintain
two redundant table structures?")

Another approach is to add a single field to the first (and only) table that
you use to "flag" an "out of service", "redundant", "archived", or whatever
record. If you ONLY need to know that it is out of service, use a Yes/No
field. If you want to know "when" it went out of service, use a date/time
field.

If that particular record ever becomes "active" again, clear the "flag"
field.

Good luck

Jeff Boyce
<Access MVP>
 
Back
Top