Storing value of multiple data then cutting it

[cromz]

Need some help with this problem.
On the following table

Supplies
Storedlocation

Containing the following rows

5 A
2 A
3 B
2 A
2 A
2 A
2 B
2 B

Here is my code to display the sum of supplies based on the stored location

select sum(supplies) from store where storedlocation ='" & textbox1.Text & "'"

The result would be
13 A
7 B

The problem is how to cut that value if someone takes some supplies from certain stored location
lets say someone take 2 from location A

and the the result would be the following table

    3 A
    2 A
    3 B
    2 A
    2 A
    2 A
    2 B
    2 B

11 for A and 7 for B.

how can i achieve this. Thanks in advance

[Reverend Jim]

Why do you have duplicate records? When someone "takes" 2 items from location A, how do you determine if they are to be removed from record 1 as opposed to record 2, 4, 5, etc? Also, when you

select sum(supplies) from store where storedlocation ='" & textbox1.Text & "'"

the result would be a single record, not two records as you indicate.

[cromz]

the table should be

    5 A     B001
    2 A     B002
    3 B     B003
    2 A     B004
    2 A     B005
    2 A     B006
    2 B     B007
    2 B     B008

yes, the result of

    select sum(supplies) from store where storedlocation ='" & textbox1.Text & "'"

is only 13
and from that "13" i would like to cut the value of supplies from the table

if someone takes 6 from location A
the table would be like this

        0 A     B001
        1 A     B002
        3 B     B003
        2 A     B004
        2 A     B005
        2 A     B006
        2 B     B007
        2 B     B008

[Reverend Jim]

I'll assume your columns are named Quantity, Item and Location respectively. In that case the query would be

UPDATE store
   SET Quantity = Quantity - 2
 WHERE Item = 'A'
   AND Location = 'B001'

That assumes that there are enough items at the given location. If your intention is to remove items from other locations as required then you need a loop and a test such as

if there are enough items in total then

    start at the first location

    do until enough items have been removed
        if there are not enough items at this location
            decrement items to remove by that number
            remove all of them
        end if
        select the next location
    loop

else

    "sorry, we don't have enough of that item"

end if

To get the total number of an item available at all locations you do

SELECT SUM(Quantity) FROM store
 WHERE ITEM = 'A'

Give the coding a try and let me know if you need further explanation.