Searching Multiple Tables at Once

[ImStevenB]

I have a search that I need to setup.

Here's what I'm trying to do:

I have a table with a list of chemical names - I want to be able to click on one of the chemicals and have Access look through 10 other tables to see if that chemical name/number appears.
If the chemical appears, I just need make a note of the table name. But I need to do this for each table. So if I click on a chemical name and it appears in 3 tables - the result needs to be

Table 1
Table 4
Table 6

Thank you in advance for any help.

Im Steven B

http://www.myarcadia.net

 

[lhite]

If the chemical names are exactly the same in each table try creating a query that links the tables by chemical name. You will need to change the join type to specify all the records from the main table and only matching records from the other tables. Set the query to select the chemical name you're looking for, and set the query properties to Unique. Bring in the chemical name from all the tables, and make some modification to specify which table it comes from, so you can easily identify which tables have the chemical name.

This might be a little rough, but I think it will do what you want.

-Larry

 

[ImStevenB]

Thanks for the response:

But unfortunately I cannot perform the searches on the names. The unique identifier is actually a chemical number. The numbers are standarized for the industry - besides - with the spelling on some of the names and the likelyhood of it being spelled wrong - the best search is by the chemical number.

Or does the same idea apply here? Probably now that I think about it.

How about storing the table names - how do I get the query to list the tables I find a chemical on?

Thanks again.
Im Steven B

http://www.myarcadia.net

 

[lhite]

In a blank column in the query grid create a field by picking a name, then typing a colon, then a value. For instance, type:

NewFieldName:NewFieldValue

In your case it could be:
InTable:"Table 1"

All records would show the newly-created [InTable] field as "Table 1" in that field. For the next table it would be something like:
InTable:"Table 2"
etc.

-Larry

 

[ImStevenB]

Thanks for the quick response -

I will try this later today - too many projects - too little time.

Thanks again.
Im Steven B

http://www.myarcadia.net