calculating number of days per month

[stuckagain1]

I have a table of records that includes a begin date and an end date for a residency. I need to create charts by month for the last ten years, ie Jan 95, Jan 96, Jan 97... Jan 04 on one chart. What needs to be plotted is the total number of residency days in the month, ie a simple example is if 10 people arrived on 1-1-01 and stayed until 1-31-01, then there would be 310 days for Jan 01 (10 * 31 days). but everyone does not arrive at the beginning of the month, nor leave at the end of the month. they arrive and leave anytime of the month, so to the extent they overlap into other months needs to be accounted for.

is there a crosstab query that could handle this? i'm not sure of the expressions.....?

Thanks!

 

[lespaul]

what is the structure of the table that contains the residency information?

Leslie

 

[PaulBricker]

Can you give us a little more info on this. For example, are the residencies likely to span several months or just one month? Could they span years?
For example one residency might go from January 1 to February 15 but the next one might be from January 3 to June 21 and another from January 1 to January 16.

If will make a difference if we have to account for spanning more than 1 month.

Paul

 

[stuckagain1]

thanks--the residencies average between 6 and 8 weeks and there are roughly ~30 people in residence each month...does that help?

there is a res_begin date and a res_end date in the table --both are date fields.

 

[PaulBricker]

Well, this may give you a starting point. Here's what I did.

My query looked like this

SELECT tblDates.StartDate, tblDates.EndDate, DateDiff("m",[StartDate],[EndDate]) AS numDays, nDays([StartDate],[EndDate],[numDays]) AS Expr1
FROM tblDates;

nDays is a Function that looks like this

Function nDays(sdate As Date, edate As Date, y As Integer)

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblDaysInMonth", dbOpenDynaset)



Select Case y
 Case 0
    i = Month(sdate) - 1
     rst.AddNew
     rst(i) = DateDiff("d", sdate, edate)
     rst.Update
 Case 1
    i = Month(sdate) - 1
     rst.AddNew
     rst(i) = DateDiff("d", sdate, DateSerial(Year(sdate), Month(sdate) + 1, 0))
     rst(i + 1) = DateDiff("d", DateSerial(Year(sdate), Month(edate), 1), edate)
     rst.Update
 Case 2

   i = Month(sdate) - 1
    rst.AddNew
    rst(i) = DateDiff("d", sdate, DateSerial(Year(sdate), Month(sdate) + 1, 0))
    rst(i + 1) = DateDiff("d", DateSerial(Year(sdate), Month(sdate) + 1, 1), DateSerial(Year(sdate), Month(sdate) + 2, 0))
    rst(i + 2) = DateDiff("d", DateSerial(Year(sdate), Month(edate), 1), edate)
    rst.Update



End Select
 
End Function

tblDaysInMonth is a table that has twelve field named 1,2,3,4,5,6,7,8,9,10,11, and 12

When I run the query, I don't return a value to the query, but rather I return [red]new records[/red] to the table tblDaysInMonth that contain the number of days in each month between StartDate and EndDate. Now here are the caveats (the issues you will have to work around).

1. You need to start with zero records in tblDaysInMonth when you run the query or you will end up with duplicate records.
2. This function only covers records that span 3 months. Anything span greater than that will not run properly. You can modify the function to cover more months that that but I didn't bother.
3. The function uses the DateSerial() function which contains the Year() function. I grab the value for Year() from the sdate value. This could be a problem (but might not be) if the dates in question start in December and run thru February or March of the following year. I'm mainly worried about picking up LEAP YEAR is the records go from a non-Leap Year to a Leap Year. I'm not sure what to do about it, but you could test the years for sdate and edate to see if the year changes and use another select statement to handle that.

Basically this is just someplace for you to jump off from to see how it handles your data. It isn't a complete solution. Look it over, test it on sample data and post back with specific problems.

Paul

 

[stuckagain1]

thanks very much! I'll give it a try........