Query Help Relation

[Guest]

I have table1 (name, ss#) table2 (date trained) they are related 1 to many.
Is there a way I can create a query to see all people who are NOT trained,
meaning no dates have been entered into table2
Table1 Table2
Ex: John 01/01/2004
Joe 01/02/2004
Jesse
Jackson 01/03/2004

I only want to see "Jesse" (note: table2 has never been entered, but I need
to know this)

 

[Guest]

Yes, but of course you will have to add a field to Table2 to relate to Table1
(such as NameID)try:

SELECT Table1.Name
FROM Table1 LEFT JOIN Table2
ON Table1.NameID = Table2.NameID
WHERE Table2.Date Is Null

Your tables would look something like:

Table1
NameID Name
1 John
2 Joe
3 Jesse
4 Jackson

Table2
NameID Date
1 1/1/2004
2 1/2/2004
4 1/3/2004

Of course, you could have multiple date entries for some people, the query
would return only the names from the names table that did not exist in the
training dates table. Similarly, you may have a field in training 2 to store
the CourseID from a third table of available training courses.

HTH, Ted Allen