Group within group

[klp via AccessMonster.com]

I have a report that is a summary. I have a group footer on field WCNo. I
want to group within that group. Is that possible? Here is an example of my
report.

Work Center Quantity
2010 1
551 2
552 3
555 4
556 5
565 6
602 77
605 8
803 9
808 10

So that is what prints currently. I would like for it to look like this:

2010 1
subtotal of 2010 1
551 1
552 2
555 3
556 4
565 5
subtotal for 500's 15

Etc.

Thanks in advance.
Kim P

 

[Marshall Barton]

I have a report that is a summary. I have a group footer on field WCNo. I
want to group within that group. Is that possible? Here is an example of my
report.

Work Center Quantity
2010 1
551 2
552 3
555 4
556 5
565 6
602 77
605 8
803 9
808 10

So that is what prints currently. I would like for it to look like this:

2010 1
subtotal of 2010 1
551 1
552 2
555 3
556 4
565 5
subtotal for 500's 15

Etc.

Sure, just insert another group on WCNo (above the one you
have), but with its Interval set to 100

 

[klp via AccessMonster.com]

Okay, I did that but it's breaking it up for EACH work center no. So if I had
555, it would total after then if I had 565 it would total after that as well.
It's not keeping the 500's together. I think I did it right. On grouping I
added WCNo above the one I already had and set the interval to 100. Correct?
What exactly does the interval do?

I have a report that is a summary. I have a group footer on field WCNo. I
want to group within that group. Is that possible? Here is an example of my

[quoted text clipped - 24 lines]

Etc.

Sure, just insert another group on WCNo (above the one you
have), but with its Interval set to 100

 

[klp via AccessMonster.com]

Okay, I did that but it's breaking it up for EACH work center no. So if I had
555, it would total after then if I had 565 it would total after that as well.
It's not keeping the 500's together. I think I did it right. On grouping I
added WCNo above the one I already had and set the interval to 100. Correct?
What exactly does the interval do?

I have a report that is a summary. I have a group footer on field WCNo. I
want to group within that group. Is that possible? Here is an example of my

[quoted text clipped - 24 lines]

Etc.

Sure, just insert another group on WCNo (above the one you
have), but with its Interval set to 100

 

[John Spencer]

Well, you should group below the existing group if you want to sub-group the
existing group.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Okay, I did that but it's breaking it up for EACH work center no. So if I had
555, it would total after then if I had 565 it would total after that as well.
It's not keeping the 500's together. I think I did it right. On grouping I
added WCNo above the one I already had and set the interval to 100. Correct?
What exactly does the interval do?

I have a report that is a summary. I have a group footer on field WCNo. I
want to group within that group. Is that possible? Here is an example of my

[quoted text clipped - 24 lines]

Etc.

Sure, just insert another group on WCNo (above the one you
have), but with its Interval set to 100

 

[klp via AccessMonster.com]

Huh, I did that and it's STILL grouping on each work center. What am I doing
wrong?

Well, you should group below the existing group if you want to sub-group the
existing group.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Okay, I did that but it's breaking it up for EACH work center no. So if I had
555, it would total after then if I had 565 it would total after that as well.

[quoted text clipped - 8 lines]

 

[Marshall Barton]

Sorry, I should have been more explicit. Set the first
group's properties:

Group On: Interval
Group Interval: 100

The two of those specify that the group will be on values
the same as if you grouped on the expression =WCNo \ 100
(i.e. ignore the lowest two digits)
--
Marsh
MVP [MS Access]

Okay, I did that but it's breaking it up for EACH work center no. So if I had
555, it would total after then if I had 565 it would total after that as well.
It's not keeping the 500's together. I think I did it right. On grouping I
added WCNo above the one I already had and set the interval to 100. Correct?
What exactly does the interval do?

I have a report that is a summary. I have a group footer on field WCNo. I
want to group within that group. Is that possible? Here is an example of my

[quoted text clipped - 24 lines]

Etc.

Sure, just insert another group on WCNo (above the one you
have), but with its Interval set to 100

 

[klp via AccessMonster.com]

Okay I see. However, in my grouping, where it says Group On: I don't have
Interval as an option. I have each value or prefix characters. Now what?

Sorry, I should have been more explicit. Set the first
group's properties:

Group On: Interval
Group Interval: 100

The two of those specify that the group will be on values
the same as if you grouped on the expression =WCNo \ 100
(i.e. ignore the lowest two digits)

Okay, I did that but it's breaking it up for EACH work center no. So if I had
555, it would total after then if I had 565 it would total after that as well.

[quoted text clipped - 10 lines]

 

[John Spencer]

My mistake. I misread what you were trying to do. Marshall's advice was
correct (as usual).

Is WCNo a number field or a text field? If it is a text that contains numbers
you might have to use an expression to get the number value and group on that.
=Val(WcNo)

Interval means that the group is created based on the interval - so if the
interval is 100 you should get a group every time the 100's value changes.

One problem is the order of the display may change since the 500's will come
before the 2000's.

Another way to create the groups (without using the interval property of 100)
is to use an expression like the following

=(Val(WcNo)-1) \ 100

If none of these work for you, you may have to build a table that contains
your WCNo and a group designator defining which group the Work Center belongs
to and include that table in the query and the group designator field and
group on the group designator field. This allows you the most flexibility in
that you can assign any work center to any group and have them appear in
sorted order based on the group designator.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Huh, I did that and it's STILL grouping on each work center. What am I doing
wrong?

Well, you should group below the existing group if you want to sub-group the
existing group.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Okay, I did that but it's breaking it up for EACH work center no. So if I had
555, it would total after then if I had 565 it would total after that as well.

[quoted text clipped - 8 lines]

Sure, just insert another group on WCNo (above the one you
have), but with its Interval set to 100

 

[klp via AccessMonster.com]

Oh wow! I will tackle that as sson as I get back in from lunch. I may need
some more help though! Thank you so much for your time. I'll let you know if
this works.

My mistake. I misread what you were trying to do. Marshall's advice was
correct (as usual).

Is WCNo a number field or a text field? If it is a text that contains numbers
you might have to use an expression to get the number value and group on that.
=Val(WcNo)

Interval means that the group is created based on the interval - so if the
interval is 100 you should get a group every time the 100's value changes.

One problem is the order of the display may change since the 500's will come
before the 2000's.

Another way to create the groups (without using the interval property of 100)
is to use an expression like the following

=(Val(WcNo)-1) \ 100

If none of these work for you, you may have to build a table that contains
your WCNo and a group designator defining which group the Work Center belongs
to and include that table in the query and the group designator field and
group on the group designator field. This allows you the most flexibility in
that you can assign any work center to any group and have them appear in
sorted order based on the group designator.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Huh, I did that and it's STILL grouping on each work center. What am I doing
wrong?

[quoted text clipped - 12 lines]

 

[klp via AccessMonster.com]

btw- yes WCNo is a text field. And I do have some Work center's that are in
the 1000's too. Will that effect what I'm doing?

My mistake. I misread what you were trying to do. Marshall's advice was
correct (as usual).

Is WCNo a number field or a text field? If it is a text that contains numbers
you might have to use an expression to get the number value and group on that.
=Val(WcNo)

Interval means that the group is created based on the interval - so if the
interval is 100 you should get a group every time the 100's value changes.

One problem is the order of the display may change since the 500's will come
before the 2000's.

Another way to create the groups (without using the interval property of 100)
is to use an expression like the following

=(Val(WcNo)-1) \ 100

If none of these work for you, you may have to build a table that contains
your WCNo and a group designator defining which group the Work Center belongs
to and include that table in the query and the group designator field and
group on the group designator field. This allows you the most flexibility in
that you can assign any work center to any group and have them appear in
sorted order based on the group designator.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Huh, I did that and it's STILL grouping on each work center. What am I doing
wrong?

[quoted text clipped - 12 lines]

 

[klp via AccessMonster.com]

Sa-weet! Thank you all so very much!! I worked beautifully! You guys rock.

My mistake. I misread what you were trying to do. Marshall's advice was
correct (as usual).

Is WCNo a number field or a text field? If it is a text that contains numbers
you might have to use an expression to get the number value and group on that.
=Val(WcNo)

Interval means that the group is created based on the interval - so if the
interval is 100 you should get a group every time the 100's value changes.

One problem is the order of the display may change since the 500's will come
before the 2000's.

Another way to create the groups (without using the interval property of 100)
is to use an expression like the following

=(Val(WcNo)-1) \ 100

If none of these work for you, you may have to build a table that contains
your WCNo and a group designator defining which group the Work Center belongs
to and include that table in the query and the group designator field and
group on the group designator field. This allows you the most flexibility in
that you can assign any work center to any group and have them appear in
sorted order based on the group designator.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Huh, I did that and it's STILL grouping on each work center. What am I doing
wrong?

[quoted text clipped - 12 lines]

 

[Marshall Barton]

That's because it's a Text field. Kind of confusing to call
it Work Center Number when it's a text field (I think I
might name the field something more like WCID.)

I see that John figured that out and told you to group on
=Val(WCNo) so it groups on the numeric value. I guess I
should have deduced that when your sample output had 2010
before the 500s
--
Marsh
MVP [MS Access]

Okay I see. However, in my grouping, where it says Group On: I don't have
Interval as an option. I have each value or prefix characters. Now what?

Sorry, I should have been more explicit. Set the first
group's properties:

Group On: Interval
Group Interval: 100

The two of those specify that the group will be on values
the same as if you grouped on the expression =WCNo \ 100
(i.e. ignore the lowest two digits)

Okay, I did that but it's breaking it up for EACH work center no. So if I had
555, it would total after then if I had 565 it would total after that as well.

[quoted text clipped - 10 lines]

Sure, just insert another group on WCNo (above the one you
have), but with its Interval set to 100

 

[John Spencer]

Yeah, that was my clue that the OP might be working with a text value
consisting of number characters. Kind of a subtle thing to pick up on.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County