Must Find Clients Who Haven't Attended Class Sessions 1

[rdpress]

My clients have to attend 15 diffent class sessions. I have 3 tables, Client, Session, SessionHistory. I've created a One-toMany relationships between Client and SessionHistory,and Session and SessionHisotry.

Tables

Client: Key = ClientID
ClientID
FirstName
LastName

Session: Key = SessionID
SessionID
SessionName

SessionHistory: Key = ClientID & SessionID
ClientID
SessionID
DateAttended



I need to make a list showing what sessions each client HAS NOT attended. Please help.

 

[jjonesal]

This is probably the long way around this....I'm sure I could do it in one query if I spent a little bit, but:

Create one query - Query1
SELECT Client.ClientID, Session.SessionID, Session.SessionName
FROM [Session], Client
ORDER BY Client.ClientID, Session.SessionID;

Notice there is no WHERE clause - this gives us all possible combinations of Client and Session.


Create another query
SELECT Query1.ClientID, Query1.SessionID, Query1.SessionName
FROM Query1 LEFT JOIN SessionHistory ON (Query1.SessionID = SessionHistory.SessionID) AND (Query1.ClientID = SessionHistory.ClientID)
WHERE (((SessionHistory.ClientID) Is Null))
ORDER BY Query1.ClientID, Query1.SessionID;

this looks at all possible combinations joined to the session histor (the actual combinations) and gets only those not attended.

I'll have to give it some thought to how to do this with one query.....

J. Jones
jjones@cybrtyme.com