Q:

Join table on Count

I have two tables in Access, one containing IDs (not unique) and some Name and one containing IDs (not unique) and Location. I would like to return a third table that contains only the IDs of the elements that appear more than 1 time in either Names or Location.
Table 1
ID    Name
1     Max
1     Bob
2     Jack

Table 2
ID   Location
1    A
2    B

Basically in this setup it should return only ID 1 because 1 appears twice in Table 1 :
ID 
1

I have tried to do a JOIN on the tables and then apply a COUNT but nothing came out.
Thanks in advance!

A:

Here is one method that I think will work in MS Access:
(select id
 from table1
 group by id
 having count(*) > 1
) union  -- note:  NOT union all
(select id
 from table2
 group by id
 having count(*) > 1
);

MS Access does not allow union/union all in the from clause.  Nor does it support full outer join.  Note that the union will remove duplicates.

