troubleshooting Question

Need help with Access query

Avatar of E C
E CFlag for United States of America asked on
Microsoft AccessMicrosoft Development
6 Comments1 Solution358 ViewsLast Modified:
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]
Shipped    [true/false]

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'

UPDATE [Orders]
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.

Example:
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros