By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,469 Members | 2,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,469 IT Pros & Developers. It's quick & easy.

calc records based on one record

ahmedtharwat19
P: 55
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??
Attached Files
File Type: zip stock on hand.zip (51.4 KB, 56 views)
Dec 25 '13 #1
Share this Question
Share on Google+
9 Replies


Expert 100+
P: 997
Expand|Select|Wrap|Line Numbers
  1. 101 | 100    |10106 | 01/05/2013
Can you explain how this number '100' is calculated from the other tables?
Dec 25 '13 #2

ahmedtharwat19
P: 55
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
Dec 25 '13 #3

Expert 100+
P: 997
"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.....
Dec 25 '13 #4

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 26 '13 #5

ahmedtharwat19
P: 55
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
Dec 26 '13 #6

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

Expand|Select|Wrap|Line Numbers
  1. Public Function do_it()
  2. Dim mydb As Database
  3. Dim mytbl As Recordset
  4. Dim mytbl2 As Recordset
  5. Dim lastnum As Integer
  6. DoCmd.SetWarnings False
  7.  
  8. DoCmd.SetWarnings True
  9. Set mydb = DBEngine.Workspaces(0).Databases(0)
  10. Set mytbl = mydb.OpenRecordset("Pending_Order")
  11. Set mytbl2 = mydb.OpenRecordset("Stock_on_hand", dbOpenDynaset)
  12.  
  13. DBEngine.BeginTrans
  14. With mytbl
  15.    Debug.Print "Checking order for: " & !item_id
  16.    mytbl2.Filter = "Item_id=" & !item_id
  17.    Qty_To_Check = mytbl("qty")
  18.    While Qty_To_Check > 0
  19.       With mytbl2
  20.         Debug.Print "Getting stock from patch_no: " & !patch_no
  21.         If !qty <= Qty_To_Check Then
  22.            DoCmd.RunSQL ("INSERT INTO Order_Transaction (Item_id, qty, patch_no, Expire_date) " _
  23.                           & " VALUES ( " & !item_id & "," & !qty & "," & !patch_no & ",'" & !Expire_date & "')")
  24.            Qty_To_Check = Qty_To_Check - qty
  25.         Else
  26.            DoCmd.RunSQL ("INSERT INTO Order_Transaction (Item_id, qty, patch_no, Expire_date) " _
  27.                           & " VALUES ( " & !item_id & "," & Qty_To_Check & "," & !patch_no & ",'" & !Expire_date & "')")
  28.            Qty_To_Check = 0
  29.         End If
  30.       End With
  31.     Wend
  32. End With
  33. DBEngine.CommitTrans
  34. mytbl.Close
  35. mytbl2.Close
  36. Set mytbl = Nothing
  37. Set mytbl2 = Nothing
  38. Set mydb = Nothing
  39.  
  40. End Function
  41.  
Dec 26 '13 #7

Rabbit
Expert Mod 10K+
P: 12,315
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.
Dec 26 '13 #8

NeoPa
Expert Mod 15k+
P: 31,186
@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.
Dec 26 '13 #9

Rabbit
Expert Mod 10K+
P: 12,315
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.
Dec 26 '13 #10

Post your reply

Sign in to post your reply or Sign up for a free account.