Group within group

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?

John Spencer wrote:
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]
Sure, just insert another group on WCNo (above the one you
have), but with its Interval set to 100

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200912/1

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

John Spencer wrote:
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]
Sure, just insert another group on WCNo (above the one you
have), but with its Interval set to 100

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200912/1

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]


klp via AccessMonster.com wrote:
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?

Marshall Barton wrote:
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

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

Marshall Barton wrote:
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