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

[elenaA]

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

[PhilOfWalton]

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

[elenaA]

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

[PhilOfWalton]

So am I correct in assuming the ID is irrelevant and you want to do your calculation purely based on the Serial Number?

Phil

[elenaA]

yes that is correct

i try with query but i dint get the sum for the same serial number

[PhilOfWalton]

Elena

Try

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

[elenaA]

hello

i try but says me that i miss some operator
i think is missing some " or ), [, ] but i dont know where

[PhilOfWalton]

Can you please post back your code (Copy it directly)

Thanks

Phil

[elenaA]

hello

i resolve everythink. thank you very much