Identify records which are not equal (1 Viewer)

Morten

Registered User.
Local time
Today, 16:24
Joined
Sep 16, 2009
Messages
53
Hi,

I need to compare two sources and identify records that are not equal if one of the values in the record set is not identical to the same record in another source.

Source 1: Table A:

ID Kreditornr Land Sale
1 9000000 DK 1000
2 9000001 DK 2000
3 9000002 NL 3000
4 9000003 4000
5 9000004 DK 1500

Land is blank on record 4.

Source 2: Spreadsheet (linked table)

Kreditornr Land Sale
9000000 1000
9000001 DK 2000
9000002 NL 3000
9000003 DE 4000
9000004 DK 8000

Land is blank on record 900000.

Result: Changed records

ID Kreditornr Land Sale
1 9000000 1000
4 9000003 DE 4000
5 9000004 DK 8000

Land is blank on record 1.

How can I do this in a query? Later in the process I need to update Source 1 - Table A with the record set from the result query.

Hope you can help me

Best regards
Morten
 

Morten

Registered User.
Local time
Today, 16:24
Joined
Sep 16, 2009
Messages
53

I tried this SQL code based on the umatched wizard. It's not giving me a result.

Code:
SELECT KreditorData.Id, KreditorData.[Kreditor nummer], KreditorData.[Uniformeret Kreditor CVR], KreditorData.[Seneste års omsætning]
FROM KreditorData LEFT JOIN SyncFil ON (KreditorData.[Seneste års omsætning] = SyncFil.[Seneste års omsætning]) AND (KreditorData.[Uniformeret Kreditor CVR] = SyncFil.[Uniformeret Kreditor CVR]) AND (KreditorData.[Kreditor nummer] = SyncFil.[Kreditor nummer])
WHERE (((KreditorData.[Uniformeret Kreditor CVR])<>[SyncFil]![Uniformeret Kreditor CVR]));
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:24
Joined
May 7, 2009
Messages
19,169
combine the query to make a Union Query:


SELECT KreditorData.Id, KreditorData.[Kreditor nummer], KreditorData.[Uniformeret Kreditor CVR], KreditorData.[Seneste års omsætning]
FROM KreditorData LEFT JOIN SyncFil ON (KreditorData.[Seneste års omsætning] = SyncFil.[Seneste års omsætning]) AND (KreditorData.[Uniformeret Kreditor CVR] = SyncFil.[Uniformeret Kreditor CVR]) AND (KreditorData.[Kreditor nummer] = SyncFil.[Kreditor nummer])
WHERE SyncFil.[Kreditor nummer] Is Null;
UNION ALL
SELECT KreditorData.Id, KreditorData.[Kreditor nummer], KreditorData.[Uniformeret Kreditor CVR], KreditorData.[Seneste års omsætning]
FROM KreditorData RIGHT JOIN SyncFil ON (KreditorData.[Seneste års omsætning] = SyncFil.[Seneste års omsætning]) AND (KreditorData.[Uniformeret Kreditor CVR] = SyncFil.[Uniformeret Kreditor CVR]) AND (KreditorData.[Kreditor nummer] = SyncFil.[Kreditor nummer])
WHERE KreditorData.[Kreditor nummer] Is Null;
 

JHB

Have been here a while
Local time
Today, 17:24
Joined
Jun 17, 2012
Messages
7,732
The below query will do it, sample database attached:
SELECT TableB.Kreditornr, TableB.Land, TableB.Sale
FROM TableA RIGHT JOIN TableB ON TableA.[Kreditornr] = TableB.[Kreditornr]
WHERE ((([TableB].[Land] & [TableB].[Sale])<>[TableA].[Land] & [TableA].[Sale]));
 

Attachments

  • Eksempel.accdb
    424 KB · Views: 72

Morten

Registered User.
Local time
Today, 16:24
Joined
Sep 16, 2009
Messages
53
Thank you for the help.
I'll take a look at both solutions and work with them.
It seems like the last solution is just the one I need.
 

Users who are viewing this thread

Top Bottom