title: Microsoft Access 2010: Update a field in another table on button click
body: <h1>Basics about the database</h1>
  <p>I am working on a (relatively) simple database that stores inventory data.  I am using Microsoft Access 2010 in order to do this.  I have six tables with the following relationships:
  <a href="http://i42.tinypic.com/a4biup.png" rel="nofollow noreferrer">Relationships of Database</a></p>
  <p>I have created forms which combine the <strong>Transaction</strong> table with <strong>Ordered</strong>, <strong>Received</strong>, <strong>Allocated</strong>, or <strong>Dispensed</strong>.  Each form requests an amount which will then be used to update <em>On Hand</em>, <em>On Order</em>, or <em>Allocated</em> (from the <strong>Material</strong> table) respectively.</p>
  <h1>The Problem</h1>
  <p>For example, my form to update <strong>Transaction</strong> and <strong>Order</strong> should be able to take in the <em>Amount</em> ordered, save all the data from the fields to the <strong>Transaction</strong> and <strong>Order</strong> tables as well as add the amount from <em>Amount</em> to <em>On Order</em> in the <strong>Materials</strong> table.</p>
  <hr />
  <p>I have been working on this database for the past two days.  I have searched several times for possible ways to perform a similar function, but have come up with nothing.  All the tutorials I have found which seem remotely close to what I need to accomplish are for versions of Access which are much older than 2010.  Unfortunately I have had little experience with the actual coding within Access, so I am stuck clicking around within the buttons on its menus.</p>
  <h1>What I have tried</h1>
  <p>Currently, the program is set to run the following Update query:
  <a href="http://i42.tinypic.com/zlcb6d.png" rel="nofollow noreferrer">Screenshot of update query</a></p>
  <p>This query works if I have one Material stored in the database but adds all the <em>Amount</em> values from <strong>Ordered</strong> to <em>On Order</em> every time it is ran, which is unfortunately not what I need it to do.  I only need each <em>Amount</em> value added to <em>On Order</em> once.</p>