Advice/Comments Please..

[Ken]

I am designing a stock control system that requires pricing information.

I Have tables that deal with (amongst other things) Purchase Ordering and
Issuing of stock, the problem is though I have not thought mutch about
holding price info.
Each PO can be attributed to many Issue Orders, and each Issue Order is
assoc. with an address where the stock was issued to. However I need to be
able to select an address and find the total value of all stock used.
I was thinking of having a 'Current Price' Table, with fields Product ID,
Date, and price fields, meaning I can always get the price for a particular
date (I hope)

Can anyone see any flaws or pitfals with this (ie searching it etc)?

Thank you,
Kenny

 

[Sharkbyte]

Ken:

I think you might want to plan a little broader. Management loves to look
at things from new ways, to see if there is a benefit...

I would suggest looking to capture the cost, per line, on the Issue Order,
for the date it is ordered/issued/purchased. This will allow you to track
historical purchase prices, IO amounts, PO amounts, money spent on a
particular item, as well as money spent from a particular location.

Doing something like this, from the beginning, will be much easier than
trying to add something in after the system is up-and-running.

HTH

Sharkbyte

 

[Ken]

How would this work?

my structure is as follows:

tblPO --> tblPOdetail
tblIO --> tblIODetail
tblJobReuirement -->tblJobReuirementDetail

(-->shows relationship)

Each 'detail' is linked to tblProducts.

When Ordering we will be unsure of the price...
So I thought about putting price info onto the PO lines, but then I wouldnt
be able to calculate the total value of the stock on an issue order (ie if we
buy 6 products on the first at £10 each, then another 5 on the third at £11
ea and issue 3 on the fourth how do we calculate the value on the fourth?)

This issue has been driving me crazy for days now!

Thanks
Kenny

 

[Sharkbyte]

Ken:

You would need to do a few things:

What type of valuation will you be using? (FIFO, LIFO, Average. Average
will likely be easiest to manage.)

You will need to create an Item table, which will track quantities/item
value (assuming Average valuation). FIFO/LIFO may require an additional
table to track items on-hand sorted by their value. Also, if you are
purchasing stock, you will need/want to track quantity/value on-hand, as well
as what was issued/used.

If you do not know the price, when you order, at what point do you know the
price? When the item is received? Then you may want to consider another set
of tables to track item receipts. Tie receipts to POs, which tie to items.
Issue order lines also relate to items.

Good luck.

Sharkbyte