Producing a search from several tables

[pirseinkim]

I need to create a search database. We recently purchased a US Database that has several companies for each state in their own table. So I have 51 separate tables. Each table can contain anywhere from 40,000 - 1,700,000+ records. So what I need to do is, on my screen create fields from which I would like to search ie) company name, industry, state. Some of these fields will be able to search on partial description. In general, I would create a query and perform the search. Simple. But how would I go about doing this? I need to search every table. So for example if I typed in 7-11 in the company and in the state of TX. I would like to search the TX table for all 7-11's. That doesn't seem so hard, however, what if I put that I wanted to see all 7-11 stores (for every state). I cannot create a query that will hold all that data. Either I'm over thinking this or I'm lost! Any help is greatly appreciated. Hopefully I explained it well.



Thanks,
Kim p

[June7]

Options:

1. combine all 51 tables into a single table

2. use a UNION query to emulate a single table - there is a limit of 50 lines in a UNION

3. use VBA code to run INSERT SELECT sql action and write records from each table into a 'temp' table which is purged before each search process

[pirseinkim]

If I create a union query and combine all 51 tables won't it take a while to run? My thought was to create queries for each state because there are select fields I would like to use only. Then create my union query. However, by creating a union query, is it faster for it to read though every field in each table or using the queries that I create? Does that make sense? So with that said, which way is reccommended for a quicker response?

Thank you,
Kim P

[June7]

UNION has limit of 50 lines (50 tables), you have 51. A UNION query can pull only the fields specified.

I don't know how fast or slow for any type of query involving millions of records.

How would you present to the user the results of searching 51 queries? Possibly the VBA and temp table approach is your only recourse.