finding table name

[mainerain]

I have a Serial Number and I want to determine which Table it resides in. Once I know which table I will open the corresponding Form to reveal the details about that associated device Serial Number. Sorry if there isn't enough info. I don't always know how to ask question to get my intended answer. I'm a Greenhorn.

[JoeM]

It sounds like you may have a data structure problem. In general, you shouldn't have multiple tables holding the same type of information (typically there would be one table for holding all like information).
Can you describe the table structure, and why you have mutliple tables that you need to search across for a single item?

[mainerain]

I have many different device types and models. I have included a devices serial number in the appropriate table.

[JoeM]

Are all the tables structured similarly?
If so, then you should only have one table. You would just have different fields for your device types and models, so you can easily filter on them.

That is why a good database design is so important. If you find yourself having to do odd things (or things that there doesn't seem to be a good way to do), that is often a sign that there may be a design issue.

[mainerain]

Often times the items are completely not alike, thus so many different tables to accommodate.

[JoeM]

Then a Union Query might be your best bet here.

Let's say that you have three tables, that are totally different structures, but all have a SerialNumber field.
Then, you could create a Union Query like this here to return all Serial Numbers and Table names to a single Query that you could then use to locate where the record exists, i.e.

SELECT SerialNumber, "Table1" as TableName
FROM Table1
UNION
SELECT SerialNumber, "Table2" as TableName
FROM Table2;
UNION
SELECT SerialNumber, "Table3" as TableName
FROM Table3;

For more on Union Queries, see these links here:
https://support.office.com/en-us/art...0-ad0a75541c6e
https://www.datanumen.com/blogs/crea...lts-ms-access/

[mainerain]

That sounds promising. I'm running out of time. Will try later. Thanks so much.

[JoeM]

You are welcome.

[orange]

mainerain,

If you don't have a solution, it may be helpful if you gave some details of your table designs, or a graphic of your tables and relationships, and/or some sample data.
Many problems/issues with database originate with improper design of basic table structures, as JoeM mentioned.


I posted a sample- modified universal search - a few years back that may be useful.
Good luck.

[Micron]

Often times the items are completely not alike, thus so many different tables to accommodate.

Not convinced that there isn't a problem. Surely there must be a parent entity involved? And to open the "corresponding form" based on what table a serial number is in just adds to that suspicion.