Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    21

    Question 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:
    Table: Item_Receipts
    Field: Quantity
    Field: Invoice_Amt
    Table: Item_Listing
    Field: Avg_Each_Cost

    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.
    ---------------------
    Thanks
    David

  2. #2
    Join Date
    Mar 2004
    Posts
    21
    Help from anyone??
    ---------------------
    Thanks
    David

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •