calc records based on one record

[ahmedtharwat19]

i have 3 tbls
tbl1:"Pending_Order"
item_id | qty
101 | 200

tbl2:"Stock_on_Hand"
item_id | qty |patch_no | Expire_date
101 | 50 |10101 | 01/05/2013
101 | 50 |10102 | 01/05/2013
101 | 150 |10106 | 01/05/2013
102 | 100 |10103 | 01/05/2013
102 | 100 |10104 | 01/05/2013

tbl3:"Order_Transaction"

item_id qty patch_no Expire_date

and i have a query but i cannot get the solution's

I need in a tbl3 :"Order_Transaction" based on others tbls like :

item_id |qty |patch_no |Expire_date
101 | 50 |10101 | 01/05/2013
101 | 50 |10102 | 01/05/2013
101 | 100 |10106 | 01/05/2013

how can i resolve it by query??

[Luuk]

101 | 100    |10106 | 01/05/2013

Can you explain how this number '100' is calculated from the other tables?

[ahmedtharwat19]

I need 200 unit from item code 101 in Oreder pending table
and i have same unit in stock on hand table as 3 values
i need in order transaction table transfer 200 units only
Thank you in advance

[Luuk]

"I need 200 unit from item code 101 in Oreder pending table"
OK

"and i have same unit in stock on hand table as 3 values"
seems that there's 250 units of item 101 in 'stock on hand'

"i need in order transaction table transfer 200 units only"
must be a language problem that i do not see where this 200 is coming from.....

[NeoPa]

I'll assume this is a problem with the language. You need to understand though, that a question needs to make sense and be complete in order for people to be able to help you.

Please try to include all the necessary logic in the explanation of your question. Currently, it makes little sense.

[ahmedtharwat19]

Mr. Luuk And Mr. NeoPa
I'm really sorry my english language is not good.
I'm very sorry
But i know this site and i trust in this site
So the problem is:

1. I need to sale item_id no: 101 as 200 units
2. I have Stock from this Item "101" 3 [Patch number] in table "stock on hand"
a. first patch from item "101" has a quantity 50 units in patch x and expire date y
b. second patch from item "101" has a quantity 50 units in patch x1 and expire date y1
c. second patch from item "101" has a quantity 150 units in patch x2 and expire date y2
but i need 200 quantity units only
so : I need the query as :
I. go to first record in "stock on hand" table when item_id like same Item_id in table "pending Order" then find the quantity if the quantity in "stock on hand" table >= the quantity in "pending Order" table then type Quantity and [item_id] and [Patch_no] and [Expire date] in table "Order_Transaction".
II. if the quantity in "stock on hand" table < the quantity in "pending Order" then go to second record and calculate the variance else go to the next record and etc.

3. to be in finaly in table "Order_Transaction"
# item_id |qty |patch_no |Expire_date
1. 101 | 50 |10101 | 01/05/2013
2. 101 | 50 |10102 | 01/05/2013
3. 101 | 100 |10106 | 01/05/2013
The first record was taken as all
the second record was taken as all
the third record was calulated like(200 unit from pending - 50 unit from first record - 50 unit from second record) to be 100 units only

the table "stock on hand" should to be like:
# item_id |qty |patch_no |Expire_date
1. 101 | 50 |10106 | 01/05/2013




I hope it explained for you

thank you in advance

[Luuk]

Here's something to start with,
you should check, and improve thing!!!
(because i'm not good at acces/vba )

Public Function do_it()
Dim mydb As Database
Dim mytbl As Recordset
Dim mytbl2 As Recordset
Dim lastnum As Integer
DoCmd.SetWarnings False
 
DoCmd.SetWarnings True
Set mydb = DBEngine.Workspaces(0).Databases(0)
Set mytbl = mydb.OpenRecordset("Pending_Order")
Set mytbl2 = mydb.OpenRecordset("Stock_on_hand", dbOpenDynaset)
 
DBEngine.BeginTrans
With mytbl
   Debug.Print "Checking order for: " & !item_id
   mytbl2.Filter = "Item_id=" & !item_id
   Qty_To_Check = mytbl("qty")
   While Qty_To_Check > 0
      With mytbl2
        Debug.Print "Getting stock from patch_no: " & !patch_no
        If !qty <= Qty_To_Check Then
           DoCmd.RunSQL ("INSERT INTO Order_Transaction (Item_id, qty, patch_no, Expire_date) " _
                          & " VALUES ( " & !item_id & "," & !qty & "," & !patch_no & ",'" & !Expire_date & "')")
           Qty_To_Check = Qty_To_Check - qty
        Else
           DoCmd.RunSQL ("INSERT INTO Order_Transaction (Item_id, qty, patch_no, Expire_date) " _
                          & " VALUES ( " & !item_id & "," & Qty_To_Check & "," & !patch_no & ",'" & !Expire_date & "')")
           Qty_To_Check = 0
        End If
      End With
    Wend
End With
DBEngine.CommitTrans
mytbl.Close
mytbl2.Close
Set mytbl = Nothing
Set mytbl2 = Nothing
Set mydb = Nothing
 
End Function
 

[Rabbit]

Assuming that patch is unique, you can do this with 3 queries. An insert, an update, and a delete.

In each one you join the table to itself to get a running sum so you can calculate how much you need to fulfill the order.

[NeoPa]

@Ahmed
We have no wish to criticise or penalise lack of language skills. Unfortunately, sometimes they are penalised automatically simply because we can't help easily unless we understand what it is you're asking for. I suspect your latest post goes some way to help with that, but even if it's still unclear - we won't be criticising you for that. Good luck.

PS. If you can understand Rabbit's post then I can certainly say he's very clever and you can be sure his advice is worth exploring.

@Rabbit
Considering the language limitations here, I expect your suggestion is presented in too cryptic a form to be (easily) understood.

[Rabbit]

I tend to be cryptic at first to see how far they can get and explain further if necessary.

Note that my suggestion is for fulfilling one order at a time, if you need to fulfill multiple orders of the same item at the same time, then you need to do a running sum on that as well and that just becomes very complicated. In that situation, it would be best to go with the more unambiguous VBA code.