Running sum not coming through into footer 1

[GavinP]

Hi,

I'm tearing my hair out with this problem, yet i know its solvable, because I've done similar things before but regardless of what i try it doesn't work.

I've a report based on a table. The report has two headers,
Depot then rank.
There are two footers, rank, then depot.

In the rank header, I have the following fields,
txt_Star_Target, Variance, and [Text91], with control sources as follows,
[=IIf([variance]>=0,1,0)], [actual]-[guide], [=txt_Star_Target]

[text91] is a running sum over group, and calculates correctly. However, in the depot footer, I have a field which is set to [=text91]. This only returns the first value from the sum, be it 1 or 0. I've tried sum([text91]) and I get an error when viewing the report. I've tried moving the fields into the detail of the report with no luck.

What am I doing wrong?

 

[dhookom]

Please correct the control sources since they should all begin with "=".

I try to [red]not[/red] use control names in expressions in other controls if possible. This would change text91 (why not change this to something meaningfull?) to:

=Abs([actual]-[guide]>=0)

And change its name to txtVarianceCount.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GavinP]

Thanks for the reply Duane but it doesn't answer my question. The field text91 will be called something meaningful, once i get it to work. I tend not to name anything unless it works, as it gets tedious naming fields, then deleting them!

Regardless of whatever i call the field, or if I do it your way with the abs(([Guide Price]-[Last Purchased Price])/[Guide Price]>=0) the running sum works but isn't pulled into the footer.

 

[lameid]

Just a thought...

If you check sorting and grouping for the report are you sure you only have each group listed once? I'm thinking that you might have one of your groups listed twice and Access is associating with the wrong group.

Also I think Duane's point was to use the actual fields instead of controls.

An alternate method would be to just sum the same vaules in the footer...

=Sum(<text91's Control source>)

That may be a whole lot faster than figuring it out.

 

[GavinP]

OK, I don't have the grouping wrong, i've just checked. Worth a try though.

I'll try again, as people don't seem to understand what I am getting at. I have a field, which has an if statement in it which simply sets to either 1 or 0. (call it what you will). This field is in a header. There are no fields in the detail. In the footer, I need to know how many fields have been set to 1. =sum([Whatever the field is called]) does NOT work as you can't sum a calculated field. Simply summing the expression will NOT work since I need to have a value PER RECORD in the header.

I have another report, which requires the same methodology, but the fields are in the detail, and the running sum, and referencing that field work perfectly. Why doesn't it work in this example?

 

[lameid]

Your problem is one of the following...

1) You are actually trying to sum values over the detail and having the control in the header prevents it from hitting all the values and my sum suggestion is valid.

Or

2) You actually want the running sum of the header and not the detail... But probably over the group above the header you have your control in. You could change the running sum so that it does not run over a group. But this would fail to reset it for the parent group. What you need to do is put the grouping in the query that is the report recordsource (or add another query based on that one if it is used elsewhere). Then move your header controls to the detail and ditch the unecessary group in the report (you will still need one).

 

[dhookom]

I just tried something similar with an orders/details report in Northwind that is grouped by Customer then order with headers and footers. I added a text box in the Order header with a

Name: text91
Control Source: =IIf([Freight]>25,1,0)
Running Sum: Over Group

Then add a text box to the customer footer
Control Source: =[text91]

I see the appropriate running sum valuer from the Order Header in the Customer Footer. Isn't this similar to your situation?

When previewed, is your text91 left or right aligned?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GavinP]

OK, LAMEID. - The running sum works. If I have it visible, it works perfectly if it is in the header. If the fields are moved to the detail, then the rest of the fields from the query are fine, but the running sum does not work correctly. either way, it is impossible to sum the running sum field. Enter Parameter Value (running sum field)

@ Duane.

Yes, it sounds exactly the scenario we're talking about. However, as per my reply above, if I put =[running sum field] then I get 1 as my result, not the correct sum. For the record, my field is not aligned, but i've tried it left, right and middle aligned and the field is set to 4.48cm wide so it isn't a display issue.

This is driving me nuts. I have a screenprint of the report layout, but you can't attach to this forum can you?

 

[dhookom]

My question about the left or right aligned was to determine if the calculated value was being regarded as numeric or text. It doesn't make any difference how you format the control, but it might make a difference if the "values" of the controls are determined to be numeric or text.

If you can't get this, you can always create a totals query that groups by Depot and Rank with the appropriate calculation. Then create another totals query based on the first that groups only by Depot and sums the calculation.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lameid]

I must confess my understanding on how running sum behaves was apparently a little askewed from its actual behavior...

I think I finally see the problem... you have

=text91 in the depot footer

But

text91 in the Rank Header.

You can only expect the values to be the same if they are at the same grouping level. My guess is that you group by Rank and then Depot.

Try moving the =text91 to the Rank footer.

 

[GavinP]

yes. That works. However, I need the value in the depot footer. AAARGGGH. I'll go back to the data source, and perform a calculation there. I can't see a way of getting the value into the right footer otherwise.

Thanks for your help.

 

[lameid]

Am I right about the order of the grouping?

Try putting an invisible control in the Rank footer say text92.

Then in the depot footer try = Text92.

I think that will work.

I wouldn't want to touch that datasource either.

 

[lameid]

***Left out important detail***

Text92 should be the same as text91... as a matter of fact copy text91 and set it's visible property to no/false.

 

[GavinP]

Unfortunately, i've tried that, and it doesn't work. Well, the running sum bit works fine, but you can't get the value from that footer into the other one.

Damned annoying.

 

[lameid]

My gut tells me there is probably a way to do this with sum()... Maybe you need to add a calculated field to the query that is 0 or 1? Still other issues involved there and it's tough to visualize without the query. Maybe I'm wrong and if you have a solution that is more intuitive to you, I say go for it, unless it involves Dlookup