help with a query 2

[netooi25]

i have a table called tblPeople in my database that keeps track of people.
I use a autonumber field for a primary key..

I have a 2nd table called tblattendance with 2 fields.
I was using PersonID(autonumber from tblPeople), Date as primary key in this table.

I dont know if this is possible right now the way i have the tables designed.

I can easily create a query to find out what people were present on a certain date, but the opposite is giving me trouble. The query i want to do is selecting all the people in tblPeople that were not present on a particular date. Any help will be appreciated.

Thanks,
Netooi

 

[r937]

there are two ways to do it:

select P.PersonID
  from tblPeople as P
left outer
  join tblattendance as A
    on (
       P.PersonID
     = A.PersonID
   and A.[D4te] = '2004-08-31'
       )
 where A.PersonID is null

or

select P.PersonID
  from tblPeople as P
 where not exists
       ( select 1 
           from tblattendance
          where PersonID
              = P.PersonID
            and [D4te] = '2004-08-31'
       )

note i use [D4te] because Date is a reserved word

rudy

http://r937.com/

SQL Consulting

 

[lespaul]

take the query that gives you the results you want, modify it so it only gets the personID and use it in a subquery (your query is red):

SELECT blah, blah from table WHERE PersonID NOT IN([COLOR=red]SELECT PersonID from Somewhere WHERE whatever = 'This'[/color])

Leslie

 

[netooi25]

sweet, thank you very much.