Find Unmatched Query Question

[ramez75]

Hi,

I been looking around the forum but cant get or pinpoint the way I need to go about this. I might be complicating matters but if it is the case I am sure someone will tell me so and guide me in the right path.

I have 2 tables tbltest and tbltest1 both have the same field names lets say for the sake of simplicity.

tbltest has strName, strDoc, strDocNum, strRev
and

tbltest1 has strName, strDoc, strDocNum, strRev

I want to create a query or something that will compare both tables and give me the rows that has strRev not matching between tbltest and tbltest1.

eg.

tbltest
strDocNum strRev
Doc123 AA
Doc456 AB
Doc985 BC

tbltest1
strDocNum strRev
Doc123 AA
Doc456 CA
Doc985 BC

Newtable will give me
stDocNum strRev strName strDoc
Doc456 CA John Doe Something

I been trying for the past few hours using Find Unmatched Query, duplicates but cant get them to work to give me the results I want

 

[plog]

Based on the explanation and sample data you provided, this SQL will provide you with what you want:

SELECT tbltest.*, tbltest1.*
FROM tbltest INNER JOIN tbltest1 ON tbltest.strDocNum = tbltest1.strDocNum
WHERE tbltest.strRev<>tbltest1.strRev;

 

[ramez75]

Thanks plog,
I ended up using a regular query just as the one you mentioned below. I guess i was looking at it in the wrong way trying to use Find Unmatched query

Based on the explanation and sample data you provided, this SQL will provide you with what you want:

SELECT tbltest.*, tbltest1.*
FROM tbltest INNER JOIN tbltest1 ON tbltest.strDocNum = tbltest1.strDocNum
WHERE tbltest.strRev<>tbltest1.strRev;

 

[ramez75]

I would like to replace in strRev column any "AA" or "0" with "90". How can I go about this. I was thinking to use an Update Query but dont know what to put in the criteria

I was thinking to use the IIF function but the problem is I only want to change if the value is "AA" or "0" otherwise leave it as is

Below what I have

UPDATE tbltest INNER JOIN tbltest1 ON tbltest.strProcNum = tbltest1.strProcNum SET tbltest.strProcRev = tbltest1.strProcRev;

Thanks

 

[ramez75]

I used this and worked

UPDATE tbltest SET tbltest.strProcRev = "90"
WHERE (((tbltest.strProcRev)="AA" Or (tbltest.strProcRev)="0"));