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

[QVeen72]

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

Hi,

what does this mean :" for which there is not at least one associated"..?

Post some Test data and required output, as your question is very confusing...

Regards
Veena

[HoganGroup]

Yes, it is very, very confusing. By associated I mean the primary key in tblSchedules associated with its foreign key in tblAttendance. Here's some sample data:

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