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

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

elenaA
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

✓ answered by PhilOfWalton

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

Share this Question
Share on Google+
8 Replies


PhilOfWalton
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

elenaA
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

PhilOfWalton
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

elenaA
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

PhilOfWalton
Expert 100+
P: 1,430
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
May 21 '17 #6

elenaA
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

PhilOfWalton
Expert 100+
P: 1,430
Can you please post back your code (Copy it directly)

Thanks

Phil
May 21 '17 #8

elenaA
P: 38
hello

i resolve everythink. thank you very much
May 22 '17 #9

Post your reply

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