Update Queries

[LorraineM]

Hi, I am currently creating a database in Access 2013 for a bookshop. I am trying to do an update query where when a new order is made from the store and an item of stock is bought the stock amount is updated to reflect the change in stock. The two tables that I am trying to do this from are tblNewSale and tblInventory, tblNewSale shows when a new order is placed and tblInventory shows all of the stock we currently have in the store. I am really unsure of how to do this and any help would be really appreciated!

[ItsMe]

Do you only have two tables in the database? A bound form can update records in tblNewSale. For instance, a control on the form could be bound to the appropriate field to indicate the quantity ordered/sold. From there, you can use an action query to update the appropriate record in tblInventory. Before running an update, you will want to validate your data and make sure the quantity desired is available in inventory.

You might use a DLookup() function to validate your data before executing a query to update. You would use variables within your Form's Recordset for your code. The SQL statement might look something like ...

UPDATE tblInventory SET tblInventory.QtyOnHand = 4
WHERE (((tblInventory.PrimaryKey)=14523));

[orange]

There are some tutorials and videos here that deal with Customers, Orders and Items. Watching these may be very helpful when building your database.

A great reference on inventory and quantity on hand

Good luck.