Results 1 to 5 of 5
  1. #1
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38

    update with multiple joins

    I have to update a table but some of the fields are calculated and require several joins.
    These are the involved tables:
    client(id, name, markup)


    raw_material(id, description, cost)
    operator(id, name, cost)
    machine(id, description, cost)
    forecast(id, client, raw_material, raw_qty, raw_cost, operator1_hours, operator2_cost, operator2_hours, operator2_cost, machine, machine_hrs, machine_cost, markup, profit, total)

    In practice, to calculate a cost I have to multiply a field from the update table times the field of a corresponding table.
    Here is the code, which does not work, but you should be able to get the idea:

    Code:
    update Forecast set raw_cost=raw_material.cost*raw_qty,
    operator1_cost=operator.cost*operator1.hours where operator.id=1,
    operator2_cost=operator.cost*operator2.hours where operator.id=2,
    machine_cost=machine.cost*machine.hours where machine.id=forecast.machine,
    markup=client.markup where client.id=forecast.client,
    profit=sum((raw_cost; operator1_cost; operator2_cost; machine_cost))*markup,
    total=sum((raw_cost; operator1_cost; operator2_cost; machine_cost))*(1+markup)
    where forecast.id=[input from user]
    How can I change the update query so that it will work? thanks a lot.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,818
    UPDATE action SQL with aggregate calc data is not possible.

    Why save this calculated data? If it can be calculated for UPDATE it can be calculated when needed.

    Saving calculated, especially aggregate, data is usually bad design.
    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.

  3. #3
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38
    Quote Originally Posted by June7 View Post
    UPDATE action SQL with aggregate calc data is not possible.

    Why save this calculated data? If it can be calculated for UPDATE it can be calculated when needed.

    Saving calculated, especially aggregate, data is usually bad design.
    I understand and agree, but costs may change and we currently store only the current (last) value.
    Otherwise, I have to create a historical table of all costs and keep it linked with the forecast.
    Still, the user can change the cost at run time (the one in the table is just a default), in which case I would loose the total

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,818
    Then options are:

    Update with DSum() domain aggregate calc

    or use VBA looping recordset to save calc from aggregate query.

    or save the aggregate calc query records to a 'temp' table and use that table as source for UPDATE action
    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.

  5. #5
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38
    Quote Originally Posted by June7 View Post
    Then options are:

    Update with DSum() domain aggregate calc

    or use VBA looping recordset to save calc from aggregate query.

    or save the aggregate calc query records to a 'temp' table and use that table as source for UPDATE action
    thanks, I decided to create a historical table, where I save all calculations, with an insert query

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

Similar Threads

  1. Multiple joins will not work
    By Newtrick in forum Queries
    Replies: 1
    Last Post: 06-19-2019, 12:50 PM
  2. Multiple Joins - SQL Error
    By hack4u in forum Queries
    Replies: 4
    Last Post: 10-19-2016, 11:46 AM
  3. Multiple Inner Joins
    By comfygringo in forum Queries
    Replies: 5
    Last Post: 09-03-2013, 10:49 PM
  4. Replies: 6
    Last Post: 06-21-2013, 08:14 AM
  5. sql problems with multiple inner joins
    By mejia.j88 in forum Queries
    Replies: 1
    Last Post: 01-03-2012, 05:41 PM

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