"NOT EQUAL" query

[Chuck]

I have two tables (financial and personnel), each containing employee social
security numbers. I created a simple report using a join to find the equal
social security numbers between the two tables and display that information.
Now I need to create a similar report showing data that has no match from
the financial table to the personnel table. Such an example would be a
mis-typed social security number in the financial table. I can't seem to do
this as there doesn't seem to be a NOT EQUAL join. Any help would be
appreciated.

 

[Van T. Dinh]

Use a Left (Outer) Join. The SQL String should be something like:

SELECT tblFinancial.*
FROM tblFinancial LEFT JOIN
tblPerson ON tblFinancial.SSN = tblPerson.SSN
WHERE tblPerson.SSN Is Null

 

[Chuck]

Thanks for the feedback, but I can't seem to get it to work. I tried adding
a dummy record to the financial table with an intentional bad social
security number and the report does not show it. Here is the sql created:

SELECT tblODLTravel.FC, tblODLTravel.RCCC, tblODLTravel.[EEIC (All 5)],
tblODLTravel.ESP, tblODLTravel.[FY (1 Char)], tblODLTravel.[Document
Number], tblODLTravel.[Return Date], tblODLTravel.SSAN AS tblODLTravel_SSAN,
tblODLTravel.PSR, tblODLTravel.DSR, tblODLTravel.AEU, tblODLTravel.EFF_DATE,
tblPersonnel.SSAN AS tblPersonnel_SSAN, tblPersonnel.NAME, tblPersonnel.SC,
tblPersonnel.RNK, tblODLTravel.BPAC
FROM tblPersonnel LEFT JOIN tblODLTravel ON tblPersonnel.SSAN =
tblODLTravel.SSAN
WHERE (((tblPersonnel.SSAN) Is Null));

As you can see, there is a lot of information being pulled from the travel
table (this is the financial table) and the personnel has minimal
information but contains accurate employee data. If the financial table has
an incorrect social security number, there should be no match in the
personnel table and the results should be displayed.

Input?

Thanks.

 

[Van T. Dinh]

You got the Left Join wrong way around. The way you have at the moment, the
Query will show Records in tblPersonnel that don't have related Records in
tblODLTravel.

Change the FROM Clause to:

FROM tblODLTravel LEFT JOIN tblPersonnel
ON tblODLTravel.SSAN = tblPersonnel.SSAN
WHERE (((tblPersonnel.SSAN) Is Null));

 

[Chuck]

That was it.... (whew!) , thank you very much.

You got the Left Join wrong way around. The way you have at the moment, the
Query will show Records in tblPersonnel that don't have related Records in
tblODLTravel.

Change the FROM Clause to:

FROM tblODLTravel LEFT JOIN tblPersonnel
ON tblODLTravel.SSAN = tblPersonnel.SSAN
WHERE (((tblPersonnel.SSAN) Is Null));

--
HTH
Van T. Dinh
MVP (Access)

Thanks for the feedback, but I can't seem to get it to work. I tried adding
a dummy record to the financial table with an intentional bad social
security number and the report does not show it. Here is the sql created:

SELECT tblODLTravel.FC, tblODLTravel.RCCC, tblODLTravel.[EEIC (All 5)],
tblODLTravel.ESP, tblODLTravel.[FY (1 Char)], tblODLTravel.[Document
Number], tblODLTravel.[Return Date], tblODLTravel.SSAN AS tblODLTravel_SSAN,
tblODLTravel.PSR, tblODLTravel.DSR, tblODLTravel.AEU, tblODLTravel.EFF_DATE,
tblPersonnel.SSAN AS tblPersonnel_SSAN, tblPersonnel.NAME, tblPersonnel.SC,
tblPersonnel.RNK, tblODLTravel.BPAC
FROM tblPersonnel LEFT JOIN tblODLTravel ON tblPersonnel.SSAN =
tblODLTravel.SSAN
WHERE (((tblPersonnel.SSAN) Is Null));

As you can see, there is a lot of information being pulled from the travel
table (this is the financial table) and the personnel has minimal
information but contains accurate employee data. If the financial table has
an incorrect social security number, there should be no match in the
personnel table and the results should be displayed.

Input?

Thanks.