Group level counts of Buildings & Rooms wrong :-(

[Guest]

I'm trying to count the numbers of Rooms at any given Grouping level on a
report, so there's so many rooms in a building, so many in a groups of
buildings and so many groups of buildings in each region.

To count the rooms in a building I've got a text box in the room header,
RmCount. Set the control value to "=1". with a running count over the group.

I've got a text box in your building footer, RmTotal. Set to "=[RmCount]".
This all works fne counting the rooms in a building.

However, if I try to use the same method for the other grouping levels, the
higher levels are NOT counting the room numbers properly, but carrying them
forward and adding them over the whole report. I've tried every combination
of "running sum" yes/no and "over all" and "over Group" but can't get it to
work.

I'm still having trouble getting an accurate count of the rooms in each
Building Group and in each Region

I can get the sum for each building using the method described above but it
all seems to fall apart when I try to use this method for higher levels of
grouping. What I'm aiming for is something like the below - but in this
example I've not put in the the occupations of each room - they may just have
1 occupation during the whole time or several, so I need to count the number
and length of each of the occupancies (can't be more than a 365 days per
single room!) .

The report is based on a query that supplies all of the required information
(but has a row for each occupation). The report is dynamically sorted/grouped
based on a value in a dropdown combo so there are no "fixed" field name sort
levels. and there is calso code that hides/shows the differnent grouping
levels as the user wants to choos bnetween a regional, building group and
building summaries (not sure if this has any bearing on the calculations but
it's in the mix anyway..)
-------------------
North Region
Building Group A
BuildingA1
Room1
Room2
Room3
SubTotal Building A1 = 3 Rooms
BuildingA2
Room1
Room2
Room3
Room4
SubTotal Building A2 = 4 Rooms
SubTotal Building Group A = 7 Rooms
Building Group B
BuildingB1
Room1
Room2
SubTotal Building B1 = 2 Rooms
BuildingB2
Room1
Room2
Room3
Room4
SubTotal Building B2 = 4 Rooms
SubTotal Building Group B = 6 Rooms
Sub Total North Region = 13 Rooms
then .... South, East & West Regions etc..

Grand total All Rooms = Sum of all region totals
----------------

Help greatly appreciated on this...

Would it work better if the individual room occupancies were based on
sub-reports? just a thought...

Thanks,

Simon

 

[Duane Hookom]

Is there a reason why you don't count the number of rooms with a text box
like:
=Count(*)
This same expression should work in each group footer to count the number of
detail records in the "group".

If this doesn't work, come back with a few sample records and expected
counts.

--
Duane Hookom
MS Access MVP

I'm trying to count the numbers of Rooms at any given Grouping level on a
report, so there's so many rooms in a building, so many in a groups of
buildings and so many groups of buildings in each region.

To count the rooms in a building I've got a text box in the room header,
RmCount. Set the control value to "=1". with a running count over the
group.

I've got a text box in your building footer, RmTotal. Set to "=[RmCount]".
This all works fne counting the rooms in a building.

However, if I try to use the same method for the other grouping levels,
the
higher levels are NOT counting the room numbers properly, but carrying
them
forward and adding them over the whole report. I've tried every
combination
of "running sum" yes/no and "over all" and "over Group" but can't get it
to
work.

I'm still having trouble getting an accurate count of the rooms in each
Building Group and in each Region

I can get the sum for each building using the method described above but
it
all seems to fall apart when I try to use this method for higher levels of
grouping. What I'm aiming for is something like the below - but in this
example I've not put in the the occupations of each room - they may just
have
1 occupation during the whole time or several, so I need to count the
number
and length of each of the occupancies (can't be more than a 365 days per
single room!) .

The report is based on a query that supplies all of the required
information
(but has a row for each occupation). The report is dynamically
sorted/grouped
based on a value in a dropdown combo so there are no "fixed" field name
sort
levels. and there is calso code that hides/shows the differnent grouping
levels as the user wants to choos bnetween a regional, building group and
building summaries (not sure if this has any bearing on the calculations
but
it's in the mix anyway..)
-------------------
North Region
Building Group A
BuildingA1
Room1
Room2
Room3
SubTotal Building A1 = 3 Rooms
BuildingA2
Room1
Room2
Room3
Room4
SubTotal Building A2 = 4 Rooms
SubTotal Building Group A = 7 Rooms
Building Group B
BuildingB1
Room1
Room2
SubTotal Building B1 = 2 Rooms
BuildingB2
Room1
Room2
Room3
Room4
SubTotal Building B2 = 4 Rooms
SubTotal Building Group B = 6 Rooms
Sub Total North Region = 13 Rooms
then .... South, East & West Regions etc..

Grand total All Rooms = Sum of all region totals
----------------

Help greatly appreciated on this...

Would it work better if the individual room occupancies were based on
sub-reports? just a thought...

Thanks,

Simon

 

[Guest]

Duane,

Is there a reason why you don't count the number of rooms with a text box
like:
=Count(*)
This same expression should work in each group footer to count the number of
detail records in the "group".

The records in the underlying query is actually the number of "occupations"
on a room (it includes an OccupationURN, a FK OccupierID, a FK RoomId & some
Start/End dates etc) - some may have 1 occupation per room for the reporting
period, whilst others may be rented out (say) 3 times - this would result in
3 Occupation records in the query for the one room, but in the groupings I
want I to count the number of rooms.

If this doesn't work, come back with a few sample records and expected
counts.

If I add in the occupancy details I would look like this - the Room Count is
NOT the same as the Occupier Count

North Region
Building Group A
BuildingA1
Room1
Occupier1
Occupier2
Room2
Occupier3
Room3
Occupier4

SubTotal Rooms in Building A1 = 3
SubTotal Occupiers in Building A1 = 4

Thanks,


Simon

 

[Duane Hookom]

When I get multiple levels where I want to count larger groups, I will often
create extra totals queries to include in the report's record source. For
instance, you could create a query
SELECT Building, Count(*) as NumRooms
FROM tblBuildingRooms
GROUP BY Building;
This all depends on your table structures.

Another method is to use a subreport to display the occupiers. This allows
for Count(*) since the "finest" level of details are in a subreport, not the
main report.

 

[Guest]

Simon,

Sorry to butt in.

Duane's total queries are probably a more elegant method.
However another method of "counting" over groups is to create the following
scenerio:

Detail section
Text1 =1 over group

First group footer
Text2 =[text1] over group

Second group footer
Text3 =[text2] over group

and so on up the grouping heirachy.

I think this was the method I used once to overcome some countiong problem I
had.


HTH

Terry