FIFO inventory calculation

[RonAle]

Hello all,
I need to design a simple inventory system based on the FIFO (First In First Out) principal.

Currently I have an excel spreadsheet that gives the resulst I'm looking for but would like to bring this into Access.

Where I'm having trouble is translating the excel calculations into Access.

I wanted to attach the spreadsheet but don't see how.

The set is as follows.
I have a table that has the receipt date and quantity per sku. A sku many have many receipt dates and quantities.
I also have a Balance On Hand which tells me the current Balance on Hand for each sku.

With these two tables I'm able to do the fifo calcualtion in Excel.

I tried to implement the same construct in Access but I'm stuck with the calculations.

Anyone have any ideas?

Thanks.

 

[JoeAtWork]

I don't know what the specifications for FIFO are. But in other inventory databases I have had a Transactions table, where there was a Quantity field. If the Quantity was positive, it was incoming, if it was negative, it was outgoing. If you wanted the current stock on hand, you would simply sum all the transactions for that product. So in other words, you have a query that groups by product and sums the quantity.

 

[JerryKlmns]

RonAle

You' ll have to get the first non-zero quantity of the oldest received date, on that sku, reduce it by the desired amount or make it zero and then go for the next non-zero quantity of the new oldest received date, till you cover all the amount.

This sounds like VBA with recordsets.

Don't treat access as excel and vice versa.

 

[lespaul]

and there's really no such thing as a simple inventory system

Have you looked into how much it would be to purchase a FIFO Access template...that may be the route to take.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins