Keeping stock figures in table?

[NewShoes]

Hey all,

I'm working on a stock control database. Very simple stock in/stock out kind of thing. However, I would like to know the best way to achieve calculating the totals of each product that we have in stock. At first I was going to simply use the stock in/out and calculate the total left with a sum'ing query, grouped on product. The only problem with this is it isnt very scaleable as it will have to sum every increasing amounts!

Is there a way to keep the stock figure in a table? so when I add, for example, 10 tins of beans, it simply adds 10 to the total?

Thanks,
-NS

 

[pbaldy]

Here's an often-linked discussion on the topic:

http://allenbrowne.com/AppInventory.html

Personally, I'd calculate quantity on hand rather than try to store it. If the app grows to the point where it becomes an issue, it's probably also grown to the point where SQL Server (full or express) is appropriate anyway. I have an app with several hundred thousand transactions and the SQL Server view that calculates quantity on hand returns records instantly.

 

[NewShoes]

Thanks for the reply Paul. Please can you let me know what you mean by 'SQL Server view'?

Also, am I right in thinking, if I chose to calculate it on the fly, I would have to look at every single IN/OUT transaction of, say, Tin of Beans? For example:

In: 10 tins.
Out: 5 tins.

The database would have to calculate IN-OUT=Stock On Hand (5 tins).

If I sell 1,000 tins on beans, the database will have to perform that calulation 1,000 times. Is this right?

Thanks,
-NS

 

[pbaldy]

A view is basically a query, but it lives on the server (and thus is processed by the server) and can be linked to like a table. Here's a Technet article on the topic:

http://technet.microsoft.com/en-us/library/cc917715.aspx

I think it's inaccurate to say the calculation will happen 1000 times. You would be using a query that summed transactions, so it's one calculation. Certainly the number of records that have to be examined can affect the performance, but it's not a linear equation like that.

Allen's method uses a "stocktake", so with that method you are only summing transactions since the last stocktake. I didn't consider my applications to be quantity intensive enough to bother with that, and like I said the performance is still excellent. I also used 2 tables, one for incoming and one for outgoing transactions. Another option is using one table with positive and negative transactions, which would be even faster performance-wise.

 

[stopher]

If I sell 1,000 tins on beans, the database will have to perform that calulation 1,000 times. Is this right?

Not exactly. If the 1000 tins were sold in one transaction then the "calculation" is only done once. If it takes 1000 transactions to sell 1000 beans then yes.

But the work is really done in finding the relevant records in the database. Access would first have to find all "tins of beans" records". This work can be improved with indexing though.

Chris

 

[NewShoes]

Thanks for the pointers people! I think I'll go the calculating route rather than keeping a stock total in the table. Just one small question. Roughly how large (in terms on number of records) do you think it would take for it to become too slow to run?

Also, any hints on how I might split the records up to make it run a little quicker, perhaps being able to specify which year may help?

Thanks,
-NS