Results 1 to 4 of 4
  1. #1
    Nikki Harry is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2018
    Location
    Manila
    Posts
    13

    Using a value from a control in one table to calculate a value in a second table

    Please advise if this is posted to the wrong sub-forum.

    Dealing with two of my linked tables, which apart from this, are working well.

    The first [Products], contains a field 'Cost'.


    The second [Transactions] contains the fields 'Sold' and 'Net'.

    My dilemma is trying to get the second table to multiply the value contained in [Products].Cost by the value in [Transactions].Sold and paste the answer automatically into [Transactions].Net.

    The goal being to enter data into a Transaction table/form and the moment the 'Sold' field (quantity sold) is filled, the calculation to update the 'Net' (how much made today) is automatically made and pasted.

    I've been down all kinds roads looking for the method, but not found it yet. I'm sure one you bright folks can point me in the right direction.

    Many thanks

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if the net control is bound to the net field, then you will need to use vba code to do the calculation and populate the field - something like this in the sold control after update event

    net=sold*dlookup("cost","products","productID=" & me.productID)

    However I have to question what you are trying to do. If this is for an invoice or similar document, the usual protocol is to store the cost field in the transactions table and then calculate the net as and when required. Reason for I suspect the same reason you want to store the net figure - because the cost figure can change over time.
    The goal being to enter data into a Transaction table/form
    not a table

  3. #3
    Nikki Harry is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2018
    Location
    Manila
    Posts
    13
    Thanks for the rapid response Ajax. Hmm, VBA. Going to take a while to get my head around that one. Your assumptions are correct. Trying to cheat by bending protocols to store a value that should be just recalculated. My problem is I'm building this thing and using it at the same time. Which means I'm having to improvise on the hoof. My goal is to correct once time allows and the big reference book by John Veritas arrives!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Store the cost, calculate net. However, a net figure usually involves a discount factor. If you are simply doing quantity times cost, I would call it TotCost.

    Will require code (VBA or macro) to save cost for selected product. DLookup is one way to retrieve value. Another is to use a multi-column combobox for product and code references column with the cost.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculate Values pulled from Another Table
    By Abuaarah in forum Database Design
    Replies: 5
    Last Post: 04-16-2018, 09:13 PM
  2. Replies: 2
    Last Post: 03-22-2015, 08:31 PM
  3. Is it possible to calculate in a table?
    By shafiq037 in forum Access
    Replies: 1
    Last Post: 05-02-2013, 10:08 PM
  4. Replies: 4
    Last Post: 06-30-2012, 02:01 AM
  5. Replies: 6
    Last Post: 03-14-2011, 09:37 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums