sum in form

[deepcec9]

Need to update a field with sum of another field
Scenario: fieldnames
productid netweightpd(product netweight) netweightpal(pallet netweight) palletid

In a warehouse job system is capturing product dimensions including weight. One pallet may contain multiple product, so total weight of pallet will be sum of all the product weight.

productA productB productC in pallet1
"netweightpal" will be sum(netweightpd ABC).
How to calculate this using expression.
Tried Sum(iif([palletid]=tablename.palletid,[netweightpd],0))

TIA

 

[Uncle Gizmo]

Are productA productB productC fields in a table?

 

[deepcec9]

no, the field name is productid.
productA, productB and productB are data in it.

 

[Uncle Gizmo]

I assume you have a main form which contains the palette detail and a subform which contains the product details. If that's the case then I suggest you look at the northwind sample database for inspiration as I believe it demonstrates how to add the columns in the sub form and return the value to the main form.

 

[deepcec9]

No the subform has pallet and product details, main form only have job number.
There can be multiple pallets or products in one single job number.

 

[Uncle Gizmo]

No the subform has pallet and product details

Is this subform in datasheet view?

 

[deepcec9]

Not in datasheet view.
subform is continuous

 

[Uncle Gizmo]

Please provide the structure of ALL your tables. A good way to do this is to put the table name and then underneath list the fields in the table.

 

[deepcec9]

Table name: out
fields: netweightp (netweight of product)
grossweightp
lengthp
widthp
height
netweight (netweight of case/pallet)
grossweight
length
width
height
productid
productname
jobnumber

 

[Uncle Gizmo]

How many products typically go on a pallet?

 

[Uncle Gizmo]

For each product you are entering it appears you are also entering the pallet details. This is unnecessary duplication and should be avoid.

The table you have there should be divided into at least two tables, one with the pallet details and one table with the product details.

 

[deepcec9]

depends on product size.
there can be 10-15 smaller products or only 1 single product.

 

[Uncle Gizmo]

Once you've divided your table up into two tables as suggested, you can then have a main form which will carry the details of the palet and then a subform which will carry the details of the products. With this arrangement, it will be simpler to do the calculation you require.

 

[Uncle Gizmo]

Don't forget to backup your database regularly!

 

[gemma-the-husky]

this may or may not be appropriate

a warehouse job system is capturing product dimensions including weight. One pallet may contain multiple product, so total weight of pallet will be sum of all the product weight.

if you know what was on the pallet, you can recalculate the pallet weight at any time. Storing the total may help in some cases, but may equally cause problems.

It's a issue with "calculated fields". Not the new Access concept of "calculated values" - more the general concept of whether it's a good idea or not.