Is Looping Necessary to Update an Entire Field?

[tyndale2045]

Using VBA, how do I fill up an entire field in one particular table (the "Usage" field in the Products table) with the contents of a field in a different table (the "Amount Used" in the Inventory Transactions table) where a certain condition is met (that is, where the "Date" field displays a certain date) ?

The first table (Products) has these fields: ProductID and Usage.
The second table (Inventory Transactions) has these fields (among many others): ProductID, Usage, and Date.

To say it another way, when the user enters a date, I would like this to happen: For each record in the Products table, I would like the Usage field to contain the amount of product used (on a given day). The info in this field (Usage, in the Products table) is displayed on a form for the user to see.

I'm wondering, do I have to loop through the Products table, and do an SQL update the "Usage" field for each record, or is there an SQL command that will update that entire field? Is this something that the INSERT INTO command can do?

Thanks in advance for any help.

[Demosthine]

Good Morning.

If I understand you correctly, you want to total the values in the usage Field on the Inventory Transactions Table.

The best solution to this is to use a Query rather than a separate Table. Create a Query with the following SQL Statement:
SELECT [ProductID], Sum([Usage]) AS SumUsage
FROM [Inventory Transactions]
GROUP BY [ProductID];


If you require an actual Table to store this data, rather than a Query, you can create the Query above and then a Query to INSERT INTO using:
INSERT INTO Products
SELECT *
FROM InventoryCount;

OR
SELECT * INTO [Products]
FROM [InventoryCount]

The difference in the two methods is that SELECT ... INTO will DROP the Table prior to inserting the Records, whereas INSERT INTO will APPEND the Table.

Hope this helps.
Scott

[tyndale2045]

Scott,

Thank you for your kind reply.

I think the INSERT INTO option will work best for my needs, but I need a little help tweaking it.

This is what I've got, and it's not quite working yet, but I think I'm close.

I've just assumed that I can splice a functioning SELECT statement into an INSERT INTO statement. I'm wondering if that's so. Tell me if the following code is close to being usable (or, more likely, if it is just outright idiotic). As it stands right now, I get an error message: "The INSERT INTO statement contains the following unknown field name: 'UnitsUsed'. Make sure you have typed the name correctly and try the operation again."

But when I simply use the SELECT statement by itself, it works just fine. So I don't know why it is saying it is an unknown field name.



Public Sub InsertProductUsage()
Dim strSQL As String
strSQL = "INSERT INTO Products SELECT UnitsUsed " & _
"FROM [Inventory Transactions], Products " & _
"WHERE [Inventory Transactions].ProductID = Products.ProductID " & _
"AND TransactionDate = #11/5/2008#"

With DoCmd
.SetWarnings False
.RunSQL strSQL
.SetWarnings True
End With
End Sub

[tyndale2045]

Okay, I gave up on trying to use either an INSERT INTO or an UPDATE statement. I could not get either one to work. I had to resort to a loop, a stupid loop. But, I think if I post the code to my stupid loop, then what I was trying to accomplish might be a little more clear.

If there are any kind souls who wish to instruct me in the ways of SQL a little more perfectly, I'd be glad to hear if it is possible to update this entire field through just a few lines of code.



Sub InsertProductUsage(datTransDate As Long)
'This procedure fills the QueryColumn of the Products table with all the UnitsUsed on
'a particular day.
Dim rs As DAO.Recordset
Dim lngProductID As Long
Dim lngUnitsUsed As Long
Dim strProductName As String

Set rs = CurrentDb.OpenRecordset("Products")
With rs
Do Until .EOF
lngProductID = rs.Fields("ProductID").Value

lngUnitsUsed = Nz(DLookup("[UnitsUsed]", "Inventory Transactions", "[ProductID] =" & _
lngProductID & "And [TransactionDate] = " & datTransDate), 0)
'If the current record's product has a matching record in the
'inventory transactions table, and this matching record has a
'certain date, then update the current record in the QueryColumn field.
If lngUnitsUsed > 0 Then
strSQL = "UPDATE Products SET Products.QueryColumn = '" & lngUnitsUsed & _
"' WHERE [ProductID] = " & lngProductID
With DoCmd
.SetWarnings False
.RunSQL strSQL
.SetWarnings True
End With
End If
.MoveNext
Loop
.Close
End With
End Sub

[Demosthine]

Good Afternoon.

When you are using the INSERT INTO Command, you can not directly use a UNION Clause or it errors out. That's the reason I recommended using the Query for your UNION Query and then you can do an INSERT INTO using that Query instead of an actual table.

If you post your database, we can see what exactly you are trying to do and that will help let us know if my first method will suite your needs.

Take Care.
Scott

[tyndale2045]

Sure.

The forms that are most relevant to our discussion are called "Daily Chick Floor Sheet" and "Daily Egg Transfer Sheet."

Thanks for your help,
Jim