Subtotal for a changing field

[Redwood]

Can anyone tell me how to subtotal the "QTY Needed" field here when the "PartNum" field changesin my report. I can't use a group by on the "PartNum" because another field (Sequence #) has to be in sequential order. Here is how the output of the report should be: (note: the data is coming from a query)


Sequence #...........PartNum.........QTY Needed
10001.....................GHIII889....................4
10002.....................GHIII889....................6
.......................................Total Needed 10
10003......................PHIII779...................3
10004......................PHIII779...................5
.......................................Total Needed 8
10005......................TTYII578...................4
10006......................TTYII578...................4
10007......................TTYII578...................4
.......................................Total Needed 12

What I need is how to get the "Total needed:"

Thanks in advance for the help.....Let me know if I need to clarify more.

 

[Robert Dunstan]

Hi there,

I've just designed a report where I've had to mess around with the groupings to get the desired results...a right pain!!!

In your case I think what you need to do is to set the groupings in the following order

1. Sequence # Sort ascending No group header / footer
2 Part Num. Yes to group footer.

In the group footer for part num create a text box control and its record source put =sum([QTY Needed]) or whatever the control name for your 'QTY Needed'

Try this and see if gives you the results that you want

 

[Redwood]

That gives me this result:

Sequence #...........PartNum.........QTY Needed
10001.....................GHIII889....................4
.......................................Total Needed 4
10002.....................GHIII889....................6
.......................................Total Needed 6
10003......................PHIII779...................3
.......................................Total Needed 3
10004......................PHIII779...................5
.......................................Total Needed 5
10005......................TTYII578...................4
.......................................Total Needed 4
10006......................TTYII578...................4
.......................................Total Needed 4
10007......................TTYII578...................4
.......................................Total Needed 4

That causes it to break on every Sequence number. Any other ideas? Thanks for your help.

 

[Redwood]

Basically I need to monitor the "Part Number" field, in the report, for a change in it. Anybody got any ideas? Or can this not be done?

 

[Robert Dunstan]

Hi Redwood

Is there any chance you can e-mail me a sample of your db so I can have a look at the report for you?

I believe the answer does lie in how the data is grouped but it would be better if could have a look if possible