Calculating Quantity On Hand

[Yam84]

Hello:

I am creating a checkIN/Out system. using this system, users will
check in and out items, as well as make purchases or receive credits
for future purchases. I am fine up the the point of calculating the
amount on hand. I have a query that calculates the total sum of a
product at a certain time, however when an item is purchased or
checked out, the query gives an incorrect result. It correctly
calculates the result when an item is initially checked out or
purchased, however if this product is checked out or purchased again,
it does not recognize that the stock of that item has been reduced, so
it will just subtract from the original stock amount. For instance:

checkOutInID Item Quantity Out Product SumOfstock Expr1
73 Hammer 1 Hammer 7 6
74 "1 1/2"" Putty Knife" 1 "1 1/2"" Putty Knife" 2 1
75 Hammer 2 Hammer 7 5

In record 75, Expr1 should read 4 and SumOfstock should read 6,
because in record 73, after the initial hammer was checked out, there
were 6 left. My plan is that when an item is checked back in, the
stock will increase by the number of items added back to stock. I
have been unable to achieve this. Any insight will be appreciated.

 

[NetworkTrade]

you probably haven't gotten a reply so far because the line wrap on this
forum makes it really hard to understand the table when view it..... it is
probably best to make each column just a representative character or two and
so keep the whole thing real narrow...so the line wrap will not be an issue...

that aside; it is not clear if you have a technical issue or a logical
issue. inventory tracking is pretty classic. generally you want to keep all
the 'ins' and all the 'outs' so that the math is done each time one wants to
look at the inventory in stock...particularly if you let users go back in
time and correct/change an entry...

if one keeps instead a running sum as a stored value - that value would be
incorrect if someone changed a prior number and the running sum hasn't been
updated....

this is just general info of course.... not sure on your specifics....

 

[Fred]

To do what you describe fully requires full implementation of what I call
transaction based inventory tracking. Here are three levels of
complexity:

1. Just a table of items with current quantity. And have someone manually
edit the quantity each time that there is a happening that can modify it.


2. A table of items linked (by ItemNumber) to a table of transactions.
And each happenining that can modify intentory for that item (initial load,
borrowing, returning, scrapping, reconciling to a physical enventory,
purshases, sales tc.) gets entered as a record in the transactions table.

Then run a report (or query) that groups transactions for each item and runs
the total to get current inventory. And so the total on hand is created when
you want to see it, it is not stored anywhere.

3. Two tables similar to the above, excpept: You do have a "quantity on
hand" field in the items table. Set up[ your DB so that each transaction
record causes an execution (ONCE and ONLY once) of a modification to the
"qunatity on hand" field.

In your first attempt, you mixed items and transactions into the same
record. In my opinion, not a viable way to do it.