Calculating totals for multiple columns (1 Viewer)

[Radioman16]

I'm still learning Access 97 and I could really use some help.

I have two columns; unit price and total price and I need to get a running total at the bottom of each column.
I've read a couple of Access books and they suggest using =Sum([Unit Price]) but I have not been able to make this work. Am I missing something somewhere? Do I need a query or can I run this directly from the main table?

Thanks
Cheers

 

[chrismcbride]

When used on a Form or Sub-Form the syntax - Sum([field_name]) - works. The only troubles that are likely is that the field name has a space in it (at which point you must use the []) or you try to use another aggregate function in the same calculation.

 

[Richie]

Where are you putting the text boxes?

 

[David Mack]

Good point Richie! If you are performing a grand total of a column of numbers in a report (we are talking a report), the text control needs to be in the footer of the report not in the detail.

Dave Mack

 

[Radioman16]

Thanks Chris,Richie and David, I do have a space in the field name and I tried the [] with no success. Therein lies my trouble, I'm not sure what other type of aggregate function to use in order to get the same results.

The text boxes are located in the footer.

 

[accesswatch]

What error messages are you getting ?

Couple of possibilities.
SUM function not available. In which case we have the dreaded lib not available

The other possibilty I can think of is that you are trying to use SUM on a previously calculated field. Is the TOTALPRICE field a calculated field by any chance? If it is you will not be able to use the SUM on it.

The basic rule is that you cannot use an Aggregate function (i.e SUM) on a previously calculated field. What you have to do is recalculate the fields within the SUM
e.g
If your total price is arrived at using
=[UnitPrice] * [Quantity]

Then the report footer control must use
!--- USE THIS SYNTAX ---

=SUM([UnitPrice] * [Quantity])

!--- USE THIS SYNTAX ---

If you used something like
=SUM([TotalPrice]) the this would fail because the [TotalPrice]) is a calulated field


Hope this helps
Regards
Trevor from www.accesswatch.co.uk

 

[Ron Bell]

Text boxes in Reports have property 'Running Sum'
'No' means you have no groups etc.
Try in a group footer, 'Over Group'
Try in a report footer 'Over All'


[This message has been edited by Ron Bell (edited 09-12-2000).]

 

[Richie]

Which footer?

 

[Radioman16]

Richie, I'm placing this info in the "Page Footer" so I am able to see the total on every page.

 

[accesswatch]

You cannot do this on a page footer without special handling (One of the Access gotcha's)Try this link

How to Sum a Column of Numbers in a Report by Page: http://support.microsoft.com/support/kb/articles/Q132/0/17.asp

Hope this helps
Regards
trevor from www.accesswatch.co.uk

 

[Radioman16]

Thankyou Trevor for the good link.
I have it working now.

Ron Bell, after viewing Trevor's link, I understand what you were trying to tell me. Thank you.

 

[Radioman16]

Now that I've got this stupid database working the way I wanted it, does anyone know a good way to replace hair pulled in frustration?

Thank you all for the help.