Query to Identify Records with Different Values in Specific Field

[Hantzisp]

I need some help. I'm really new to SQL. I’m not sure what I'm missing here and why this isn’t working.


Background:


I have 2 tables related by an “ID_Key”. Each table contains an ExpDt field which represents an expiration date. These expiration dates may be different between these two tables. A record may not even exist on Table 2. Table 1 has the correct information. Database relationship has been set to Table1.ID_Key to Table2.ID_Key.


Query Objective:


I need the query display only the records that have different data in the “ExpDt” field.


Here’s the SQL code I’ve tried so far:

SELECT Table1.Last_Name, Table1.First_Name, Table1.ExpDt, Table2.ExpDt
FROM Table1 LEFT JOIN Table2 ON Table1.ID_Key = Table2.ID_Key
WHERE Table1.ExpDt<>Table2.ExpDt;

When I run this, the query results excludes records from Table2 that do not have a matching ID_Key with Table1 but lists all remaining records regardless if the ExpDt matches or not. Have I got the SQL code wrong? Or, maybe I created the relationship incorrectly?

 

[Joe4]

Try this:

SELECT Table1.Last_Name, Table1.First_Name, Table1.ExpDt, Table2.ExpDt
FROM Table1 LEFT JOIN Table2 ON Table1.ID_Key = Table2.ID_Key
WHERE [COLOR=#ff0000]NZ(Table1.ExpDt,1)<>NZ(Table2.ExpDt,2)[/COLOR];

 

[Hantzisp]

No luck, sir. I get the same result. It's showing all records even if the Table1.ExpDt & Table2.ExpDt match. I don't know if this makes a difference or not, but those field data types are set as "Short Text" because they are in "mm/yyyy" format. So Access doesn't know the ExpDt represents a date. I cannot change the data type either. When I try changing data type to anything other than Short Text, I get an "Operation is not supported for this type of object." message.

 

[Joe4]

It's showing all records even if the Table1.ExpDt & Table2.ExpDt match.

I recreated your scenario, and cannot reporduce this behavior. It works exactly as expected for me, not showing any matching values.
That leads me to believe that your values don't really match.

Bear in mind that they must match EXACTLY. Anything extra, like a single extra space in one, will cause them not to match.
So find a record that is not working as you expect, and check BOTH the ID_Key and ExpDt fields for extra characters.
You can use the LEN function on all the fields to count the characters of these entries, which should expose any extra characters (spaces, invisible non-breaking spaces, soft-carriage returns, etc).

I often see issues with data that is downloaded from the Web or comes from other applications. They often have extra characters not easily seen.

 

[Hantzisp]

Solved*

Thank you. The LEN function indeed identified a space on the Excel worksheet Table2 was linked to and counting that extra charachter.

I selected the whole column, hit Ctrl-F > Replace > Find what: " " > Replace with: "". This removed all extra spaces in the cells and my charachter count was the same.

Thanks again for the assist!

 

[Joe4]

You are welcome.
Glad you got it working. Those spaces/extra characters can really cause a lot of problems!