querying a negative 2

[marct]

Hi,

I have a problem that sounded easy at first, but I can't seem to find the query or queries to get the job done. I've got a (Names) table that holds an ID#, First Name, and Last Name. I've got another table (Activities) that holds activity information for people, with a one-to-many relationship between the tables through the ID# field. What I need to do is print a report for everyone who has NOT performed an activity yet (they would be the ones with no activiies currently entered in the Activities table). I can't seem to figure out how to query the table(s) to get the right information. It would be everyone who is listed in the Names table but has no entry in the Activities table. It's like detecting information that's not there. Can anyone help?

Thanks in advance,

Marc Tower

 

[hennep]

This should work:

SELECT Names.FirstName, Names.LastName
FROM Names LEFT JOIN Activities ON Names.ID = Activities.lngPersonID
WHERE (((Activities.strActivity) Is Null));

 

[hennep]

Or this one:

SELECT Names.FirstName, Names.LastName
FROM Names
WHERE (((Exists (select lngPersonID from activities where [lngpersonid] =[names].[id]))=False));

 

[marct]

Thanks for your help, hennep. The second one works just the way I need it to.

Much appreciated,

Marc