A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"NOT EQUAL" query



 
 
Thread Tools Display Modes
  #1  
Old May 23rd, 2004, 06:00 AM
Van T. Dinh
external usenet poster
 
Posts: n/a
Default "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  
Old May 23rd, 2004, 12:27 PM
Chuck
external usenet poster
 
Posts: n/a
Default "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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:44 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.