How do i create a query which can search for data in many tables?.

[Guest]

I created around ten tables in Access 2000 and I want to create a query which
can search for data in all of these tables by entering in just one parameter
value. Does anyone know how to do this?
Thnaks

 

[Rick B]

Well, you would need to share a bit about your structure. Do you have these
tables linked to each other in some way? Does each table contain the same
fields (If so, wouldn't it make more sense to put them in one table with a
"type" field or something to differentiate?)

Assuming they are all pretty much identical, you would create a UNION query.
The help file should get you going.

If they each contain different informaiton and are linked in some way (name
and address in one, health information in another, etc. all linked together
by social security or some similar scenario) Then you would simply include
all the tables in your query and pull whichever fields you need from each
table.

Rick B

 

[Edward G]

One method of doing this would be to add the tables and fields you want to a
select query.
Then in the criteria rows enter the same parameter in staggered rows. For
instance, you could put
[Enter a number] in the first criteria row of the first field and in the
second criteria row of the second field, etc, etc...
Using copy and paste will save you some time and assure that spelling does
not vary.

Ed