This query just is not working

  • Thread starter Thread starter Frederick Wilson
  • Start date Start date
F

Frederick Wilson

I am trying to find all the people who have NOT completed training.

There are over 600 people in the database. When I run this query I only
get 385 records.

Even when I use j.NameCode Is Null or = Null it gives me NO records.

SELECT tblPersonnel.LAST, jtblNameToEvent.NameCode,
jtblNameToEvent.EventCode, tblPersonnel.NameCode

FROM tblPersonnel LEFT JOIN jtblNameToEvent

ON tblPersonnel.NameCode = jtblNameToEvent.NameCode

WHERE (((jtblNameToEvent.EventCode)=1))

ORDER BY tblPersonnel.LAST

UGH, What am I doing wrong?

I think this has something to do with the join table for the many-many
relationship. All the names in tblPersonnel may be in the join table at
least once.
 
No sure with Access Left Join, but did you try:

WHERE (jtblNameToEvent.EventCode is Null or jtblNameToEvent.EventCode=1)

OR:

WHERE (jtblNameToEvent.NameCode is Null or jtblNameToEvent.EventCode=1)

S. L.
 
Sylvain said:
No sure with Access Left Join, but did you try:

WHERE (jtblNameToEvent.EventCode is Null or jtblNameToEvent.EventCode=1)

OR:

WHERE (jtblNameToEvent.NameCode is Null or jtblNameToEvent.EventCode=1)

S. L.
Click to expand...
HMMMMM, I haven't tried that but I am having a hard time getting my mind
around that suggestion and how it should work
 
I assume that all people are in tblPersonnel table, but that a person is in
tblNameToEvent table only if the person has attended at least one training
event?

Assuming that this is correct, then your query will not work unless it
returns a record for every person in tblPersonnel, with a flag for whether
that person has taken the specific training class for which you're seeking.

Try something like this:

SELECT * FROM tblPersonnel
WHERE tblPersonnel.NameCode NOT IN
(SELECT T.NameCode FROM
jtblNameToEvent AS T WHERE
T.EventCode = 1);
 
Not sure but try:

SELECT P.[LAST], P.NameCode
FROM tblPersonnel AS P LEFT JOIN
(
SELECT NameCode
FROM jtblNameToEvent
WHERE (((jtblNameToEvent.EventCode)=1))
) AS vtPAE1
ON P.NameCode = vtPAE1.NameCode
WHERE (vtPAE1.NameCode Is Null)
ORDER BY P.LAST

Logically, the inner Select gives you the NameCodes for people already
attending EventCode 1. The Left Join with the criteria in the outer Select
will select NameCodes in tblPersonnel that _not_ in the list of NameCodes of
people attending EventCode 1. This should be what you want except I may
have syntax errors in the SQL.

Note: vtPAE1 = (virtual table for) People Attending Event 1

Alternatively, you can use the (Not) In clause in the Query. Check Access
Help (the JET SQL Reference section) for the In keyword.
 
Ken said:
I assume that all people are in tblPersonnel table, but that a person is in
tblNameToEvent table only if the person has attended at least one training
event?

Assuming that this is correct, then your query will not work unless it
returns a record for every person in tblPersonnel, with a flag for whether
that person has taken the specific training class for which you're seeking.

Try something like this:

SELECT * FROM tblPersonnel
WHERE tblPersonnel.NameCode NOT IN
(SELECT T.NameCode FROM
jtblNameToEvent AS T WHERE
T.EventCode = 1);
Click to expand...
THAT IS GREAT. May I ask one small question, how do I display the Event
Code with the name?
 
I am trying to find all the people who have NOT completed training.

There are over 600 people in the database. When I run this query I only
get 385 records.

Even when I use j.NameCode Is Null or = Null it gives me NO records.

SELECT tblPersonnel.LAST, jtblNameToEvent.NameCode,
jtblNameToEvent.EventCode, tblPersonnel.NameCode

FROM tblPersonnel LEFT JOIN jtblNameToEvent

ON tblPersonnel.NameCode = jtblNameToEvent.NameCode

WHERE (((jtblNameToEvent.EventCode)=1))

ORDER BY tblPersonnel.LAST

UGH, What am I doing wrong?
Click to expand...

The fact that you're putting a criterion on the EventCode field in
jtblNameToEvent is ipso facto excluding all NULL records from that
table.

I'd suggest a Subquery:

SELECT tblPersonnel.Last
FROM tblPersonnel
LEFT JOIN (SELECT jtblNameToEvent.NameCode FROM jtblNameToEvent WHERE
EventCode = 1) AS X
ON X.NameCode = tblPersonnel.NameCode
WHERE X.NameCode IS NULL;

John W. Vinson[MVP]
 
Try this:

SELECT tblPersonnel.*,
(DLookup("EventName", "tblEvent", "EventCode=1")) AS EventName
FROM tblPersonnel
WHERE tblPersonnel.NameCode NOT IN
(SELECT T.NameCode FROM
jtblNameToEvent AS T WHERE
T.EventCode = 1);
 
Yeah, you're probably right, this piece of code will not work in your case.
After re-reading your post, I think that you should use the Exists statement
instead (or the NOT IN); something like:

SELECT * FROM tblPersonnel as P
WHERE Not Exists (Select * from jtblNameToEvent E Where E.NameCode =
P.NameCode
AND E.EventCode = 1)

However, in your case, the NOT IN - shown in another post - will probably
give you a better performance.

S. L.
 
Back
Top