Counting Bed Nights 2

[crozier]

I'm working on a database for a Retirement Home. Each client is assigned a "Bed Type": Dementia, Rest Home, Hospital, Respite. Each "Bed Type" can be assigned either Private or Subsidised. At the end of each month I need to be able to count how many nights each type of there have been allocated and whether for Private or Subsidised. Anyone know haw I can do this?

 

[BoxHead]

DCount should work for this.

Take a look at MS Help for setting up the criteria for each instance you need.


HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[JerryDennison]

While DCount() could work it is not efficient. Domain aggregate functions are notoriously poor performance wise. You should use a totals query instead. You can group by on your Bed Type and Count on your date fields.

 

[crozier]

Thanks Guys, I thought that a totals query would be the way to go. I've managed to query on the numbers of beds but I'm not sure of how to total for the month. We record the date of Admission and Discharge and I'm sure that we can assume an empty Discharge field is assigned a particular date (today?) but I'm just not sire where to go from here.

 

[JerryDennison]

You can take the date field and use it for grouping as well. To limit it to months use Format([DateField], "yyyy/mm&quot. I would use a seperate field based on the date if you want to limit the query (i.e. use a parameter) to a specific date range.

 

[crozier]

Thanks again Jerry. How do I get Access to work out the number of nights a bed has been allocated? Maybe I'm not asking correctly, I guess it's like a Hotel needing to know its occupancy rate each month. How would I know how many nights a room has been used? Sorry if I'm being a bit vague but it's alrady Friday here in NZ......

 

[JerryDennison]

You would compare the count to the number of days in that month. To get the number of days in a month you would use the following formula:

DateDiff("y", DateSerial(Year([DateField]), Month([DateField]), 1), DateSerial(Year([DateField]), Month([DateField])+1, -1))