Get detail row numbers from main group

B

bhammerstrom

New member

Local time

Today, 02:09

Joined

Mar 10, 2009

Messages

4

• Mar 10, 2009

• #1

The subgroup footer is hidden and has txtCounter (=1) in it to count number of records in the subgroup. Now I want row numbers in the Detail that display neither OverGroup (the subgroup) nor OverAll (no group), but instead for the Main Group.

for Example, the report should look like:

Daisy (the main group, Flowers)
Item# Breed Color
1 Hb1-A Yellow
2 Hb2-B Red
3 HB5-A Red

3 Items Total, 2 Different Colors (in the footer for Flowers)

Poppy (the next main group)
Item# Breed Color
1 Hr1-C Yellow
2 Hr2-D Yellow
3 Hr5-A Red
4 Hr6-B Yellow

4 Items Total, 2 Different Colors

I have no trouble with the calculation of total items and different colors. I only need the Item numbers, but with the subgroup of Color, the numbering shown above is not possible. Is there another way?

 

wiklendt

i recommend chocolate

Local time

Today, 19:09

Joined

Mar 10, 2008

Messages

1,746

• Mar 10, 2009

• #2

i don't have any hidden textboxes in mine. i simply put "=Count(*)" (without the quotation marks) as the control source of an unbound textbox in the main group header and it will count how many total records in each group.

i guess it is also possible to use a second text box and use another Count (or other) and incorporate a filter where it counts the number of colours...?

 

B

bhammerstrom

New member

Local time

Today, 02:09

Joined

Mar 10, 2009

Messages

4

• Mar 10, 2009

• #3

I actually have the counts already. I'm not interested in counting anything. I simply want row numbers for the records returned in the Detail grouped not on the lowest heirarchy group, but on the next-higher level of group. Has anyone done this? It seems like a common enough task, but Access seems unable. . .

 

wiklendt

i recommend chocolate

Local time

Today, 19:09

Joined

Mar 10, 2008

Messages

1,746

• Mar 10, 2009

• #4

ok, taking a shot in the dark b/c your question/explanation is unclear: do you mean you want sequential numbering like this:

1 Daisy (then detail info below)
2 Poppy (then detail info below)

??

have you tried putting an unbound textbox into the group header (of the level you want numbered) then making the control data "=1", then in data properties for that textbox set the 'running sum' to be 'over group'?

 

B

bhammerstrom

New member

Local time

Today, 02:09

Joined

Mar 10, 2009

Messages

4

• Mar 10, 2009

• #5

Take a fresh look at my example report up in the first post on this thread. The Flowers main group is not numbered, but the detail rows are numbered. I want to be able to number the detail rows just as they appear in my example, per Flower, not per Color. This, sadly, cannot be in the usual way (as you suggest) by placing a textbox in the Detail with the ControlSource set to =1 and the RunningSum set to OverGroup or OverAll. The reason this cannot be done is due to the fact that the lowest heirarchy of grouping is Color, not Flower. And if I remove the Color grouping, then I don't know how to count the number of Colors per Flower in the Flower group footer. . .

 

wiklendt

i recommend chocolate

Local time

Today, 19:09

Joined

Mar 10, 2008

Messages

1,746

• Mar 10, 2009

• #6

can you post this database? i'm sure there MUST be a way to do it...

 

B

bhammerstrom

New member

Local time

Today, 02:09

Joined

Mar 10, 2009

Messages

4

• Mar 10, 2009

• #7

Solved: Get detail row numbers from main group

See attached mdb

Due to this limitation of Access, I had to eliminate the bottom level group and create a work-around to count the detail rows at the bottom level. Credit to Allen Browne (one of your fellow Ausies) for this idea. The key is using a combination of two properties: HideDuplicates and IsVisible together with RunningSum to tally the unique Colors. TaDa!

 

Attachments

• bhammer.zip

  22.5 KB · Views: 197

wiklendt

i recommend chocolate

Local time

Today, 19:09

Joined

Mar 10, 2008

Messages

1,746

• Mar 10, 2009

• #8

thanks for posting the solution - good work on figuring it out

 

wiklendt

i recommend chocolate

Local time

Today, 19:09

Joined

Mar 10, 2008

Messages

1,746

• Mar 24, 2009

• #9

Re: Solved: Get detail row numbers from main group

bhammerstrom said:

The key is using a combination of two properties: HideDuplicates and IsVisible together with RunningSum to tally the unique Colors. TaDa!

Click to expand...

Hey bhammerstrom... where do you set the "HideDuplicates" property (on the 'txtColorDupe')? i can't seem to find it anywhere in your sample DB... thanks.

hm... allen browne... i'll have to have a look there. he's got some fantastic resources. had no idea he was aussie!

 

wiklendt

i recommend chocolate

Local time

Today, 19:09

Joined

Mar 10, 2008

Messages

1,746

• Mar 24, 2009

• #10

don't worry - i found it, thanks (i hit the "all" tab in the property sheet of the control, and scrolled down and found it - funny i missed it the other times...). now to nut out my own counting problem!