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

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.

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
``````

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.