I have inherited an old Access database with about 300 tables. We need to be able to search for a client ID number (for example 'R12345') in every table in the database and to return all tables that it exists in (all rows would actually be good but a list of the tables it exists in would do as a summary). So I guess the query would need to go through every column and every row in every table in the database before returning its results. I am guessing this must be possible but haven't the faintest idea what SQL would achieve this! Can anyone help? It is important we find every entry that references this client ID in the database as otherwise medical records for that person could be missed out of the search.
Searching for a particular ID number across all tables in an Access Database
- Thread starter 83dons
- Start date
Minty
AWF VIP
- Local time
- Today, 19:26
- Joined
- Jul 26, 2013
- Messages
- 10,640
You can get all the fields in a table using the TableDefs properties, have a read here;
https://www.access-programmers.co.uk/forums/showthread.php?t=234890
https://www.access-programmers.co.uk/forums/showthread.php?t=234890
NauticalGent
Ignore List Poster Boy
- Local time
- Today, 14:26
- Joined
- Apr 27, 2015
- Messages
- 6,747
Sounds like a bit much. If your tables are structured properly, an ID field should either be a primary key or foreign key - which means I doubt it would be in EVERY table (why would you have a Client ID field in a Suppliers table?). And it definitely would not be in certain fields such as qty, price, etc.
I know you are looking for a quick fix but I think your time would be better spent getting familiar with the right tables and base your queries on them.
I know you are looking for a quick fix but I think your time would be better spent getting familiar with the right tables and base your queries on them.
NauticalGent
Ignore List Poster Boy
- Local time
- Today, 14:26
- Joined
- Apr 27, 2015
- Messages
- 6,747
Sorry Minty, I didn’t know you were engaged!
Minty
AWF VIP
- Local time
- Today, 19:26
- Joined
- Jul 26, 2013
- Messages
- 10,640
Cross-posted here http://www.accessforums.net/showthread.php?t=71981
@OP,
As the others have commented, you can start with fields you know WON'T have the value you are looking for. With 300 tables, I'd also look at how the tables are related.
If "Patient" has the ID, you would also want all "Blood Tests", even if the "Blood Tests" table does not have the client ID but the parent records ID. Hopefully with 300 tables you have relationships already established to keep your data together.
As the others have commented, you can start with fields you know WON'T have the value you are looking for. With 300 tables, I'd also look at how the tables are related.
If "Patient" has the ID, you would also want all "Blood Tests", even if the "Blood Tests" table does not have the client ID but the parent records ID. Hopefully with 300 tables you have relationships already established to keep your data together.
- Local time
- Today, 14:26
- Joined
- Feb 19, 2002
- Messages
- 46,861
Think about what you are asking.
300 tables
Average 20 columns per table
= 600 queries against mostly non indexed fields
Given that you are looking for a string, you will get data errors if you even attempt this against non - text fields so you have to start with examining each table definition to determine which columns to run queries against.
Why not tell us what problem you are trying to solve.
300 tables
Average 20 columns per table
= 600 queries against mostly non indexed fields
Given that you are looking for a string, you will get data errors if you even attempt this against non - text fields so you have to start with examining each table definition to determine which columns to run queries against.
Why not tell us what problem you are trying to solve.
Similar threads
