If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"NOT EQUAL" query
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) "Chuck" wrote in message .. . 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. |
#2
|
|||
|
|||
"NOT EQUAL" query
That was it.... (whew!) , thank you very much.
"Van T. Dinh" wrote in message ... 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) "Chuck" wrote in message .. . 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. |
Thread Tools | |
Display Modes | |
|
|