stumped on query

  • Thread starter Thread starter Jason Kaufman via AccessMonster.com
  • Start date Start date
J

Jason Kaufman via AccessMonster.com

I am needing to create a query of some sort that will look at a row which has
an item #, an OnHand qty, a Sales Order Qty, and a revised OnHand qty (which
is First OnHand Qty - SalesOrder Qty).
Then IF the next row contains the same item #, I want the revised OnHand Qty
to replace the First OnHandQty so the Onhand Qty will continue to decrease
for each successive line of the same item#. (The SalesOrderQty will likely
change for each record of the table)

I am somewhat new to Access and am mostly self-taught by playing and testing
ideas out, but this request from my boss has me stumped. If it is necessary
to create this in SQL, please be quite clear how to do this as I haven't
ventured into this area to date.

Any help on this idea would be immensely appreciated, as I have been banging
my head on this for the past week.
 
I dont know if anyone is familiar with Mas200 - I am pulling data from SO1 &
SO2 tables and IM1 & IM2 tables and then boiling these down.
I am ultimately needing to determine for a requested date range, of the Sales
Orders in that range, which ones will, after subtracting the Sales Order
quantity for the specified Sales Order, not be in the negative inventory
quantity-wise for each particular item number.
Since it is possible for a particular item number to appear in more than one
Sales Order, I am supposed to subtract the quantity of each sales order from
the OnHand amount (thus arriving at a modified OnHand amount) by date order,
so we can determine at what point will we potentialy run out of any
particular item number. So then we can also determine which Sales Orders in
the requested date range will not be able to be completed and shipped.

I read over the material that was suggested and am looking over my queries
currently but still havent seen the light on this problem.
 
How is the data setup? Do you have 2 fields for quantity on hand? Why are
you checking the next row - do you have to go back through the data to
correct it?

For proper inventory, you should have only one quantity on hand field, which
is updated as items are added or removed via the shipping and receiving
forms:

Shipping form controls used:
txtItemNo
txtQuantityOnHand (this can be a hidden field, or lock it so users can see
but not mess with)
txtNumberSold

Sub txtNumberSold_AfterUpdate

Dim item as Integer (or string if the table has this as a text field)
Dim quant as Integer
Dim sold as Integer
Dim newQ as Integer
Dim sql as String

item = Me.txtItemNo
quant = Me.txtQuantityOnHand
sold = Me.txtNumberSold
newQ = quant - sold

sql = "UPDATE Table SET QuantityOnHand = '" & newQ & " WHERE ItemNo = '"
& item & "'"
docmd.runsql sql

End Sub

If your table is set up correctly this should also work to add for negative
numbers (returns)
 
Back
Top