troubleshooting Question

Need help with Access query

Avatar of E C
E CFlag for United States of America asked on
Microsoft DevelopmentMicrosoft Access
6 Comments2 Solutions358 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]));

Open in new window


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.
ASKER CERTIFIED SOLUTION
DOSLover

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
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 2 Answers 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