relationship - query

[ghostman via AccessMonster.com]

i'm having trouble in making a query to display training records for trainees.
I want to display who is scheduled for next training session (NTSMonth) and
the trainees who has NO training records.

Displaying the trainees scheduled for next training session is easy, but the
trainees with NO training records giving me a hard time.

I tried combining these tables to make a query but no luck.
The main thing is to display all Training records with or without training
history. In this way, its easier to find who are due for training session and
enroll trainees with NO training record.

using the relationship mentioned below, the tables shows correct info. I can
see trainees with no training record IF i open them ONE-BY-ONE. That is not
what the user wanted. how can i do this?

These are my tables:

1) Trainees

- TraineeID (PK)
- FirstName
- LastName
- JobTitle
- Company Name
- (other info)

2) Training Session
- TSID (PK)
- ModuleName
- SessionDate
- SessionTimeF
- SessionTimeT
- SessionVenue
- InstructorID


3) Training Record
- TRID (PK)
- TraineeID (FK)
- TSID (FK)
- NTSMonth (Next Training Session month)
- NTSModule (Next Training Session module)
- NoShow


Relationship:
- Trainees.TraineeID => Training Record.TraineeID = One-to-Many
- Training Session.TSID => Training Record.TSID = One-to-Many


i hope i made my self clear...this is the only problem im having, if this
problem is solved...my database is complete...and they will leave me alone
lol!

 

[Douglas J. Steele]

Relationships won't help you determine non-existence.

There are two common options:

SELECT TraineeID, FirstName, LastName, JobTitle, Company Name
FROM Trainees
WHERE TraineeID NOT IN (SELECT DISTINCT TraineeID
FROM [Training Record])

or

SELECT TraineeID, FirstName, LastName, JobTitle, Company Name
FROM Trainees LEFT JOIN [Training Record]
ON Trainees.TraineeID = [Training Record].TraineeID
WHERE [Training Record].TraineeID IS NULL

 

[ghostman via AccessMonster.com]

that was fast...

the first one works perfect! it displays the trainees with no training record.