Multiple table query

[lipin]

I have about 12 tables, 1 for each department, and I was trying to query them all to find duplicates(employees that have worked in multiple departments); all tables have the same four fields(if it matters).
What kind of relationships(if any) must be between the tables to query them all? Can I do this multiple query with the wizard?
Any feedback is much appreciated.

 

[David R]

Because you've stored your departments in different tables, you'll have to do a UNION query to gather them together before you can do any useful analysis. It will probably be helpful to include a "field" in this query that lists the department for that table.

For example:
SELECT *,"Human Resources" AS Department FROM tableHR
UNION
SELECT *,"Accounting" AS Department FROM tableAcc
UNION
....

All tables must have the same number of fields in a union query. It will not be updateable, and you must code it in the SQL window by hand; there is no wizard for UNION queries.

For future reference, you should combine all your tables into one (more than one, actually, since you've got employee information and department information in these, it sounds like) and add a field that lists the department. This will avoid this problem in the future.
Look up "database normalization" in the Forum archives, in the MS Knowledge Base, or on the web. You'll thank yourself later.