How to search 50 tables for same data (1 Viewer)

[davidg47]

Let me try this again and try to word it differently.

Sometimes I have to search for one employees ID number through 50 different tables.

Is there a way that I can return about three fields from a record that I search for using the employees ID number?

What I need is a form that when I input the employees ID number in a text field, and I click the search button, the application will search through about 50 tables (each table represents a different company within a corporation) for that employee ID number. If the ID number is found in one of the tables, then it will return 3 or 4 fields found in that record. If the ID number is not found, then a MsgBox will pop up saying, "Not Found!"

Thanks in advance for your help...
David

 

[Oldsoftboss]

(each table represents a different company within a corporation)

You should have a table for Employees, a table for Corporations, and a table for Companies. Then link them all together with relationships.
If your tables were set up correctly, each employee would have a one to many relationship with the company (One Company, Many Employees)
That way you would be able to set up a query that returns the data.

Dave

 

[Bat17]

Are all of the tables identicaly structured?
are the number of tables static or will you need to keep updating the list?
If the tables are identical you could build a Union query to join them and filter on that, it will be quite long winded though. It could be done in VBA but then would probably be complex.
Do you have control of the structure of the DB as your best solution is to use proper design and have just one table with a field to show the company that the person belongs to.

Peter

 

[davidg47]

OK, I cannot change the structure of any of the tables. Each company has it's own table, then there are other tables that support the company tables such as a Comments table, Term Codes table, Employee Status table, etc. This is an application that was written by someone else, and I have been asked to come up with a way for them to be able to quickly search all the company tables for an employee ID with the click of a button. If that employee ID is founf, it will return the name of the company that employee works for. Yes, all of the company tables are exactly the same.

So if I just build a query linking all of the tables (about 50 of them) using maybe the SSN field, then use that query as the place to search for the employees SSN, would it search all the tables and return the Company name?

Thanks,
David

 

[Len Boorman]

Depending on how much data is present I would be inclined to run so Make Table queries that feed from Union queries and then do data extracts from these new temp tables, Refresh the tables as required.

In not too many "Companies maybe purely create some Union queries and do data extracts from them
L

 

[davidg47]

Gee Whiz, ok, you just went over my head with that one. How would I go about doing that?

I have a query that works in SQL Server for this, is there a way I can build a VB application that runs the query and returns the fields I need?

 

[Len Boorman]

Gee Whiz, ok, you just went over my head with that one. How would I go about doing that?
?

Which bit

Union query

Select x
from y

UNION
Select a
From b

UNION
Select p
From q

etc ;

Same number and sequence of fields from each table

UNION excludes Duplicates
UNION ALL includes duplicates
UNION MINUS ( I think) Only Duplicated values

There is a limit as to how many UNION's you can have in one query.

Nothing to stop you doing a UNION on two UNION queries

Clear as mud eh
L