Tables

[shanedoherty]

Can anyone help me

I have 4 Tables, all have the same fields. I need now to be able to have a 'Search all Tables' feature. I was thinking of adding a new table that will hold all of the Data from the 4 tables. Everytime data is entered into one of the tables it is automatically entered into the new table.

This probably seems like a really stupid question to all you Access geniuses out there but if anyone can help it would be well appreciated.

Shane

 

[Mile-O]

Why do you have four identical tables in the first place? What purpose does that serve?

 

[shanedoherty]

Sorry didn't explain it well. Its a Contract DB each table has a unique field. Catalgue Number. An automatic number. One is SAM 001, others are TR001, MH001, CA001. I have 4 different types of Contracts. But I now need to be able to search through all Contracts. Any ideas

 

[Jigs]

Shane,

make another table which will consists of the names of the other 4 tables. then from your programming language make a query for searching which loops over the fields of the table which consists of the tables name with preserving the quotes for the search criteria.

Try it, if it can help u.

Jigs

 

[Mile-O]

Then you only need one table. One field will just flag the contract type; there's no need for four tables.

Jigs, surely a UNION query would have been a better solution.

Of course, a proper table structure is the best solution.