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

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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!