Dates and enrolment

[Peter Bailey]

I have a student table with studentid as pk and an enrolment date field as
date.

I have made a qry to group on date and count the number of enrolments for a
particular day.

What this doesnt give me of course are days where there were no enrolments
ie there are gaps in the dates.

Ideas would be welcome as to how I might get the missing days. My own
thought would be that I might need to store a table of days and then look
for all the days in the date table and those from the enrolment table but
that would require populating a new table and repopulating it with the new
days every time the query parameters are changed.

The query only looks at an intake day as a parameter to give the enrolment
dates over a 20 week period. ie criteria on Enrolment date :[color=blue]
>=dateadd("w" , -20, [Intake Day] And <=[Intake Day].[/color]

This query will be used to get cumulative values over a set period so that
the school can build historical data and forecasts of student bookings.

Thank you in advance

Peter Bailey

[Allen Browne]

Re: Dates and enrolment

Hi Peter.

Yes: create a table of dates.

Create a query that uses an outer join on the date field, so you get all the
dates, and any matches. Group by date from the date table, and count the ID
of the enrolment table.

You can use this code to populate a table named tblDate, with a field named
TheDate with 10 years worth of dates:

Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0). OpenRecordset(" tblDate")
With rs
For dt = #1/1/2004# To #12/31/2013#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter Bailey" <peterbailey@an daluz.fsbusines s.co.uk> wrote in message
news:cg42q9$l4m $1@newsg2.svr.p ol.co.uk...[color=blue]
>I have a student table with studentid as pk and an enrolment date field as
> date.
>
> I have made a qry to group on date and count the number of enrolments for
> a
> particular day.
>
> What this doesnt give me of course are days where there were no enrolments
> ie there are gaps in the dates.
>
> Ideas would be welcome as to how I might get the missing days. My own
> thought would be that I might need to store a table of days and then look
> for all the days in the date table and those from the enrolment table but
> that would require populating a new table and repopulating it with the new
> days every time the query parameters are changed.
>
> The query only looks at an intake day as a parameter to give the enrolment
> dates over a 20 week period. ie criteria on Enrolment date :[color=green]
>>=dateadd("w ", -20, [Intake Day] And <=[Intake Day].[/color]
>
> This query will be used to get cumulative values over a set period so that
> the school can build historical data and forecasts of student bookings.
>
> Thank you in advance
>
> Peter Bailey[/color]