none
Microsoft Access Sum query RRS feed

  • Question

  • I apologise if I have posted this in the wrong place.

    I have a sub-form  that has  Column headers of; Quantity, Description and Line Price. This form is within a further form that I want to have a "Total Price" which is the sum of all the Line Prices. I have tried a few methods but cannot make this work.

    I have created the database and forms myself from what started out as an idea for a simple purchase order form and has grown to become a monster. Currently the user inputs the Total Price manually, but I would like to do away with that.

    Again I apologise if I have posted this in the wrong place, I am self taught and have learned as I have gone along (which is probably frustrating to experts like yourselves)

    Feel free to tell me to clear off, but any help or advice will be greatly appreciated.

    Tuesday, June 27, 2017 5:16 PM

All replies

  • Hi,

    Sounds like you're storing the Sum value if the user "enters" the Total Price manually. Normally, storing "calculated" values is not recommended. For example, you might be able to place an unbound Textbox on your form to display the Sum of the subform records without needing to enter anything or store it in a table.

    You could try the following:

    1. Add an unbound Textbox in the Footer section of the subform and use the following as the Control Source

    =Sum([Line Price])

    2. Add an unbound Textbox on the main form, below the subform, and use the following as the Control Source

    =[SubformControlName].Form![UnboundTextboxNameInFooter]

    Hope it helps...

    Tuesday, June 27, 2017 5:23 PM
  • I think for your 'Total Price' display you should use DSum with criteria used in the sumform query.

    Build a little, test a little

    Tuesday, June 27, 2017 8:44 PM