How to Find Unmatched Fields in a Single Table

[rkaiman]

Here's some sample data. I want to compare the date in TX-Date Filled to the date in RX-LsDte Filled*Cent. If the dates are DIFFERENT I ultimately want to delete that row from my table.



I have tried importing the data into two seperate tables and creating a "find unmatched" query. It does provide the results I want, but the results are read-only so I cannot delete the results.

What is the best way to handle this? I am intermediate in skill so your help is appreciated. In the image below the first two rows are what I want to delete from my data because the dates in the first two columns do not match. I will be running this on 1000's of records weekly.

Thank you,

[June7]

Bing: query delete duplicate record
Review http://support.microsoft.com/kb/139444

[pbaldy]

Generally

SELECT...
FROM...
WHERE Field1 <> Field2

[pbaldy]

Bing: query delete duplicate record
Review http://support.microsoft.com/kb/139444

But they aren't duplicates.

[ssanfu]

As Paul said,

Try

SELECT [TX-Date], [RX-LsDte Filled*Cent] FROM YourTableName WHERE [TX-Date]<>[RX-LsDte Filled*Cent];

If the query above only returns records where the dates in the two fields do not match, change the query to

DELETE * FROM YourTableName WHERE TX-Date<>[RX-LsDte Filled*Cent];

Replace the BLUE text with your table name...

[orange]

Hi Steve,

I hear you the only 1 in North America enjoying a relative heat wave.

-14C in Ottawa.

[June7]

Duplicates based on TX-RXNumber. Sounds like OP wants to delete the older dated records.

If the TX-DateFilled will always equal RX-LsDte Filled*Cent in the most recent record, query that deletes all where they aren't equal should work.

Yep, 25F today.

[ssanfu]

Yes, it is a blistering 28F (-2.2C) here today.