How to make Parent field store sum of child fields

[Alexandros]

Hello everyone.

I feel that this might be common but I browsed around the net for hours and didn't solve my problem.

I'll cut to the chase.
I have two tables. Parent and child.
Child table keeps records with quantity on 2 fields. One field is for imported quantity and one field for exported quantity.
Parent table keeps records with items that each one has many records on child table. A one-to-many relashionship then.
I would like to have a field on Parent table that stores the calculated total from child table fields. like =Sum([QuantityImported]-[QuantityExported])
I know that I can't make calculations with fields from other tables or queries so Im looking for a solution on that.
It's Important to have that total calculation on a Parent field to make other calculations.


Any help is welcome.
Thanks in advance

[R_Badger]

You need to use DSUM.

See
http://msdn.microsoft.com/en-us/library/aa172193%28v=office.11%29.aspx

Don't worry it says visual basic you can also use it in a Controlsource on a form/report or as a field in a query.

If you have problems, I can try to help with the syntax, but I would need a bit more information on your table structure, in particular the primary key on your parent table.

Also for the record I suggest performing the calculation in a form and writing it to you table that way or using query.

[Alexandros]

Seems like Im gonna need your help.

[R_Badger]

I have made an assumption that the itemID is a number here, so the formula should look something like

=dsum("[imports]","[log]","[itemID]="&[ItemID])-dsum("[exports]","[log]","[itemID]="&[ItemID])

I have attached an example database below to show how it works

If the ItemID is a string it would be


=dsum("[imports]","[log]","[itemID]='"&[ItemID]&"'")-dsum("[exports]","[log]","[itemID]='"&[ItemID]&"'")

[Alexandros]

Hmm, im a bit lost. I checked your Example.dbm but I can't see where did you put that formula.
Plus, at least for me the quantity field doesnt show the expecting amount. To be more precise it's zero.
Where exactly do i put that formula? I make the field type to calculated and put there the expression?

[Alexandros]

I just made a query that was summing up the imports and exports and I have an expression subtracting exports from imports and I put that in the subform instead of the Items table. Although I'd like to store the values in a field, i was told its huge waste. Access book says that too but as Jeff Conrad says there are ocasions that you might have to brake the rules.

[R_Badger]

Thats what i had in the example db

[RayMilhon]

I would like to point out that storing an element of data that is a calculation of other elements of data is redundant and unnecessary and does not conform to 3rd Normal form. The calculation can be place in a query, form or report anywhere it's needed not stored as a data element. The only exception possibly being in a data warehouse where the amount of data is large enough to impact performance and even then it should be used with care. You need to be sure that if any field effecting the calculation is updated the calculated field is also updated.

[R_Badger]

I did previously state the calculation could be placed in a form or query, and if you look at the sample I uploaded, I did not place it in the table (I left the table structure as described to best illustrate the example), but actually placed the calculation in a query. Although I expect most regular users here normalise their data, I can at the same time, say that there are also likely a number of people who deviate from it knowingly for specific applications, for one reason or another.

[RayMilhon]

I apologize if my statement was unclear. re-reading it I realize I should have been more specific. I was referring to the statement."Also for the record I suggest performing the calculation in a form and writing it to you table that way or using query. Hope that helps!" that implies storing the calculation in a table. Being the writer you knew what you meant. Being the Reader I did not.

[R_Badger]

My bad too much caffiene not enough sleep, having re-read it, I actually wrote gibberish! You were quite right to clarify!