435,136 Members | 1,054 Online
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

# how to calculate sum for items that have the same id or the same serial number

 P: 38 Hello i have a table with some products. in the table i have two fields called itemIn ( buy) and Item out( sold). now i want to calculate how many items with the same serial number or with the same id i have in stock. example ID date serial number ItemIn ItemOut Stock 1 20.05.2017 test1 55 20 35 i know how to calculate that but lets say that another day i get new items with the same serial number how to create field that will take the value from previus field and sum the new items so that i will have correct sum in the stock? example ID date serial number ItemIn ItemOut Stock 1 21.05.2017 test1 100 0 ? correct way shuld be... the value from the stock and the new items that arrieved today so the results shuld be 35+100 = 135, my new value of the items should be 135. also i want if i sold some items that my value in the stock should change exapmle ID date serial number ItemIn ItemOut Stock 1 21.05.2017 test1 100 50 ? the results should be 35 from stock +100 new items = 135 and 50 sold today ..the new value in the stock should be now 85 thanks in the advance ID date serial number ItemIn ItemOut Stock 1 21.05.2017 test1 100 50 85 May 20 '17 #1

Elena

Try
Expand|Select|Wrap|Line Numbers
1. Nz(DSum("ItemIn", "MyTable", "Serial Number = " & Chr\$(34) & [Serial Number] & Chr\$(34))) - Nz(DSum("ItemOut", "MyTable", "Serial Number = " & Chr\$(34) & [Serial Number] & Chr\$(34)))
2.
Where "MyTable" is the name of your table

Phil

8 Replies

 Expert 100+ P: 1,430 Can you please clarify what you mean by "with the same serial number or with the same id" In your examples, ID = 1 has Serial Number "Test1". Is this always the case or could ID = 1 have a Serial Number "Test999"? Phil May 20 '17 #2

 P: 38 well with my serial number i mean that the serial number has to be the same, and ID is chaning example ID Serial number 1 test1 2 test1 3 test1 4 test2 5 test3 6 test2 7 test3 8 test2 9 test4 10 test5 May 20 '17 #3

 Expert 100+ P: 1,430 So am I correct in assuming the ID is irrelevant and you want to do your calculation purely based on the Serial Number? Phil May 20 '17 #4

 P: 38 yes that is correct i try with query but i dint get the sum for the same serial number May 21 '17 #5

 Expert 100+ P: 1,430 Elena Try Expand|Select|Wrap|Line Numbers Nz(DSum("ItemIn", "MyTable", "Serial Number = " & Chr\$(34) & [Serial Number] & Chr\$(34))) - Nz(DSum("ItemOut", "MyTable", "Serial Number = " & Chr\$(34) & [Serial Number] & Chr\$(34)))   Where "MyTable" is the name of your table Phil May 21 '17 #6

 P: 38 hello i try but says me that i miss some operator i think is missing some " or ), [, ] but i dont know where May 21 '17 #7

 Expert 100+ P: 1,430 Can you please post back your code (Copy it directly) Thanks Phil May 21 '17 #8

 P: 38 hello i resolve everythink. thank you very much May 22 '17 #9