calculate total of line item sales in a subform

[dvbman]

just signed in here for the first time - trying to figure out how to calculate a total in a form/subform to display the total sales of line items in the subform. I have tried a few things but nothing I tried is working. Is it possible to do this? ..if so, how do I approach it?

[June7]

If the subform is in Datasheet view, while open in form (not design) view, click the subform then click the Totals button on the ribbon. This will open a totals row at bottom of subform. Select Sum from the dropdown list below the column you want to sum.

If the subform is in Continuous or Single view, can have a textbox in form header/footer with expression: =Sum([fieldname]). You can do this in Datasheet view but the header/footer sections will not show, however, a textbox on main form can reference the subform textbox.

[dvbman]

I had tried your Single view suggestion previously and was getting a "#Error" in the box... not sure what else to try. The field I want a total on is a calculated field in the subform, a result of multiplying the quantity sold (an entered field in the subform) by the product price (a field pulled in from the product table). The resulting line item "total" displays, but then I can't get a total of that column. Does something else need to be set up that I am missing? (Thanks for responding by the way!)

[June7]

Aggregate function must reference field(s) in the form RecordSource, cannot reference controls.

Could include the Product table in the form RecordSource (join type "Include all records from Sales and only those from Product that match")

Do calc in the query and reference that constructed field.

[orange]

Just a note for consideration

Be cautious of your table design. If you record selling price in the Product table, you will lose all history if you change the selling price.

All historic order/sales will be updated to use the new sellingprice. You should consider adding 'AgreedToPrice" and Quantity in the actual OrderDetail record.
That is the Price that that product sold for on this transaction - it could be clearance, loyalty price, negotiated... and will not affect historic Orders.

Good luck with your project.

[TommyK]

I'm missing step somewhere. Do I open the Form (with form header and subform), then click on the subform? Or do I open the Subform, by itself, in form mode? Either way I go I cannot find the "the Totals button on the ribbon."

Is "the Totals button on the ribbon" under the Home, Create, External Data, or Database Tools tab?

[June7]

Is this a normal form/subform arrangement - not a navigation form?

The Totals button is on Home tab in the Records group. It looks like Sigma symbol and has label 'Totals'.

[TommyK]

"Is this a normal form/subform arrangement - not a navigation form?" What does that mean? I'm thinking normal, but I don't know what 'navigation form' means.

[TommyK]

Okay, so I now have a total under the desired subform column, which displays whether it is opened as just the subform with all records totaled, and while part of the controlling form with only the records which apply to the main form. Now how do I get that total to show up on the main form outside the subform box?

[June7]

Then must use the other option I describe in post 2.

[TommyK]

So, how do I reference the field in the subform's footer? It is a text box with: "=Sum([quantity]*
[List Price / Unit])" in it. The two fields are from a query which pulls in the following fields from a table: [quantity] and
[List Price / Unit]. The query also multiplies the quantity and list price fields thus: "ExtPrice: IIf([status]="c",0,[Order Detail]![quantity]*[Order Detail]!
[List Price / Unit])" (the "c" is a switch that identifieds a 'canceled' item so as to not use it's value to calculate the order's total'. FWIW, the subform's footer text box has a label which reads "Form Total:". This footer's text box shows in the subform while in Design View but not in Form View as you described.

[June7]

If you have the calc in query then why not refer to that calculated field in the Sum?

What is name of subform and textbox in footer? Expression on main form would be like: =[subformname]![textboxname]

The textbox in footer will not show if the subform is set for Datasheet view.

[TommyK]

If you have the calc in query then why not refer to that calculated field in the Sum?

I tried that but got the dreaded: "#Error"

What is name of subform and textbox in footer?

"Order Detail subform". The main form is "Order Detail".

Expression on main form would be like: =[subformname]![textboxname]

I've tried this by typing by hand, then tried by using Property Sheet>Expression Builder. Building the expression: "=Sum([Forms]![Order Detail subform]![Text22])"
[Text22] being the system assigned title of the text box in 'Order Detail subform'.

The textbox in footer will not show if the subform is set for Datasheet view.

This I understand

[June7]

No, cannot use aggregate function with a control name. Remove Sum from the expression of main form control. Don't need the [Forms]! prefix.

I always give subform container a name different from the object it holds, like ctrDetails.

=[ctrDetails].[Form].[Text22]

[TommyK]

No, cannot use aggregate function with a control name.

Can you explan Aggregat function. What function(s) are/is being aggregated.

Remove Sum from the expression of main form control. Don't need the [Forms]! prefix.

I always give subform container a name different from the object it holds, like ctrDetails.

I changed the subform to ctrDetails subform. All Hades broke loose. the main form couldn't find it's subform....

=[ctrDetails].[Form].[Text22]

Wait, it seems to have worked. I changed the subform back to 'Order Detail subform', then somehow when I made the changes then immediately displayed the form in Form View, then went to Display view, then back the number magically appeared!

The box on the Main form, outside the subform box is: "=([Order Detail subform]![Text22])" where Text22 is the name of the text box within the sub-form "Order Detail subform"

The Sum of (the Sum of [quantity] times [unit price]) is now showing up in the Detail section AND the footer section of the 'Order Form'.