Query which groups months between startdate and enddate

[HoganGroup]

Hi fellows: I am at a complete lost as to how to construct a query or report to answer this question. Any help is greatly appreciated. The question I need to answer is this:

Identify the SchID and month for each SchID/Month combo for which there is not at least one associated AttendanceDate between that SchID's StartDate and EndDate.

Table structure:

tblSchedules
------------------
SchID
StartDate
EndDate

tblAttendance
--------------------
AttendanceID
SchID
AttendanceDate
AttendanceStatus

 

[PWise]

create a digits table
1 field (Digitid) type number with numbers from 1 till .......(as many as the largest numbers between StartDate and EndDate)

Select Schid ,StartDate,EndDate,dateadd("m",1,Digitid)
from tblSchedules, digits 
left join tblAttendance
on tblAttendance.Schid =tblSchedules.Schid 
where tblAttendance.Schid is null
and dateadd("m",1,Digitid) between StartDate and EndDate

 

[PWise]

sorry need some tweaking

Select Schid ,StartDate,EndDate,dateadd("m",Digitid,StartDate)
from tblSchedules, digits 
left join tblAttendance
on tblAttendance.Schid =tblSchedules.Schid 
and month(AttendanceDate)=month(dateadd("m",Digitid,StartDate))
and year(AttendanceDate)=year(dateadd("m",Digitid,StartDate))
where tblAttendance.Schid is null
and dateadd("m",Digitid,StartDate) between StartDate and EndDate

not tested

 

[HoganGroup]

Thanks, pwise, for your reply. I'm sorta following what this will do, but not entirely. I created tblDigits of 1-1825 but could not create the query (reported: error on JOIN operation). Just to be clear, this is the output result I am looking for. (Can be query or a report generated by script.)

Thanks again!

INPUT
tblSchedules
-------------------
SchID, StartDate, EndDate
10, 1/1/07, 2/28/07
20, 1/1/07, 3/31/07
30, 1/1/07, 3/31/07

tblAttendance
--------------------
AttID, SchID, AttDate, AttStatus
444, 10, 1/1/07, "Present"
445, 10, 2/7/07, "Present"
446, 20, 1/1/07, "Present"
447, 20, 1/5/07, "Present"
448, 30, 2/1/07, "Present"
449, 30, 3/6/07, "Present"

OUTPUT
SchID:20, Month:2/07
SchID:20, Month:3/07
SchID:30, Month:1/07

LOGIC
SchID:10 has at least one record in tblAttendance for every month it's suppose to (the months between it's startdate and enddate, 1/07 and 2/07) so it is not output
SchID:20 should have at least one day of attendance in 2/07 and 3/07 but does not, so those months are output
SchID:30 should have at least one day of attendance in 3/07 but does not, so that month is output