Unanswered: Defining Variable with SQL Statement in VBA
This is my first post with dBforums and it appears there is a ton of information here. Hope you guys can help me.
I'm kind of new to using SQL statements in Access. But I'm very familiar MySQL and Access. I'm trying to SUM() two fields of a table based on the entered value of a field in the open form. Then perform a calculation with the two results to get my vb variable. Then I would use a SQL UPDATE to pass the variable value to another table. I believe I have found the proper syntax to perform the UPDATE, but I'm struggling with getting the two sums. Also, I want this to occur when a button click is performed. This also is not a problem as I believe I know how to create the sub syntax. All tables reside in the same Access database.
If this helps, here are the table names and field names and a brief description of how I want to use them:
When items are entered into Item_Receipts table through form, user clicks on command button. On click, VBA sums Quantity for that item in Item_Receipts table and sums Invoice_Amt for same item in same table. Sum of Invoice_Amt divided by Sum of Quantity is calculated to arrive at Avg_Each Cost. Avg_Each_Cost is passed to Item_Listing table and updates Avg_Each_Cost field for entered item.
Hope this makes sense and I appreciate any help with this.