Common entries within multiple tables

[JesterMania]

Greetings,



I am very new to MS Access 2007 and have a problem I am trying to overcome. I have about 100 tables containing listings of user IDs. My task is to obtain a list of users IDs common to all 100 tables.

i.e. if I had 3 tables with the following:

Table 1
-------
George
John
Harry
Chris

Table 2
-------
John
Ben
Peter
Chris
Henry
George

Table 3
-------
Ben
Chris
George
Michael

The query should return the following:

George
Chris

How would I design this query in MS Access and would the solution scale well with approximately 100 tables?

Thanks!

[TheShabz]

Are the IDs the same to those users? How do you know George in Table1 and George in Table2 aren't different people?

[JesterMania]

They are the same user. Actually, the example below was only a simplified version of the problem. The real data IDs are first name initial + last name such as GJACKSON (George Jackson).

[TheShabz]

That's still an ID violation though. What happens if you have 2 George Jacksons?

In any case, how are the tables and fields named? What you would do is create a massive UNION query then do a SELECT DISTINCT from the UNION.

[JesterMania]

The Shabz, I appreciate your concern regarding ID validation but let's just assume that these are the same people for the sake of solving my problem For each table, there is only 1 column called "U ID"