stock/inventory control

[sillysod]

This is a really broad question but im just interested to know how you experienced developers handle an inventory system in an access database.

I can see two options, either having a "lot history" table which lists all transactions and then having control that calculates a total
ie
(Purchases + adjustments in + o/s orders ) - (issues + adjustments out)

but i can see that running quite slowly in a larger table.

The other option would be to have a "lot history" table (a necessity for audit purposes) and have a field in your parts table which shows the total inventory values, and these values are updated after each reciept into stock / issue from stock.

Personally i can see immediate benefits to both methods, and would lean toward the first option, since the second doesnt seem very versatile.

However, to throw a spanner in the works.
It isnt always possible to group the lots in stock by part.
IE some parts may have been purchased at different prices etc and it would be important to record the price for that particular lot ( if you were going to look at costing sales orders)

Therefore each individual lot would need to be listed.

in this case would you have a lot history table and a table that shows a record for each lot in stock?

Like i say intersted to see other peoples opinions

 

[Trendsetter]

If you go to

http://www.fhsservices.co.uk

and have a look at Wine Seller - Stock transaction form - this allows for purchase and sale on the same form plus any value of bought or sold for even though there are default values shown.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[sillysod]

I have been reading a few websites etc,
My requirements are :

1, need to show full audit trail of lots
2, need to show lots in stock
3, need to be able to allocate lots to an order to show
the costs of that job and therefore profit margin
4, a full lot quantity may not be issued to one order
(ie 10 widgets go into stock, and only 5 issued to order)
5, Parts could be supplied from many suppliers
6, Many suppliers could provide many parts, at different unit sizes (ie packs of 100 / packs of 500)

+ others ut they the most important so far

Im thinking about maintaining a transaction table for stock.
however my main problem is handling point 4

If i was to only issue full lot quantities then i could probably have one record for every entry into stock, and having a field for how/when it was issued/deleted from stock.

the stock listing would then only stock those records where no deletion has been recorded.

But im sure this isnt that good of a way to handle an inventory. and its big flaw is point 4

I have searched the web extensively and have not been able to find a clue as to deciding on a table structure which gives what i need.

Im confident i can handle all other points, and ensure that stock history and current stock is accurate. But point 4 is really starting to hurt my head.

Any pointers would be appreciated

 

[Trendsetter]

If everything you buy goes into tblTransaction upon arrival
then each allocation to each job can be recorded in the table and the differential is the amount left in stock - the varying prices can be handled in this table as each transaction can have its own price.
TransID
StockID - foreign key to tblStock
TranDate
TranType - in or out
TranQuan
TranUnit
TranPrice
JobID - foreign key to tblJobs



Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[sillysod]

but how do you match up the transactions

ie lets say i buy the following

75 widgets @ £10 on PO 100
100 widgets @ £11 on PO 102
50 widgets @ £10 on PO 103

i want stock listing at this point to show
BTW i dont want to enforce any particular stock control type ie FIFO or LIFO i want the user to select the lot that they are going to issue from

Widgets
75 - PO 100 - Serial No 676767
100 - PO 102 - Serial No 676548
50 - PO 103 - Serial No 698756

See how each reciept is an individual lot.

no lets say that 25 widgets from PO102 are issued to SO199 i would see a transaction of 25 out

However how would the stock list be recalculated? I know this would work in aggregate but i want each lot to be treated individually. So that the user can track the full history of an individual piece of stock

ie
Lot History for Widgets - Serial No 676767
75 widgets reciepted into stock on 10/01/04 from PO100
25 widgets issued to SO199 on 30/01/04
5 widgets issued (shrinkage) on 15/02/04

 

[Trendsetter]

Each transaction would record the serial number - and the PO if that was relevant.
You could record a receipt as +tive and an allocation to a job as -tive.
Run a query against each serial number and it will give you the stock balance for the item.


Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[sillysod]

Good idea,

Sounds like i might have to store a LotID field which autogenerated by some function upon reciepting into stock then when deletions are being made, the user selects a lot to delete from, the LotID is picked up and aggregates are calculated from that.

Then lots could be consolidated by amending the Lot number

and they could be split by making a deduction and an addition using that same lot.

Yes yes yes that would work

I dont want to use the serial number since i want this to be a field entered by the user. and in order for this to be as versatile as possible i dont want to restrict the user in any way. (ie enforcing no duplicates)

I work in the metals industry, metals dont have serial numbers, they generally have a "cast" number (a "heat" number to the americans) this represents the particular cast of metal from which ingots are made, this is all waffle but i do have a point

The cast number is crucial to quality assurance as it is an indentifier of a particular cast and therefore the chemical analysis / when it was made / where it was made.

As far as stock goes we use the serial number as this cast number as it is the nearest thing

but what im getting at is that there can be multiple lumps of metal in stock from the same cast.

Again im waffling about stuff that isnt really applicable, but this project is going to be a personal task and not used for any particular thing therefore it is important that i make it versatile, so maybe if one day i find a use for it ...

to sum up
Trendsetter, you are the man.