Get Item Numbers from next-to-last group

[bhammer]

In my exterior highrise observations database, my report groups by:

Issue
Elevation
Location
in that order.

In the Coder footer I can display counts of total observations, and distinct
Locations. what's missing is a line item number within each Elevation group.
I can only seem to count the *deepest* group, Location, but I want to count
on the next level up, Elevation. How?

 

[Allen Browne]

So you have an Elevation group header on your report?

In that section, place a text box with these properties:
Control Source =1
Running Sum Over Group

 

[bhammer]

I don't want the counter to count the Elevations, but to count the Locations
listed for each Elevation. The catch is that I have a Location group footer
(not visible) with a counter (=1) inside that allows me to display the
distinct number of Locations for each Elevation (since there can be duplicate
observations of the same Locations on an Elevation. Access seems to be unable
to *go up one level* to do the counter item numbers if a *lower* level group
is present.

 

[Allen Browne]

I'm not sure I follow where the problem lies.

You have a running sum text box give the count of unique locations in the
(hidden) Location group footer section, and it gives the correct count.

Now you are trying to transfer this count to a text box in the next level
up, so the Elevation group Header? Or the Elevation group Footer?

Is this a timing issue, where the total count is not available when Access
completes the Elevation group Header, but it works okay in the Elevation
group Footer? If so, you might be able to work aound it by putting the text
box in the Elevation group Footer, and then refering to that text box in the
Elevation group Header section.

 

[bhammer]

Allen, wrong tack. Let me give an example of what I want the Report to look
like.
Issue 1 (main group)
North Elevation (next group level)
Item# Location (Location is the third group level)
1 grid A2
2 grid B3
3 grid B3
4 grid C1
Subtotal: 4 Observations at 3 different Locations (in Elevation footer)
East Elevation
Item# Location
1 grid A1
2 grid A1
3 grid B3
Subtotal: 3 Observations at 2 differnt Locations
Total: 7 Observations at 5 different Locations (in Issue footer)

What I'm not showing in this example is the Location footer which has the =1
(txtRSumLocations) textbox that is referred to for the counts of unique
Locations. But that's working fine. What I can't get is the Item#. Due to the
Location group, putting a =1 txtbox in the Detail results in a count that
resets when the Location changes. I just want a running Item count for the
Observations per Elevation.

 

[Allen Browne]

Okay, I guess I'm not getting what the problem is.

This looks fairly close to your example:
http://allenbrowne.com/temp/bhammer.zip
10kb zipped A2000 mdb.

 

[bhammer]

Allen,
Your example db illustrates my problem correctly. Compare it to my text
example and you'll see that what I'm after is a seqential numbering of the
detail items per Elevation, rather than per Location, as your example does.

For example, in North there are 4 items at 3 different Locations (calc
fields correctly display these two counts in the Elevation footer). But
notice that the item numbering in your example reflects each new (different)
Location, ie, B3 is listed twice with an item number of 2 for each detail
item--this is not my intent. Rather I want the item numbering to reflect the
items for that Elevation; 1, 2, 3, 4 for the four observations.

To me, the issue seems to lie in the Sorting and Grouping dialog, where of
the three groups: IssueID, Elevation, and Location, the lowest level group is
Location. So the Detail item numbering takes that group. I want the Elevation
group, which is next-up in the heirarchy. Perhaps Access cannot do this?

 

[Allen Browne]

There are several possible approaches here, ranging from using the report
events to accumulate a count through to using a temporary table and
assigning the numbers there. The section events don't fire correctly if you
only print some pages of a report (and don't fire at all in the new Report
and Layout views), and the temp. table is more complex than you need.

A simple workaound is to drop the Location group footer/header out, so you
can get the correct counting on the Elevation (since it is now the lowest
level group.) Now the problem becomes how to get the count of unique
locaations for each elevation.

You can actually do that by manipulating the Hide Duplicates property, and
testing IsVisible. Since you (presumably) do want the repeat locations
printed on each line, I added another text box to the Detail section, with
these properties:
Name txtLocationDupe
Control Source Location
Hide Duplicates Yes
Fore Color 16777215 (i.e. white on white.)
Then another text box, with properties:
Name txtDiffLocCount
Control Source =[txtLocationDupe].[IsVisible]
Running Sum Over Group
This yields -1 for each new location, and 0 for repeat locations, so yields
the negative count of the number of unique locations.

New report in same link:
http://allenbrowne.com/temp/bhammer.zip

 

[bhammer]

Nice work-around, Allen, thanks.

Interesting combination using the HideDuplicates and IsVisible properties
together. Report design is still a big mysterious world to me. . .