p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

sql_language thread: access Query


Message #1 by "Vani" <vani@a...> on Mon, 25 Mar 2002 10:46:33 +0300
SELECT *

FROM tblStaff

WHERE StaffID in (SELECT staffid

FROM tblAttendance

WHERE date NOT between #3/1/2002# and #3/4/2002#);





---- Original Message ----
From: ken@a...
To: sql_language@p..., 
Subject: RE: [sql_language] Re: access Query
Date: Wed, 27 Mar 2002 12:51:21 +1100

>Try this:
>
>SELECT 
>    a.StaffID, 
>    a.[Name]
>FROM 
>    tblStaff AS a
>WHERE 
>    NOT EXISTS
>    (
>    SELECT 
>        NULL
>    FROM 
>        tblAttendance  AS b
>    WHERE 
>        a.StaffID = b.StaffID
>    AND
>        b.[Date] BETWEEN #2002/3/1# AND #2002/3/4#
>    )
>
>Cheers
>Ken
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>From: "Vani" <vani@a...>
>Subject: [sql_language] access Query
>
>
>Hi All,
>
>I have two tables in Access 2000
>tblStaff , tblAttendance
>
>tblStaff          |   tblAttendance
>
>StaffID             Date
>name               TimeIn  
>
>...                      ..
>
>I want to write a query to display all the staff members who are 
>absent
>in a particular period of time.. lets say between march 1 and march 
>4..
>The staff member name should come even if he is absent for one day in
>that period.
>
>I tried this ..
>SELECT *
>
>FROM tblStaff
>
>WHERE StaffID not in (SELECT staffid
>
>FROM tblAttendance
>
>WHERE date between #3/1/2002# and #3/4/2002#);
>
> 
>
>This works fine, if the staff member is absent for all the days in 
>that
>particular period.. But my requirement is,  even if the staff is 
>absent
>for one day, his name should appear..
>
>
>
>How to do this in a single query or using access 2000
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
>


  Return to Index