M
martin_watts
I posted the following on this group a few weeks ago:
I have a table containing a start date and an end date for training courses.
I need to extract quarterly statistical information.
[snip]
(NB dates in dd/mm/yy format)
Start End Startqtr Endqtr
27/01/04 27/04/04 12004 22004
30/01/04 15/07/04 12004 32004
10/07/04 01/10/04 32004 42004
I want the user to be able to enter (as in a parameter query) the quarter
and year so the query will return records where the start or end date is in
the selected quarter and where the course runs through that quarter.
[snip]
and Chris Nebinger kindly posted the following solution:
[snip]
Select * from TrainingCourses
WHERE
[Start] <= DateSerial([Enter Year],([Enter Quarter]-1)
*3+1,1) AND [End] >= DateSerial([Enter Year],([Enter
Quarter]-1)*3+1,1)
Which worked like a dream ... however my 'customer' has now decided to
complicate the matter somewhat.
In the quarterly stats we need to count the students who have progressed.
They can only progress once. I have put in a date field where the date of
progression can be entered. So what I need to do is count the students who
have progressed during a selected quarter (entered as a parameter), where
the course either starts, ends or runs through the selected quarter. It
could be that a student starts a new course in a particular quarter and
there record would be counted in the stats for that quarter, but I do not
want to count them in the progression stats if they progressed in a previous
quarter. Does that make sense?
TIA & BTW thanks for taking the time for reading this lot!
Martin Watts
I have a table containing a start date and an end date for training courses.
I need to extract quarterly statistical information.
[snip]
(NB dates in dd/mm/yy format)
Start End Startqtr Endqtr
27/01/04 27/04/04 12004 22004
30/01/04 15/07/04 12004 32004
10/07/04 01/10/04 32004 42004
I want the user to be able to enter (as in a parameter query) the quarter
and year so the query will return records where the start or end date is in
the selected quarter and where the course runs through that quarter.
[snip]
and Chris Nebinger kindly posted the following solution:
[snip]
Select * from TrainingCourses
WHERE
[Start] <= DateSerial([Enter Year],([Enter Quarter]-1)
*3+1,1) AND [End] >= DateSerial([Enter Year],([Enter
Quarter]-1)*3+1,1)
Which worked like a dream ... however my 'customer' has now decided to
complicate the matter somewhat.
In the quarterly stats we need to count the students who have progressed.
They can only progress once. I have put in a date field where the date of
progression can be entered. So what I need to do is count the students who
have progressed during a selected quarter (entered as a parameter), where
the course either starts, ends or runs through the selected quarter. It
could be that a student starts a new course in a particular quarter and
there record would be counted in the stats for that quarter, but I do not
want to count them in the progression stats if they progressed in a previous
quarter. Does that make sense?
TIA & BTW thanks for taking the time for reading this lot!
Martin Watts