playing with dates

[nomojo]

The Goal:

I am attemting to find something called shelter nights. Shelter nights is determined by taking the number of nights a household stayed in the shelter and multiplying by the number of people in the household. its quite an easy formula, and i can do it quite well in Access. My problem is that i need to know the specific number of shelter nights per month, not per household.

The Setup:

I have 4 fields that are relevent. Number of adults in household (AIH), kids in household (KIH), Shelter Start Date (SSD), and Shelter End Date (SED). I can determine the difference between SSD and SED easily enough, and there is no problems, providing SSD and SED are within the same month. Which rarely happens. Usually someone will come in like may 5th, and leave like june 9th. I need to know how many nights from may and how many nights from june. This information is not going in a table, its for a report, so i wil be generating it in a query.

I have no clue how to do this part. please help.

 

[PaulO]

You will need to set up a CALENDAR table with the following fields:

Date (date field)
Year (text field)
Month (text field)

Off this Table you can build a SELECT Query with TOTALS to establish the first and last day of each month (GROUP on Year and Month, MIN/MAX on Date)

e.g. 28/06/2012;2012;06

This will enable you, by linking [SSD] and [SED] to [CALENDAR]![DATE], to establish the Start and End Months. Link this query, then, to the MIN/MAX date query (above) to establish the number of days in each month.
Expr1: [MAX]-[SSD]+1
Expr2: [SED]-[MINDATE]+1
then add the two sums together ...

Is there ever a time when a household spans THREE months? e.g. 31/05/2012 to 01/07/2012