Running Sum (Variation Required)

[chacamasur]

I've looked around and have found: running sum =1 ,(Over group and/or Over All) produces ALMOST what I am hoping to achieve.

I think based on my HEADINGS I am hoping to RESET the running sum (If possible)

HEADING AREA
--HEADING CATEGORY
[Text box] --Detail (RECORDS)

running sum =1 ,(Over All) produces:
A100
Furniture
1,2,3
Lighting
4
E200
Furniture
5,6,7,8, etc, 22,
Lighting
23,24, etc

**************************
**************************
I I would like to do is:
A100
Furniture
1,2,3
Lighting
4
E200 ß RESET based on AREA Change
Furniture
1,2,3,4,5 etc, 18,
Lighting
19,20, etc

I am hoping this can be done on the report... if not thinking query.. but a little unsure what might be ideal.

Thank you to all that read
Cheers,

 

[vbaInet]

GROUP BY Category and set the Running Sum to be Over Group.

 

[chacamasur]

Thank you for the REPLY -- (FYI) Not Completely up on the word terminology.

I have tried the which is what you have mentioned in your reply: (under the DATA TAB)
Control Source: 1
Text Format: Plain Text
Running Sum: Over Group

it Produces

A100
Furniture
1,2,3
Lighting
1
E200
Furniture
1,2,3,4, etc, 18,
Lighting
1,2, etc

 

[vbaInet]

I don't understand. The Grouping must be done on the report not in the query.

 

[chacamasur]

sorry I wasn't finished with my reply to your post.. tab ,... typing .. aahhhh.. posted. I edited the post see above:

 

[vbaInet]

Got ya! Group by the A100 field.

 

[chacamasur]

but I haven't seen anything as to where I need to make that happen.. in my travels. So, where am I require to set that: (not sure)

code??

 

[vbaInet]

What version of Access are you using?

 

[chacamasur]

Access 2007

 

[vbaInet]

Right click inside the report and select SORTING AND GROUPING. Then click the ADD A GROUP button and select that field.

 

[chacamasur]

K, I haven't done anything yet: Within the sorting and grouping I have:

1: Goup on AREA-NUM - with A on top
2: Sort by MTO REPsort - Smallest to Largest (this is a sort for the Category)
3: Group on CATEGORY
4: Sort by criteria No.1
5: Sort by criteria No.2
6: Sort by criteria No.3

Are we on the same page... ??

 

[vbaInet]

Try it out first and see the result.

 

[chacamasur]

Would like to thank for your patiences: (I don't see where exact I need to add a group).. I think one already exists. When you say "select that field" I am assuming it is "Group on Category"

Just not sure what I am trying out first? sorry

 

[vbaInet]

I don't know your field names. If A100's field name is Category then yes that's the field I meant you should Group by.

Ok, let's make things easier for you.

1. Remove the control source of the textbox that is doing the running sum
2. Put this code in the FORMAT or PRINT event of that Category group HEADER section:

txtBox1.value = 1

Where txtbox1 is the name of the running sum textbox

3. Put this code in the FORMAT or PRINT event of the DETAIL section:

txtbox1.value = txtbox1.value + 1

 

[chacamasur]

A100 is the group one level up... A100 is AREA-NUM (Group)
trying to figure what you posted...

 

[vbaInet]

What part are you not understanding?

 

[chacamasur]

After a few head scratches, confused looks and trying to get the computer to mindmeld and do what I was thinking:

Went to the design view --
Right clicked on the desired Header/ build event / code builder

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Text91.Value = 0
End Sub

***********************
RIght Clicked on Detail

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Text91.Value = Text91.Value + 1
End Sub

And this works... when I tried the Text91.Value = """1""" the counting started at 2,3,4,5, (reset) 2,3,4,5,---142

Thank you very much for the help.

 

[vbaInet]

Excellent! Yeah, I forgot, that should have been setting it to 0 instead of 1 at the group header.

By the way, maybe you want to call your textbox something more meaningful. Something txtRunningSum

 

[chacamasur]

I was just double checking the print out... and when it jumps to a new page it goes from

Previous page last item " 35 " --> next page starting item is at 37
thoughts?

I am looking at printcount...

 

[vbaInet]

For each condition, envelope them in this IF block.

If[COLOR=Red][B] FormatCount [/B][/COLOR]= 1 Then
      Text91.Value = 0
End If

If you're using the Print event then you need to use PrintCount.