Checking for duplicates using Access SQL

Question

I am currently working on an access form which handles duplicate checking between two tables.

The scenarios I am looking for it to return are as follows:

-Duplicates in TempTableExcelFile -Duplicates in tblPeople -Duplicates across the two tables.

I understand through research that I need to use a join, but I can't seem to get it working.

My current code query is as follows:

SELECT * FROM (
    SELECT count(code) AS [Number of Duplicates]
           code AS [Code Duplicated],
           "Existing Database" AS [Duplicate Location] 
    from tblpeople 
    group by code
    having count(code) > 1 
    UNION ALL
    select count(code) AS [Number of Duplicates],
           code AS [Code Duplicated], 
           "Imported Data"  AS [Duplicate Location] 
    from TempTableExcelFile 
    group by code
    having count(code) > 1
)

So from my understanding, this currently will give me duplicates contained in their respective tables.

My understanding of how I would join the tables is as follows:

SELECT count(code) AS [Number of Duplicates],
       code AS [Code Duplicated],
       "Existing Database" AS [Duplicate Location]
FROM tblPeople        
    JOIN TempTableExcelFile 
    ON code = code
GROUP BY code
having count(code) > 1 

But despite numerous efforts, I can't get it to trigger in the instance that we have a code TempTableExcelFile duplicated in tblPeople. I am painfully aware this is likely down to a lack of understanding on my part, so any assistance in this matter would be appreciated.

If you do post a solution, please do what you can to explain where I went wrong, and suggest topics I can research to further my understanding.

Thanks,

Dan

P.S, I appreciate the naming convention here is somewhat off, I am using existing tables created by my predecessor.

Answer 1

Use a UNION ALL to make the 2 tables into 1 "table", then count from it

SELECT count(*), code FROM (
    SELECT code
    from tblpeople  
    UNION ALL
    select code
    from TempTableExcelFile
) TableAndTemp
    group by code
    having count(code) > 1

Alternatively, select all the codes from the temp table that already exist in tblpeople

SELECT DISTINCT code
from TempTableExcelFile
WHERE code in (SELECT Code from tblpeople)