Inventory Management With Expiry Dates & Incremental Depletion

[ndbennett]

I am trying to create an inventory database for our company that manages product expiry dates and phased depletion (i.e. partial stock issue over time).

I have created the table for the master product data, then a second table which has a look-up for the product plus entry for the Expiry Dates for the various batches we have at hand, and also the current inventory for that batch.

I want to create 2 forms, one to allow me to add stock to the second table - this will likely be with a new Expiry date but sometimes return to stock of some of an existing batch; the second form will be to withdraw stock from the current inventory - and I need to be able to see the inventory available per Expiry date. Of lesser importance but nice if possible, would be the ability to add Purchase Order numbers to the additions and withdrawals.

I am struggling to conceive how these transactions will be represented and where the data stored - whether a query or a table. Any guidance or advice is much appreciated.

[ranman256]

why isnt there just 1 table for tInventory? Each batch has its qty and expiration dates.
a query will tally total inventory for 1 product.

[orange]

Do your design work with pencil and paper, it will save you time and frustration.
Working from a description of your business, identify the things(subjects) involved and their attributes.
Identify and mock up some of the outputs the business needs.
Create some test data and test scenarios to ensure you can store the data you need in tables you have identified. Then work through some of the scenarios to ensure you can pass/receive data and get the outputs.
It is much easier to design, analyze, test and adjust on paper than with a physical database.

Here are a few links that should be helpful to you.
-info on database planning and design (videos, articles and tutorials)

- stump the model

-free video from software-matters on building a Stock Management Database with Access

- Allen Browne Stock on Hand --Inventory

-you should also do some research on FIFO and LIFO --these are costing mechanisms that deal with costs and sales of items/products that have limited shelf life(best before date, stock rotation)

More FIFO LIFO
https://www.accountingdetails.com/fi...ls_costing.htm

There are several good youtube videos on FIFO and LIFO. Search youtube with these term(s).

You should search the forum for Inventory/Stock Management articles, questions, responses, tips etc.
Inventory is not a trivial subject and is not a "business" to be considered for learning or a first database.
Work through a tutorial or 2 in the first link I gave above --you will learn, but you have to do the work.

Good luck with your project.

[ndbennett]

Are you suggesting that the batch expiration would be a characteristic of each entry in the main inventory table? I was reluctant to do that because I thought I would need to create an entirely new entry with each delivery, and also that I would have an ever expanding inventory list.

[ndbennett]

Thanks - some great resources there; much appreciated.

[orange]

I was not suggesting anything specific to attributes of your proposed tables. I was saying that inventory is not a trivial subject. The important thing is to fully understand what exactly you are trying to automate/support.
Get the facts down on paper (or digitally) and confirm that you have covered the scope of the requirement.
Don't start with a physical table or 2 and hope that things will magically fall into place --the odds are against you.
Build a prototype, test it, adjust it... and repeat. When the prototype/model works, develop your database and application based on your blue print.

The FIFO/LIFO stuff deals with stock/inventory that typically has a shelf-life/best before date. It is a different concept to consider, but often happens with inventory/stock management.

Good luck.

[ndbennett]

I was not suggesting anything specific to attributes of your proposed tables. I was saying that inventory is not a trivial subject. The important thing is to fully understand what exactly you are trying to automate/support.
Get the facts down on paper (or digitally) and confirm that you have covered the scope of the requirement.
Don't start with a physical table or 2 and hope that things will magically fall into place --the odds are against you.
Build a prototype, test it, adjust it... and repeat. When the prototype/model works, develop your database and application based on your blue print.

The FIFO/LIFO stuff deals with stock/inventory that typically has a shelf-life/best before date. It is a different concept to consider, but often happens with inventory/stock management.

Good luck.

Sorry, my bad - I was replying to the comment from ranman256; I should have clicked Reply with Quote rather than Reply


I fully take on board your point about proper design upfront. In fact, I actually tried to do that but I did not really appreciate that the prcess of going from paper to finished db would go through several iterations. It was when I started to build the db I realised that my initial design was inadequate. Your point, however, is an encouraging one - Rome wasn't built in a day! Thanks again!

[ArviLaanemets]

I would use following design.

tProducts: ProductID (autonumeric PK), [ProductNo] (optional product/article number), ProductName, ... is used to register products, and is used as lookup table for forms and queries;

tProductBathes: ProductBatchID (autonumeric PK), ProductID (FK), BatchDate (serves as batch identificator, and as a base for ExpireDate calculation), ExpireDate - here you register the new batch for product;

tStorageLog: StorageLogID (autonumeric PK), ProductBatchID (FK), LogEntryDate, LogEntryType (smallint, has different values for arrival, sending out, sending into production, returning from production, scrapping due expiring, inventory correction, etc.), Qty (either incoming into storage quantities are always positive and outgoing negative, or quantities are always positive, and LogEntryType determines, are they multiplied bi 1 or -1), ... - I didn't use Inventory in table name here, as it will be more passing elsewhere;

tInventory: InventoryID (autonumeric PK), InventoryDate, ProductBatchID (FK), Qty - here you periodically (p.e. at end of year/month) register the results of inventory of your storage (real count of all product batches currently in storage). When the count differs from quantity calculated from tStorageLog, then you register the correction in storage log with proper entry type. The table us useful, as then you can calculate current product batch quantity at any given date as quantity in tInventory at last InventoryDate before this date + sum of all quantities for this product batch between InventoryDate and given date instead of summing up all entries in tStorageLog. And it allows you delete too old entries from tStorageLog without losing all info.

When you want more info about cleaned up periods in tStarageLog, then you may consider having in tInventory a separate row for currently in storage quantities, and additionally enter calculated rows for every LogEntryType with summary quantity during inventory period (you add a LogEntryType field, and for actual inventory LogEntryType will be p.e. 0).

[orange]

@ndbennett
No need to be sorry. I was just trying to promote more analysis before jumping to physical database. If I had checked a little deeper, I should have recognized the "batch" reference in ranman's post. Anyway, good luck with your project and I hope you do find the reference links/info helpful.