Summing Different Quantities (1 Viewer)

[phoenix9]

I'm creating an order form which can accept products in different unit bases. For example, a single order may contain products that are sold by EACH unit, products that are sold by the DOZEN, and products that are sold by the SET. So, my products have a field called UnitType that states whether it is sold by EACH, DOZEN or SET. In the order form, there is a field for product no, quantity, unit type, unit price and subtotal. Summing the subtotal to find the grandtotal is simple using "=Sum([subtotal])", however I want to sum the quantities separately- by EACH, DOZEN and SET. Then eventually paste them in the order form and report. Any suggestions? Thanks

 

[o1110010]

In the order form, there is a field for product no, quantity, unit type, unit price and subtotal. Summing the subtotal to find the grandtotal is simple using "=Sum([subtotal])", however I want to sum the quantities separately- by EACH, DOZEN and SET. Then eventually paste them in the order form and report. Any suggestions? Thanks

If you use a query, you can use a Group By option for the UnitType field and also use a Sum option to get the corresponding Sums.

 

[phoenix9]

I have no problems with the query, but I have trouble when trying to write it back to the form. I've tried several different query style and used a criteria to filter the order number for which each sum corresponds to, however, it's not writing. Is there a specific expression I need in the form to call the summed value from the query?

 

[The_Doc_Man]

Don't know if this is productive or counter-productive, but you COULD build a translation table for quantities.

tblUnits
fldUnitName, text, {some short length}
fldUnitQty, long, {actual number of elements involved in this unit}

Then you could join the unit name from the units table to your actual order table and get the per-unit quantity as a separate field in the query. Of course, you would still have a quantity of units in the order (like ordering six of EACH and 0.5 DOZEN of the other?) to go with the unit names. But the key to summations is to have something to add up. If you are dealing with discommensurate units, the FIRST step is to make the units commensurate again.