need to search all tables for a particular value

[Doug Starkey]

Okay, I hope there is a way to do this other than opening EVERY table
and looking for this value.

I've inherited a database from someone else. I am slowly building a map
of how it is structured but it is, from my perspective, a very complex
database with lots of relationships. One of the things it does is query
an outside data source and create/add to tables any "new" items it
finds. It does not, apparently, REMOVE the data from any of the tables
if it is removed from the external data source.

I will try to address the root problem (failure to remove) at some other
time. What I want to do right now is find out EVERYWHERE a particular
value exists in this database. I know I could write a query for each
table... but there are LOTS of tables. Is there some sort of "global"
search routine built into Access? Or is there a way to right a query
against all the tables and all the fields?

Thanks for helping this noob!

 

[Jeff Boyce]

Doug

Not sure I understand... the db is free to stick "new" data anywhere? or
create new tables at will?

It would help to have an idea about the domain/topic... are we discussing
dogs & cats, stock quotes, or a bill-of-materials for nuclear reactors?<g>

Also, if you want a "map", you can use the database documenter, and/or the
relationships window, or a third party tool.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Donald A. Herman]

I made a sample form for access to do that.
http://don_herman.tripod.com/msoextras/tips.htm

--
Donald A. Herman
Software - Scheduler Pro, Disk Cataloger, Math Wizard
http://don_herman.tripod.com

Office Extras, Addins
http://don_herman.tripod.com/msoextras

 

[Doug Starkey]

See my responses below:

Doug

Not sure I understand... the db is free to stick "new" data anywhere? or
create new tables at will?

Well, I don't think it's "free to stick new data anywhere". But there is
NO documentation and I did not design this database. So, I don't know
all the possible locations a value from an external database might be
used. This value is a "key" value that would be useful to reference for
many purposes.

It would help to have an idea about the domain/topic... are we discussing
dogs & cats, stock quotes, or a bill-of-materials for nuclear reactors?<g>

I don't really see what difference it makes but this is a food product
database. The "value" is an item code/identifier. While it would be
smartest to make simply one table to use as the reference for item codes
& descriptions, etc. I have NOT found that this particular designer was
careful to do that. While I could ASSUME that one table that contains
the value is the ONLY table that contains the value, I would NOT be
comfortable with that assumption.

To perhaps clarify, let's say I am looking for the value, "ABC". I have
tables, "T1", "T2", and "T3". Each table has fields.
T1 fields: F01, F02, F03
T2 fields: F11, F12, F13
T3 fields: F21, F22, F23

For the sake of the example, let's assume the value "ABC" could possibly
be stored in F01, F13, and F22. However, because I am not the designer,
I have no clue which of the 9 fields it could be in. It does not HAVE to
be in any of those fields, but I want to know for sure that it is not. I
want to find EVERY occurrence of that value without having to open each
table and search for the value. (My example of 3 tables wouldn't be so
bad but when you have over a hundred tables, it gets a bit tedious!)

Also, if you want a "map", you can use the database documenter, and/or the
relationships window, or a third party tool.

The documenter is a useful tool for a small database. But, again, I am
searching for a VALUE, not a field name. And the documentation on just
the tables is several thousand pages. Without knowing what field name
I'm looking for, I'm still looking at a couple of days to sift through
all of that... and I STILL wouldn't have the VALUE that I'm looking for.

-Doug

 

[Jeff Boyce]

Doug

If the value "ABC" (or any other) could be found in field F01 (or any of 5
or 10 or 100 other fields), you might be able to use a "normalizing union
query" to look. But you'd need to write a lot of SQL, because you're
basically telling Access to "look here, and look there, and look there, and
...." for all possible locations.

Another approach might be to build a new table and append the values into a
single field -- this would be more searchable, but would still require that
you create a mess of append queries to get the data from current
tables/fields into the new table.

A third possibility is that you do the whole thing in code. Create a
function that runs through all tables, and within each table, runs through
all fields, and within each field checks each row, and writes a "Found It!"
record to a table (or recordset) each time it finds the value.

I'm not familiar with other approaches...

Good luck!

Jeff Boyce
Microsoft Office/Access MVP