asked on

Need help with Access query

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.

jerryb30

Can you post a sample db?

Air code:

Private sub CommandClick cmdDecrement
dim rs as dao.recordset
dim rs2 as dao.recordset
dim strSQL as string
dim strSQL2 as string
strsql = "Select [product description] from orders where shipped = 1 and insertdate = #" & [Forms]![Main Form].[txtReportDate] & "#"
set rs = currentdb.openrecordset(StrSQL)
rs.movefirst
do while not rs.eof
strsql2 = "Select * from Products where [Product Description] = "" & rs![product description] & "'"
set rs2 = currentdb.openrecordset(strSQL2)
rs2.movefirst
do while not rs2.eof
rs2.update
rs2![Quantity on Hand] = rs2![Quantity on Hand] -1
rs2.update
rs2.movenext
loop
rs.movenext
loop
end sub

Open in new window

would be executed by a command button named cmdDecrement on a click event.

THIS IS AIR CODE!

A sample DB is necessary to test this out. You probably need a field to ensure Quantity On Hand is not decremented multiple times.

ASKER CERTIFIED SOLUTION

DOSLover

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

E C

ASKER

DOSLover,
That is exactly the logic I was trying to implement earlier today... using the Count as the number to deduct from inventory. I couldn't figure out how to write the sql statement.

I will try it and come back here with an update.

SOLUTION

jerryb30

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

jerryb30

Does both actions at the same time, avoids decrementing Quantity on Hand too many times.

E C

ASKER

Looks like both solutions would work, but I ended up using jerryb30's code.

This is exactly what I was looking for.
Thank you for your help!