design to calculate a balance stock

[zekkk]

Hello everybody,

Firstable, excuse me for my English. I have to build an application that
calculate the balance of a stock.

I have the choice with 2 tables design.

First possibility with 3 tables :
**********************

I do a table Product, a table Purchase and a table Sales. And to calculate
the balance, I calculate the purchases records minus sales records.

Second possibility with 2 tables
************************

I do a table Product and a table Stocks. The table Stocks contains a field
Purchase and a field Sales.

What is the best tables design. Could you give me advantages and
disadvantages for each design and when we used it.

Thank you in advance for your help and have a nice day
Fabrice

 

[Scott McDaniel]

Hi Fabrice,

Take a look at Allen Browne's site regarding stock counts:

http://users.bigpond.net.au/abrowne1

There's a section devoted to issues similar to yours.

 

[zekkk]

Thank you for your answer.

Unfortunately, the link is dead.
Fabrice

 

[Allen Browne]

The site has moved to:
http://allenbrowne.com
and the article is at:
http://allenbrowne.com/AppInventory.html

 

[zekkk]

Thank you, the link is OK, but I don't find the answer of my question.
If someone can help me.

thank you in advance
Fabrice

 

[Allen Browne]

Perhaps a variation on your #2 choice might be best.

Two tables: Product, and Transaction.

Transaction has fields:
Quantity
ProductID
Multiplier

The Multiplier fields is a lookup field indicating if the transaction is a
purchase or sale. If you use the lookup value -1 for sales and 1 for
purchases, you can calculate stock movement as:
Sum([Quantity] * [Multiplier])

It would be possible do do this just by using negative values in the
Quantity field, but that has 2 limitations:
- it's a messy interface to develop, and possibly prone to errors;
- it may not be adequate for credits (sales that are returned) or
return-to-supplier (reversed purchases).

 

[zekkk]

Thank you very much
Fabrice

Perhaps a variation on your #2 choice might be best.

Two tables: Product, and Transaction.

Transaction has fields:
Quantity
ProductID
Multiplier

The Multiplier fields is a lookup field indicating if the transaction is a
purchase or sale. If you use the lookup value -1 for sales and 1 for
purchases, you can calculate stock movement as:
Sum([Quantity] * [Multiplier])

It would be possible do do this just by using negative values in the
Quantity field, but that has 2 limitations:
- it's a messy interface to develop, and possibly prone to errors;
- it may not be adequate for credits (sales that are returned) or
return-to-supplier (reversed purchases).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you, the link is OK, but I don't find the answer of my question.
If someone can help me.

thank you in advance
Fabrice

contains

a