Query to Identify Records with Different Values in Specific Field

Hantzisp

New Member
Joined
Apr 11, 2019
Messages
9
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:


Code:
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?
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
Try this:
Code:
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

New Member
Joined
Apr 11, 2019
Messages
9
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.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 11, 2019
Messages
9
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad you got it working. Those spaces/extra characters can really cause a lot of problems!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,341
Messages
5,624,109
Members
416,011
Latest member
chengkoonwing

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top