Search across tables (Access 97 SR2)

Topic

New Reply

We have a database containing tables that hold data about document issue to people. A new table is started when a document is issued – the table title contains the document code and the date. We now need to search across these tables to see when individuals signed for their document – the search only needs to return the table title in which name ocurrs. The number of tables changes all the time as documents are issued. We need to be able to tell the search to look for tables beginning with a specific code.

Could someone give me any tips on how to do this please?

Reply | Quote

Replies

You’re going to hate this answer, I’m afraid. You should store data in tables, not in their names.

Storing your data in many tables is making things difficult. You’d have to write lots of code to search table names and then retrieve records from some of them.

It would be much better to use one table with document information and one table that records when and to whom a document was issued. The tables should be linked (one-to-many) by the document ID.

Reply | Quote

Yes – big sigh, so early in the morning too.

We inherited this system some years ago and only in this week have we been asked for information.

Going to sit down now and see if there is some way of pulling the specific document code info together. Looks like the start of another learning curve.

Thanks for your help Hans – spent some days near Amsterdam last year – really envy you your on the flat cycling.

David

Reply | Quote