Counts of subgroups and subsubgroups

[Guest]

Scenario: Access 2002 (with an Access 2000 format db), Windows XP. I'm a new Access user with very little database experience (as may soon become obvious ). What I'm asking here may be very easy, but I can't even figure out how to search Access's help system for an answer

I have some items, each of which has subitems, each of which in turn has subsubitems. I'd like a report that gives me simply counts of the number of subitems and subsubitems in each item, and the number of subsubitems in each subitem. (In reality, there's yet another level to this, but let's keep it simple.

The data structure I have to work with is that there are three tables: a table of items, a table of subitems, and a table of subsubitems. Each item, subitem, and subsubitem has a unique code, and each subitem's record has a field with its parent's item's code, and each subsubitem's record has a field with its parent's subitem's code. There's a one-to-many relation (on item code) from the item table to the subitem table, and one-to-many relation (on subitem code) from the subitem table to the subsubitem table

I already have a query that uses all three tables to simply list items, subitems and subsubitems, and someone else has done a report based on this query that lists them all nicely in outline format. What I'd like to add to this report is the counts, as described in the first paragraph of this message. I can get the counts of all the subsubitems (lowest level objects) at each level, but I can't figure out how to get the number of subitems (the intermediate level) per item (the top level). Any help would be appreciated

 

[Marshall Barton]

Scenario: Access 2002 (with an Access 2000 format db), Windows XP. I'm a new Access user with very little database experience (as may soon become obvious ). What I'm asking here may be very easy, but I can't even figure out how to search Access's help system for an answer.

I have some items, each of which has subitems, each of which in turn has subsubitems. I'd like a report that gives me simply counts of the number of subitems and subsubitems in each item, and the number of subsubitems in each subitem. (In reality, there's yet another level to this, but let's keep it simple.)

The data structure I have to work with is that there are three tables: a table of items, a table of subitems, and a table of subsubitems. Each item, subitem, and subsubitem has a unique code, and each subitem's record has a field with its parent's item's code, and each subsubitem's record has a field with its parent's subitem's code. There's a one-to-many relation (on item code) from the item table to the subitem table, and one-to-many relation (on subitem code) from the subitem table to the subsubitem table.

I already have a query that uses all three tables to simply list items, subitems and subsubitems, and someone else has done a report based on this query that lists them all nicely in outline format. What I'd like to add to this report is the counts, as described in the first paragraph of this message. I can get the counts of all the subsubitems (lowest level objects) at each level, but I can't figure out how to get the number of subitems (the intermediate level) per item (the top level). Any help would be appreciated.

Are you using Sorting and Grouping with a group header
section fot the items and another for the subitems? If so,
you can get the count of subsubitems in the subitem and in
the group header/footer by using a text box with the
expression:
=Count(*)

You could get number of subitems in the items in the items
group footer by using a text box in the subitems group
header/footer with the expression =1 and setting its
RunningSum property to Over Group. Then the items group
footer can use a text box that refers to that text box to
display the count.

Getting the number of subitems in the items group header is
a different matter altogether.