Results 1 to 3 of 3
  1. #1
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130

    Calculated Fields from Multiple Tables


    This is the most intimidating query or programming I have ever contemplated. I am not even sure that this is something to be done in Access, or export the data to Excel and perform calculations there. Structure first.

    Fees table with the fields ProcessCode and ProcessFee.
    Inventory table with ProcessCode and BoxingType.
    WOTracking table with Process, SKUQty, SKU, ContractCo, Date_Time, and OrderNo.

    Table Relationships:
    WOTracking to Inventory by SKU
    Inventory to Fees by ProcessCode
    Fees to WOTracking by Process

    Objective is to query WOTracking for orders between date range. Use WOTracking.Process to get Fees.ProcessFee for each item on order. Sum ProcessFees for order. Get Fee.BoxingFee based on Inventory.BoxingType WHERE SKU is = WOTracking.SKU.

    HandlingFee = 1.75 + ([SKUQty] - 1 * .50)

    SUM ProcessFee, BoxingFee, and HandlingFee per Order.

    Am I trying to do too much?

    NOTE: This is what ambition with a lack of knowledge lead to. Recommendations would be greatly appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you can do it in a query, (or a custom function)
    Q1 to get your total,
    Q2 uses Q1 to set Handling fees.

  3. #3
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    I guess what I am not sure of is creating multiple nested queries from different tables, each with its own WHERE clause. Then looping through each item on a multi-item order for the Order Total.

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

Similar Threads

  1. Creating calculated fields from different tables
    By Brownie1987 in forum Access
    Replies: 6
    Last Post: 01-15-2018, 01:37 PM
  2. Replies: 1
    Last Post: 04-12-2017, 07:39 AM
  3. Replies: 1
    Last Post: 03-14-2016, 06:44 PM
  4. Calculated Fields in tables
    By dharsh in forum Misc
    Replies: 12
    Last Post: 07-01-2013, 03:07 PM
  5. Calculated fields across tables and forms
    By drrob1983 in forum Queries
    Replies: 4
    Last Post: 09-15-2011, 09:41 AM

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