Unmatched fields

[Guest]

Two tables

Table ONE - Field 1 - CustID is Primary Key
Field 2 - Status Code

Table TWO - Field 1 - CustID is Primary Key
Field 2 - Status Code

What I need to do is compare Field 2 in both tables for unmatched data with
the two primary keys joined with a Left Join from Table One to Table Two.

I have tried many ways without success. Field two does not have unique
values.

1234 3 1234 3
4567 3 4567 4

I need 4567 to show up in my list.

Thank you in advance.

 

[Douglas J Steele]

SELECT Table1.CustID, Table1.StatusCode, Table2.StatusCode
FROM Table1 INNER JOIN Table2
ON Table1.CustID = Table2.CustID
WHERE Table1.StatusCode <> Table2.StatusCode

If you're not comfortable with SQL and want to build the query through the
query builder, add both tables, and join them on CustID.

Drag the fields down into the grid, and under the Status Code field for the
first table, put

<> Table2.StatusCode

as the criteria.

 

[Guest]

I tried that, but every time I leave the field - Access automatically adds "
" around the query statement. When I try to run it that way - an error msg
pops up - Data Type Mismatch. I have checked both tables and both of those
fields are the same. I have them both set at - number. Any suggestions?

 

[Guest]

<>[Table One].[Field 2]

I tried putting it in the other Field 2 and Access added the brackets
(instead of quotes) go figure? Anyway, it worked from Table ONE.field two
vs. the Table TWO.field 2.

Thanks for your help.

 

[Douglas J. Steele]

What are you trying to do with that SQL statement?

You can't type it into a module or macro: it's SQL, and has to be typed into
a query.

 

[Guest]

I did use it in a query. I tried to paste a screen shot here for you, but
the paste function is subdued. Again, thank you for your assistance.

 

[Douglas J. Steele]

Were you in SQL view at the time? (Look under the View menu when the query's
open in Design mode)

 

[Guest]

Thanks, I have only used the SQL View a couple of times.

Putting in the script you supplied (changing script to actual names of
tables and fields of course) worked.

Your earlier suggestion also worked.

<>[Table One].[Field 2]

I tried putting this in the other Field 2 and Access added the brackets
(instead of quotes) go figure? Anyway, it worked from Table ONE.field two
vs. the Table TWO.field 2.

Thanks for your help.