Summing a Calculated Field

[Elhan]

Hi,

I want to sum a calculated field on one of my forms. The field is calculated by multiplying the cost of a good by its quantity. So far I have been unable to sum it. Does anyone know how I can do this?

Here is a link to what the field is related to just so you people know what I am talking about: Project ...basically I have fixed all the problems mention in that thread. All that remains is this field. I will attach my project in case that helps. (the calculated field is in the Treatment Form) Thanks in advance

 

[Rich]

You have to repeat the calculation to be able to Sum it
ie
Sum([Item]*[Quantity])

 

[Elhan]

Ok umm it works, but the problem is for one item only :S It doesn't add all the items up to give a total cost. Please... I really need to get this thing working

 

[Elhan]

Hmm the subform's footer or the form's footer? I tried both and it still generates an error message. Could someone have a look at the form itself to see?

 

[neileg]

I'm afraid you need to go back to the drawing board!

Your forms are based on tables and should be based on queries. Your subform is designed as a columnar report but you are showing it in data sheet view.

If you based your frmTreatment_Subform on a query, you would calculate the total cost of each treatment in the query, not on the form. Then you add this calculated field to your subform. This way the subform total would work.

You have called the text box on your main form 'Sum'. Since this is a reserved word in Access, this may also be causing problems.

I'm actually quite impressed that you've got so far with as many design problems. It's nearly as bad as the databases I used to produce!

 

[Elhan]

Eeek no lol I had changed the design from a form based on queries to a form based on tables due to the fact that I couldn't attach working combo boxes to the subform or the main form. Now you're saying I have to go back to a query based form so I can make a calculate sum field. '

What controls exactly pat?

 

[Elhan]

Hmm so what should I do now? If I redesign the form and base it on queries how will I get the combo boxes to make changes on the subform and the form itself? I want the subform to have a cost combo box which allows costs to be selected and the main form to have a combo box allowing patients to be selected. What do I do then?

 

[Elhan]

Anyone?

 

[neileg]

Eeek no lol I had changed the design from a form based on queries to a form based on tables due to the fact that I couldn't attach working combo boxes to the subform or the main form. Now you're saying I have to go back to a query based form so I can make a calculate sum field. '

No, we're saying that you are suffering from some design problems. If you insist, you can continue with the table based forms and still get what you want, but using queries is a much better approach.

You need to change the subform design. Create a new form that has your column headings in the header. Under these headings, in the detail section, place text boxes that are bound to your data and a text box to do your calculation, in the footer put your Sum() statement in another text box. Make sure you show this subform in form view, not datasheet view.

If I get a chance, I'll have another look at your combos to set what's wrong, but it's a busy day for me. My boss expects me to do some work!

 

[Elhan]

OK the problem is the data view form allows you to add many records easily and view them...does the form view method allow you to add multiple records as well?

 

[Lithara]

Hello Elhan, I've had to deal with a similar issue. To keep it in form view, go into the subform properties and change the Default View value to Continuous Forms, to display many lines, or Single Forms to display only one at at time. Form view can hold multiple records, just like datasheet view does (use the navigation bar to toggle through and add new records). But datasheet won't show the calculations you are trying to make in the footers. Hope this helps!

~Lith

 

[neileg]

Lith is spot on.

 

[Elhan]

Okay now the sum function works on the subform. Now., say I want to link this field to one on the main form (or a report). How do I do this? Altering the control source alone doesn't seem to work.