How to make Parent table field store sum of child table 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

 

[plog]

If you've spent 15 seconds searching you would've found the answer at least twice: Don't store calculated values, calculate them.

You should make a query that calculates the value you need and then use that query whenever you need to use that value. Data gets added, date gets deleted, data gets changed. Storing that calculated value is a huge waste, you should just let the computer work for you and calculate it everytime you need it.

 

[Alexandros]

So I guess I lied about spending hours searching in various forums.
Anyway I did have a query that was summing up the Exports and Imports for each item and also had an Expr1 subtracting exports from imports and each Item from the parent field had its correct amount on the Expr1: column.
Problem is I don't know what to do with that. How can I use it on a subform that way? Do I have to link it with Items table too?

 

[Alexandros]

In my subform I put it to show my query which includes the calculated values I want. The answer I guess was way more simple than I thought. But are you sure that its more of a waste to store a number in a field instead of having computer to calculate 100.000 records every time?



[this post was not edited by g-t-h]

 

[MSAccessRookie]

In my subform I put it to show my query which includes the calculated values I want. The answer I guess was way more simple than I thought. But are you sure that its more of a waste to store a number in a field instead of having computer to calculate 100.000 records every time?

I believe the point to take form the previous comment, is that you will either need to caluculate the values every time, or risk using stale data values. There is no guarantee that none of the data has changed, and if any of it has, the stored values would be out of date.

-- Rookie

 

[gemma-the-husky]

In my subform I put it to show my query which includes the calculated values I want. The answer I guess was way more simple than I thought. But are you sure that its more of a waste to store a number in a field instead of having computer to calculate 100.000 records every time?

100K records is a lot. At that level I expect it might be worth storing the calculated value.


The problem is, as AccessRookie just said - is how you maintain the integrity of the stored value.


ie every time something changes, you may need to recalculate the stored value. you have to manage every possible place that could result in an Insert/Delete/Amend that would affect the stored value. Even then some things can happen that just cannot be managed automatically (eg - you delete a record directly in a table)


You therefore need to consider very carefully whether the extra overhead in managing a calculated value, is worth the time saved. If it is something calculated on historic data that is unlikely to change, then it may well be worthwhile.

 

[Simon_MT]

If you store values ou just need to take care when dealing with an event a there is fail safe method calculation to do the calculation. If the child has a subform then always On Exit I use:

Function Prints_Avail()
    With CodeContextObject
        .[Print Avail] = Prints_AvailabilityCount
        Call IsDirty
    End With
End Function

Private Function Prints_AvailabilityCount()
    On Error GoTo Err_Prints_AvailabilityCount
 
Dim db As DAO.Database
Dim rs As DAO.Recordset

    With CodeContextObject
        Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT DISTINCTROW Count(*) AS [CopyCount] FROM [Prints Details] INNER JOIN Statuses ON [Prints Details].[Print Detail Status] = Statuses.Status HAVING [Prints Details].[Print Code] = " & .[Print Code] & " and Statuses.[Status Flag]='A'")
        rs.MoveFirst
        Prints_AvailabilityCount = rs!CopyCount
    End With

Exit_Prints_AvailabilityCount:
    rs.Close
    Exit Function

Err_Prints_AvailabilityCount:
     Resume Exit_Prints_AvailabilityCount
End Function

Simon