updating table with a count or sum (1 Viewer)

[shutzy]

i am trying top create a stock take form. the form is now set up so each time an item is scanned a new record is created in the tblStockUpdate. i ow need to create an update query that updates tblItems with the correct quantities.

the relational data will be the barcode and is unique to each record in tblItems.

because each time an item is scanned a new record is created i need to count records grouped by barcode or sum as each new record has a 1 as default in the qty column.

i have created a qry that sums the records by group(Barcode) but now i need to update the tblItems quantities.

update queries have always troubled me and this one is beating me at present.

i have attached the relevant tables and hopefully get a resolution to this.

thank you

 

[arnelgp]

you cannot directly involved your query "qryStockUpdateTotals" on the update since MS A will complain that it is not updateable, so that solution is to make a temporary table out of it and involved this table in update query:

Public Sub UpdateTableItem()

    Dim db As DAO.Database
    Dim strTempTable As String
    
    strTempTable = "zzTempTable"
    Set db = CurrentDb
    
    With db
        ' put our aggregate query result to temp table
        .Execute "select * Into " & strTempTable & " " & _
            "from qryStockUpdateTotals;"
            
        ' update table item values from our temporary table
        .Execute "UPDATE tblItems INNER JOIN " & strTempTable & " " & _
            "ON tblItems.Barcode = " & strTempTable & ".Barcode " & _
            "SET tblItems.StockQTY = [tblItems].[StockQTY] + [" & strTempTable & "].[SumOfQty];"
        
        ' delete our temporary table
        .Execute "Drop Table " & strTempTable
    
    End With
End Sub

put this in a module and run in through macro or VBA.

 

[Galaxiom]

i am trying top create a stock take form.

Many developers don't store a stock quantity at all but calculate it directly by totalling invoices, purchase and adjustments records each time the quantity needs to be known. This avoids the whole issue of updating quantities and their propensity to get out of step with reality due to glitches.

If you think about it, stock quantity is a derived value and hence storing it could be considered a breach of normalization.

so that solution is to make a temporary table out of it and involved this table in update query:

I am not a fan of temporary tables especially when they are written to the front end. The FE is an application not a place to write temporary data and have it bloat the file requiring compacting. Temporary tables belong in a separate database.

Most jobs often done with temporary tables can be better done by other techniques such as recordsets or transacted bound forms.

BTW Execute should always include the second argument. dbFailOnError. Otherwise any errors are silently ignored.

 

[arnelgp]

thank you mr. moderator for your kind comment.
if the poster mr. zhutzy elect to use another temporary database, so as not to bloat the fe:

Public Sub UpdateTableItem()

    Dim db As DAO.Database
    Dim strTempTable As String
    Dim strOtherDatabase As String
    
    strTempTable = "zzTempTable"
    
    ' replace this portion with the real path and name of your temporary database
    strOtherDatabase = "z:\tmp.accdb"
    
    Set db = CurrentDb
    
    With db
        
        ' delete our temporary table
        ' do not care if temporary table exist or not
        On Error Resume Next
        .Execute "Drop Table [" & strOtherDatabase & "]." & strTempTable
        On Error GoTo 0
        
        ' put our aggregate query result to temp table
        .Execute "select * Into " & strTempTable & " In '" & strOtherDatabase & "' " & _
            "from qryStockUpdateTotals;", dbFailOnError
            
        ' update table item values from our temporary table
        .Execute "UPDATE tblItems INNER JOIN [" & strOtherDatabase & "]." & strTempTable & " " & _
            "ON tblItems.Barcode = " & strTempTable & ".Barcode " & _
            "SET tblItems.StockQTY = [tblItems].[StockQTY] + [" & strTempTable & "].[SumOfQty];", dbFailOnError
        
        ' delete our temporary table
        .Execute "Drop Table [" & strOtherDatabase & "]." & strTempTable
    
    End With
End Sub