Running Sum

[Amfine]

I am currently using the following function in a form to show total of each record.

=Nz([Waste],0)+Nz([FacilityCharge],0)+Nz([TankWash],0)+Nz([H2O/BTU/Solids/Weight Surcharge],0)+Nz([StateFee],0)+Nz([Transportation],0)++Nz([Insurance/SecurityCharge],0)+Nz([Loading Demurrage&Equipment],0)

I am wanting to take that total and show monthly totals in my report, but I'm not having any luck. I have monthly headers and footers but when I change the running sum properties, I keep getting some strange numbers that aren't adding up correctly.
Any ideas will be greatly appreciated.

Tim H

 

[hneal98]

have monthly headers and footers ", Not sure what you mean by that. Do you mean you have groups setup by month?

 

[Amfine]

Sorry for the confusion. Yes, I have groups setup by month. I was trying to show the totals in the group footer by month. Is this possible?

 

[hneal98]

It should be possible if all your fields involved are numeric. Put a Textbox on the form in the footer of the group and put your code something like this:

 =sum([FieldName])

where the FieldName is the name of the field you are putting your calculation in. In some cases that won't work (I don't recall what those instances are,) so you might also try just copying the field to the footer with the same calculations and see how that works.

 

[Amfine]

When I copy the the field to the footer,

=Nz([Waste],0)+Nz([FacilityCharge],0)+Nz([TankWash],0)+Nz([H2O/BTU/Solids/Weight Surcharge],0)+Nz([StateFee],0)+Nz([Transportation],0)++Nz([Insurance/SecurityCharge],0)+Nz([Loading Demurrage&Equipment],0)

For some reason it just won't add up correctly. Any idea what I'm doing wrong

 

[hneal98]

Is the field your are grouping based on a field within the same table as the fields in your formula?

 

[Amfine]

Yes, the field I am grouping is the total of multiple fields within one table.

I have one field in a form that shows the total of the record entered. I am using the following function to make this happen:
=Nz([Waste],0)+Nz([FacilityCharge],0)+Nz([TankWash],0)+Nz([H2OBTUSolidsWeightSurcharge],0)+Nz([StateFee],0)+Nz([Transportation],0)++Nz([InsuranceSecurityCharge],0)+Nz([LoadingDemurrageEquipment],0)

I would like to have a report show the totals spent by month. I copied the field/calculation to the group footer, it does not calculate correct.

Any idea what I'm not doing?

I really appreciate your help.

Tim

 

[hneal98]

Yes, the field I am grouping is the total of multiple fields within one table."

Previously you said "Yes, I have groups setup by month. "

Where are you getting the month from? Is it in a date field?

If your first statement above is correct, that could be where the problem is.

 

[Amfine]

I'm not understanding what your saying.
I setup my report to group by month. (Month field is actually a lookup field, ex. January, February,....etc.) I am asking it to give me a monthly total in the Month group footer using the calculation above. I can't figure out what I am doing wrong.

I have used the =sum([FieldName]) in a group footer in other reports with no problems. I just can't figure out how to get the report to total the field in a group footer with a calculation.

 

[hneal98]

For you to group by month and get a monthly count, you have to some how have the data you are trying to add up linked to the month you are grouping by. Generally that would be through a date field on the table(s) or at the vary least, a link from those tables to the table that contains the date.

Is your database set up like that? Do you have a date in the those tables? or do you have another table that they link to that contains the date they were entered?

 

[hneal98]

Sorry, I did not realize your fields were all in one table. So, the question would be, is there a date field in the table that contains the date this info was entered?

 

[Amfine]

Yes, I do have a date field in the table.

 

[hneal98]

Are you using it to do the monthly grouping? If not, perhaps you should rethink your logic. That would help the numbers formulate correctly.

 

[Amfine]

Yes, I'm using the date field to do my monthly grouping. That is what I can't figure out. I have one field in a form that shows the total of the record entered. I am using the following function to make this happen:

=Nz([Waste],0)+Nz([FacilityCharge],0)+Nz([TankWash],0)+Nz([H2OBTUSolidsWeightSurcharge],0)+Nz([StateFee],0)+Nz([Transportation],0)++Nz([InsuranceSecurityCharge],0)+Nz([LoadingDemurrageEquipment],0)

I would like to have a report show the totals spent by month. I copied the field/calculation to the group footer, it does not calculate correct.

Any ideas,
Tim

 

[hneal98]

I wish I could see your report becuase if you are grouping by this date and the date is in the same table, that should be working. I does not make sense that you would be getting some strange numbers.

The only otherthing I can think about is check to see how your () are setup around the numbers to make sure you have that correctly. The reason I say this is because those calculations inside the () are performed first. So, you might want to look at that.

 

[Amfine]

Thanks so much for your help. You were right, the problem was in the placement of the () in the calculation formula.

Thanks again for your help.

 

[hneal98]

No problem.