Total Formulas

[louisa]

So i have 5 fields example:
Field1
Field2
Field3
Field4
Field5
+ a Total Field

I would like it to add all 5 fields together and enter the amount in the total box, on occasions amount could be £29.99 so preferably would not like it to round the figures up.

I also have another form which is set to continuous forms and i have a total field on this form to, obvioulsy how ever much data is added on that form the total field is duplicated everytime. On my page footer i have a total cost field. I would like that to add up all of the totals and enter the figure in the box in my footer. Any ideas???

 

[Trevor G]

So i have 5 fields example:
Field1
Field2
Field3
Field4
Field5
+ a Total Field

I would like it to add all 5 fields together and enter the amount in the total box, on occasions amount could be £29.99 so preferably would not like it to round the figures up.

I also have another form which is set to continuous forms and i have a total field on this form to, obvioulsy how ever much data is added on that form the total field is duplicated everytime. On my page footer i have a total cost field. I would like that to add up all of the totals and enter the figure in the box in my footer. Any ideas???

Your first question is straight forward, Open the form in design view, select (hopefully you have named each field Like TxtTot1, TxtTot2 etc.) The total field, then look at the properties and select to use the Expression builder (3 full stops icon ...), then simply Formula = txtTot1 + txtTot2 + txtTot3 + txtTot4 + txtTot5.

Second question try using an extra textbox in the form Header or Footer and sum the totals field, same steps as above, to select the field and the formula is =SUM(Total Field Name) if you think it is rounding up then add -.01 outside the Brackets.

 

[boblarson]

You would also want to use the NZ function or else a null field will nullify any others.

Nz([Field1],0) + Nz([Field2],0) + ...etc.

 

[louisa]

Thanks for coming back to me but which part do i use to build the expression, before update after update etc?

 

[PNGBill]

You type the expression in the Control Source of Data in your text box Properties.

 

[PNGBill]

This example enters the result from one text box less another.

=[txtNetpay]-[RepayAmt]

to add the suggested Nz

=(Nz([txtNetpay],0)-Nz([RepayAmt],0))

 

[louisa]

Well the first part i have managed to do so thank you for that.
The second part however i am receiving problems, my form called equipment is set to a continuous form design so every time i need to add another line of info it automatically displays all the fields again ready for entry. On the form footer i have a text box called TotalCost. The field i enter data into is called TotalPrice. But everytime i complete that info another box displays for a second set of data, then a third and so on. As they all have the one name TotalCost i am unsure how to get it to total and then display the amount in my TotalCost field. The advise given earlier didnt total the fields or display the data.

 

[PNGBill]

A continuous form will display the same data for each record just like the datasheet view of a table or query.

If you want a total field on your form, you should put it in the Form Footer.

I have a Continuous form on my screen now with 146 records - Many screen pages of records.
Each record has it's own values and has calculated fields for each of them.
On the Form Footer is two calculated text boxes (unbound - get them from your toolbox) that each Sum a different field in the form body.
Field [xyz] in the form body is totaled by one text box with =Sum([xyz]) in the Control Source, just like your question one solution.

 

[louisa]

Thanks for coming back to me again, unfortunately i am only a beginner in access so would you be able to show me an example? or is there anywhere online that shows this?

 

[louisa]

Hi All,

Please find attached an image of the form i am describing, the Total Price is called "TotalPrice" in the table, the Total Cost is called "TotalCost" in the table, it is set as a continuous form so many records can have say 20 pieces of equipment and some can have one. The TotalCost i would like it to calculate all the TotalPrice (depending on how many there are) and display that figure in the TotalCost Box. I have tried many different ways but keep getting "Error" in the field.

Any advise greatly appreciated.

 

[PNGBill]

Try not using TotalCost from your table.

Open up your "toolbox" Drag a New Text Box to your Form Footer.

This will be an "Unbound" Text Box.

In the Properties of this New Text Box, enter =Sum([TotalCost])

If you wish to call this Text Box Total Price then while in Properties, go to Other and replace the name the system gave it with TotalPrice or SumTotalCost.

This value, sum of the records on your form at this moment should not be in your table.
Even the TotalCost possible should not be a field in your table but a calculated field either in your query or just on the form.

 

[PNGBill]

You have a table with some records and the fields include values that either by them selves or joined by one or more other tables can arrive at a Total Price/Cost. This process may or may not require some fancy query work involving calculations but the fact may well be that this same result will always occur.
In this case, the result (total Price/Cost) should be calculated every time you need it - ie remain in a query or on a form's unbound text field.

Now think about your Sum of this price per record.
At any given time, how many records do you have?? This often can and does change. Also, consider your need to Filter the records - ie just show today's records.
Given this, then how can a field on each of these records be expected to hold the "Sum" of the value of all the records you wish to view at this moment?
Bit like one car in a highway knowing at any time how many cars are on the same road.

This value is definitely not to be in the same table as the records it belongs to and in 99.99% of the time, should just be calculated as required.

Should, for some reason you need to store this value, say the temperature at Noon each day, then a new table can hold this value only and be updated each noon.
But say the value is sales for the Day, then this can be calculated each time you need it because the data is all stored and all has dates.
The sale price of an item can change, store this in your invoice/transaction table along with the qty sold and possibly the description (in case this changes) but not the Total sale as this is always able to be calculated.

 

[louisa]

Thank you so much, works brilliantly.