Query to compare two columns in two separate tables for identical values

[sam81]

I'm just not sure of the language to use to do this in the Criteria section of Query Design.

Basically, I'm looking to see if the same customer IDs are present in two separate tables. This would indicate double payments. Both tables have a customer ID column.

For example, if the customer ID 12345 is present in both tables, then a double payment was made.

I tried this in Excel (using =ISNA/Match formula) but the number of rows is close to 100,000 and it locks up. I'm new to Access and hoping there's something that could do this.

[June7]

Could try Find Unmatched query. There is a wizard for that.

[sam81]

Could try Find Unmatched query. There is a wizard for that.

Well, the description in the Unmatched Query wizard states that it will find rows in one table that have no related records in another table. I need it to find rows in one table that do have related (identical) records in another table.

[June7]

Sorry, misread post.

Then that is just a SELECT query with INNER JOIN.

[sam81]

Here's the syntax I'm attempting, but it keeps causing an error message. I know I'm probably overlooking something minor, still new to this.

The two tables are "19m" and "34m". Both tables contain a column called "Claim ID".

SELECT;
FROM 19m INNER JOIN 34m ON 19m.Claim ID = 34m.Claim ID

[June7]

Why is the ; following SELECT? Should be at end of statement. Did you manually type in SQL View? Use the builder then look at statement in SQL View.

[sam81]

Why is the ; following SELECT? Should be at end of statement. Did you manually type in SQL View? Use the builder then look at statement in SQL View.

I manually typed this in SQL View based off of an example I found during a search online. I placed the ";" after the entire statement, here's what it looks like now:

SELECT
FROM 19m INNER JOIN 34m ON 19m.Claim ID = 34m.Claim ID;

It's still not working though. I am not sure how to use the builder using the INNER JOIN function. Is the builder essentially the query Design View? Would I place the INNER JOIN in the Criteria section?

[June7]

Yes, query Design View. Create the JOIN clause by establishing a link line between the tables. Click key field in one table, hold and drag to key field in other table. If you set up relationship in Relationships builder the query would automatically generate the link.

However, the reason for failure is probably the spaces in field names. Enclose in [], like: [Claim ID]. If you used the Designer, it should supply the [] for you.

Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention. Also, do not use reserved words, like Name and Date, as names.

This is basic Access query design functionality. Search Access Help or web for more info.

[sam81]

Yes, query Design View. Create the JOIN clause by establishing a link line between the tables. Click key field in one table, hold and drag to key field in other table. If you set up relationship in Relationships builder the query would automatically generate the link.

However, the reason for failure is probably the spaces in field names. Enclose in [], like: [Claim ID]. If you used the Designer, it should supply the [] for you.

Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention. Also, do not use reserved words, like Name and Date, as names.

This is basic Access query design functionality. Search Access Help or web for more info.

Wow that was so easy with the builder. It found the identical results I was looking for. THANK YOU! I just dragged and dropped the Claim ID from one table to the other.

I can now see the SQL view and see how far I was off with my code.