Results 1 to 7 of 7
  1. #1
    tazzzz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Location
    USA
    Posts
    37

    change value in a table based on criteria from a different table

    Hello all,
    In my database I have 2 tables that contains values I would like to add if a check box is marked.
    Table 1 columns: JobID;JobNumber; Product; Qty.


    Table 2 columns: COrderID, JobNumber, QtyChanged, Accepted(Accepted has the check box).Also, there are multiple records for each job number based on how many change orders will be.
    I would like to change the value of Qty on Table1 with the values of QtyChanged from Table2 only if the checkbox is marked.
    Thank you

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Qty should be a dynamic calculation, not saved into table.
    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
    tazzzz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Location
    USA
    Posts
    37
    I am not very familiar with dynamic calculations? What do I need to do?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Build an aggregate GROUP BY (Totals) query that calculates the Sum of QtyChanged - filtered to only the Accepted records.
    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
    tazzzz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Location
    USA
    Posts
    37
    Each day could have multiple change orders for multiple Jobs.
    Do I need to create a separate table only for the Qty on each job?
    Also do you know an example I could take a look? I googled dynamic calculations and nothing came up.
    Thanks !

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Use query builder. The sql statement would be something like:

    SELECT JobNumber, Sum(QtyChanged) AS SumQty GROUP BY JobNumber WHERE Accepted = True;

    Review http://www.opengatesw.net/ms-access-...cess-Query.htm

    Or build a report using its Sorting & Grouping features with aggregate calcs in group header/footer sections.

    This is all basic Access functionality. Have you completed an introductory tutorial book or on-line course?
    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.

  7. #7
    tazzzz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Location
    USA
    Posts
    37
    I haven't worked in acces for many, many,many years and now I have to remember a lot of things.
    Thanks for the review and explanations.

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

Similar Threads

  1. Replies: 1
    Last Post: 08-11-2014, 09:57 AM
  2. Replies: 4
    Last Post: 02-26-2014, 12:42 PM
  3. Replies: 8
    Last Post: 11-20-2013, 01:03 PM
  4. Query for table, based on criteria from other tables
    By PureLoneWolf in forum Queries
    Replies: 2
    Last Post: 10-04-2012, 11:23 AM
  5. IIF Criteria based on another table
    By BED in forum Queries
    Replies: 6
    Last Post: 11-24-2010, 01:55 PM

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