MS-Access group by column having count(*) > 1 weird behavior?

Question

I am playing around with ms-access (MS-Office Professional Plus 2013) trying to figure out if I have duplicate rows before I merge one table into another table. I want to collect the rows that are duplicates and give an error with the duplicates before the merge happens. I have two scenarios to cover. The first scenario is duplicates on a single column. The second scenario is duplicates on two columns. Any help on the first scenario would be appreciated.

Scenario 1: The two tables have the exact same column structure so to keep it simple I will use the following table structure. ( I simply added two tables inside access and run the query to figure out the correct syntax.)

Duplicates based upon one column:
    Table1    Table2
    ID        ID       
    1         1
    2         3

Running the query:
Select ID from Table1
Union ALL
Select ID from Table2
group by ID having count(*) > 1

The result set is always the records from the first select statement. In other words it always returns Id=1 and Id=2. If you change Table1 to Table2 the result set is always from table2. If I change "Union all" to union same results. I tried changing the ID column names as well as change the type to be number instead of auto. Any idea what am I doing wrong?

Scenario 2: I know what the value should be in the second column so it is hard-coded. I added this here to show access appears to work as expected in this scenario but not in scenario 1.

Duplicates based upon two columns:  
    Table1             Table2
    ID   Field1        ID   Field1
    1    abc           1    abc
    2    bcd           3    abc

Running the query below works as expected. The row with ID=1 is only returned.
select ID, Field1 from Table1 where Field1 = 'abc'
union all
select ID, Field1 from Table2 where Field1 = 'abc'
group by ID, Field1 having count(*) > 1

Ken White Ken White · Accepted Answer · 2018-06-21T00:50:44

The GROUP BY is only being applied to the second table. You need to do the UNION ALL first, and then the GROUP BY and HAVING on a SELECT from the combined results.

Not Access specific, but something like this works:

SELECT id FROM
(
  SELECT id FROM a
  UNION ALL 
  SELECT id FROM b
) AS c
GROUP BY id HAVING COUNT(*) > 1

MS-Access group by column having count(*) > 1 weird behavior?

2 Answers