I have two tables in Access 2013: 'Products' and 'Orders'
The 'Products' table:
Product ID [auto increment / primary key]
Product Description [text, no duplicates]
Quantity on Hand [integer]
The 'Orders' table:
Order ID [auto increment / primary key]
Order Date [date]
Product Description [text]
A data file is sent to me every week. I simply import that entire data file into the Orders table. (As a result, the Orders table is not normalized)
Since the IDs in each of my tables are only meaningful to me, and since there are only 6 products, the two tables are joined by 'Product Description'
For any given product in an order, the quantity is always '1'. In other words, a customer cannot order 2 of Widget-A. They only get one.
When a product within an order ships, two tables need to be updated:
1. The 'Shipped' field (in Orders) needs to be set to 'true'
2. The 'Quantity On Hand' field (in Products) needs to be decremented by 1.
I have the first one figured out. Need help writing code that takes care of number 2.
We usually ship 20-30 orders at a time, so marking order as 'shipped' one at a time is not practical.
So I created a form which prompts the user to enter a date.
When the user clicks OK, an update query finds all of the orders for that day and changes the 'shipped' field to 'true'
SET [Orders].[Shipped] = 1
WHERE ((([Orders].[Insert Date])=[Forms]![Main Form].[txtReportDate]));
So now how do I update the quantity on hand for each of the 6 products, as a result of shipping these 20-30 orders?
I need help writing a 'for each' loop.
Let's say I just marked the 20 orders for 7/12/2013 as 'shipped'
Now I need to go back and cycle through those 20 orders.
For each order, I need to update inventory Quantity on Hand in the Products table.