Counting Grouped Records

CutAndPaste

Registered User.
Local time
Today, 21:49
Joined
Jul 16, 2001
Messages
60
I've got a report containing information on Building occupancy. Each building has rooms and each room has occupancies. I've designed a query to bring together all of the required information.

Buildings have a URN of BuildingID
Rooms have an URN of RoomID
Occupancies have an URN of OccupancyID
Cients have a URN of ClientID

I've grouped my report on Buildings as the top level of grouping, with Rooms as the next level and the detail showing the Occupancies.,
Code:


Building1
Room1
Occupancy1 28 Days Client1
Occupancy2 300 Days Client2
Occupancy3 37 Days Client3
Total Occupancy 365 Days
Room2
Occupancy4 340 Days Client4
Occupancy5 20 Days Client5
Total Occupancy 360 Days
Room3
Occupancy6 345 Days Client6
Total Occupancy 345 Days




In the above example, the report period is a year (365 days) so the total possible days is 365 x Number of Rooms.
My occupancy calculations work fine on an indivdual Room basis where they may only be one or several occupancies.

However, when I want to do the calculations for each building (i.e. based on all of the rooms in the building), I can't get the count of the rooms, if I use "Count([RoomID])" it seems to count a RoomId for each occupancy (which I think it must be getting from the underlying query where each occupancy is assigned to a room). I've tried referencing another text box on the report that's bound to RoomID "=Count(Reports![MyReport]![txtRoomId])" but this doesn't return the correct figure.

At the bottom of each Building Footer, I want to work out % occupancies and so on for each building. E.g.

Code:


Building1 Occupancy Figures
DaysInReportingPeriod 365
PossibleOccupancyDays = 1095 (this is 365 days x 3 rooms in this example)
ActualOccupancyDays = 1070 (this is sum of all Occupancy days.
Percentage Occupancy = 97%




How can I get a room count count for each building so that I can get these figures? or am I doing something silly??

tx,

Simon
 
Take a look at the attachment file - I made few adjustments with the file.

hth,
Michael
 

Attachments

Users who are viewing this thread

Back
Top Bottom