Multiple tables in a query

[Guest]

I have 3 tables that someone else created that all contain identical
information columns but for different years. I just need to create a query
that will search these 3 tables for a particular number that could appear in
any one of the tables in a column titled LotNumber. I cannot figure out how
to relate these identical tables since there isn't a one to many
relationship. They all have many lot numbers and all have the heading for
that column as LotNumber. The pupose for the search is to find the "File Box"
location of the lot number being searched.

CODE LOT NUMBER QTY PACKAGING FILE BOX
753322 4068HG7533 3 BTL-240 0001 0011589
191041 4150IG1910 3 BTL-300 0001 0012698

 

[Roger Carlson]

Actually, you won't Join the tables (which is what I expect you mean by
"relate"). Instead, you will Union them.

Something like:
Select * From Table1
Union
Select * From Table2
Union
Select * From Table3

This just slaps the results of table 2 to the bottom of table 1 and so
forth. If there are duplicate records, you would use Union All to show the
duplicates.

However, the larger question is why this information is in separate tables.
They should all be in a single table. Then you wouldn't need to do this. A
simple query would suffice.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L