Update query pulls over only first field in column

[KpAtch3s]

I have two tables InventoryT and NewInventoryT plus a query NewInventoryQ. Last column in the query totals current inventory on hand and new inventory that is entered into a column through a form (NewInventoryF). I have put a button on this form that uses an update query that takes these new totals from the totals column and updates the [Quantity] column on InventoryT.

This works to a point. My update query only takes the value in the first field in the [Total] column of NewInventoryQ and enters the same value into the entire column of the [Quantity] column of InvetoryT.

I'll be adding more functions to this, but below is the code in my button as of now.

Dim cpyString As String


Private Sub UpdateInventoryT_Click()


cpyString = "UPDATE InventoryT SET Quantity = Total"
DoCmd.RunSQL cpyString
End Sub

[DrGUI]

I follow the philosophy that you never store a calculated value in a table. Just calculate it when you need to use it (i.e. in a form, a query, or report).

[June7]

Especially aggregate data. It should be calculated when needed. Review http://allenbrowne.com/AppInventory.html

[KpAtch3s]

I guess I thought I would be avoid any potential problems by using an update query as above instead of having the totals column directly updating the InventoryT. I'll check out the link. Thanks.

[June7]

What is the difference - either way calc is saved? The point we are trying to make is that calculated data, especially aggregate calcs, should not be saved to table (not without strong justification). The risk is that raw data and calc data can get 'out of sync'.

Having said that, I do have a db where I save calculated data - however, none are aggregate calcs.