Query no records with in a date range

[Tequila_Mockingbird]

I'm having trouble with a query and I was hoping someone here could help.

I have a DB that is tracking volunteer hours at a museum. I need to write a query that will display all volunteers that have not worked with in a date range (or maybe no work after "this date"). I have one table called Volunteer Information with Volunteers Names and another table that is Volunteer Time that had the Volunteer ID and Work date. I know how to report all volunteers that have no Volunteer Time ever but also want the names of people that haven't worked recently.

Thanks in advance for any help!

 

[georgedwilkinson]

You could potentially use the "NOT IN" sql clause or do an outer join and in your where clause, filter on rows with null in the hours table/column.

 

[spaddhu]

I dunno if this may be combined into one step. But here is what you can do.
First let me tell you the tables I'd created.

Vol_Info : Vol_ID,Vol_Name
Vol_Time : Vol_ID,Work_Date,Time_Worked

Create a query SubQry1 as
SELECT Vol_Time.Vol_ID, Vol_Time.Work_Date, Vol_Time.Time_Worked
FROM Vol_Time
WHERE (((Vol_Time.Work_Date)>#9/23/2009#));

that filters the records for the required time range, ie greater than a date; or change it for between date1 and date2.

Next create the actual query as
SELECT Vol_Info.Vol_Name, Sum(SubQry1.Time_Worked) AS SumOfTime_Worked
FROM Vol_Info LEFT JOIN Query4 ON Vol_Info.Vol_ID = SubQry1.Vol_ID
GROUP BY Vol_Info.Vol_Name
HAVING (((Sum(SubQry1.Time_Worked)) Is Null));

 

[spaddhu]

This should work if your Time table does not track volunteers who have not booked time for that day.
Instead, if the Time table tracks all volunteers, for all dates, say with 0 as working hours, for the days they didnt book, then change the last clause in the actual query from Is Null to =0 .

Do get back if this works or doesn't work.

 

[Tequila_Mockingbird]

Thanks for your help guys... It definitely pointed me in the right direction. I ended up with a query and sub query and used "WHERE NOT EXISTS" to find the names I was looking for.