FIFO Inventory

[Analogkid]

Trying to figure out the best way to do my inventory. I have read previous posts but trying to wrap my brain around how to make it work on my database. What I have is a “Materials” table. The issue is that we may purchase a material on one month for $10 ea. Then, the next month, we purchase the same item for $11 each. We would like to sell the $10 items first. I understand that I may need 3 tables. One for Materials On Hand, One for Materials Purchased, and one for Materials Sold. Giving a sample of my database attached here, how would I make this work in the Materials Tab in my database? How would I make the FIFO calculations? Please let me know if you need more details.
Thanks,

[orange]

I think there is much more to inventory management and FIFO than you have found. There are many aspects documented at the following site that may shed more light on the issue.
http://www.accounting4management.com...ls_costing.htm

There are a number of existing data models related to Inventory management at
http://www.databaseanswers.org/data_models/index.htm These may give you some ideas.

Good luck with your project.

[Analogkid]

Thanks Orange. I kind of get the concept but wondering how the best way to make it work in Access 2010. I think I might go with the 3 table idea but how do I run the query to pull up the earlier date of an item and, how to calculate the differences and also, have the “items used” subtract from the “Materials on hand”
Thanks for the “Good luck” I will need it!

[Analogkid]

Any ideas how I can get this to work? I did download a FifoStock.mdb which gives a great example. But since I don't understand code that well, was hoping to have someone explain in detail the best way to do this. I want, when I select a material item, it will pull up either a query results of FIFO or, if possible, have it auto adjust when you put the Quantity in.
Thank you for your time.

[orange]

Which fifoStock.mdb did you download?
What specific issues are you having?

[Analogkid]

Which fifoStock.mdb did you download?
What specific issues are you having?

Attached is the example DB. If you can open mine, you will see the see the 3 tabs, Work Order Employee, Equipment, and Materials. Under the Materials tab, this table is an inventory table which will need to have FIFO because we purchase the materials at different prices. I'm trying to figure out the best way to go about this. Either when we select a material, it will pull up a query of that specific material with the dates and prices, and they pick the earliest date. Or, have it automatically keep a Running total. This is the only thing I have left to fix before going live. I tried understanding the example but can’t figure out the best way to do in in my database.
Thank you
FifoStock.zip

[orange]

I have access 2003 so can not open the accdb. Can you make a copy of your database in mdb format?

I have looked at the FifoStock.mdb an can see how some of it works.
The author Purchases Products on a date and records the date, the products, the quantity of each and the price he pays for each.
Then when he sells product(s) he creates a sales invoice and records the date, product, quantity sold and the selling price.
To do his FIFOStock (to determine what his current FIFO Inventory is) he repopulates the FIFOStock table, using info from the "zx" query in which he processes purchases and sales records, and fills his sales orders with the products from the earliest purchase dates. He then adds detail records to the FIFOStock table based on his FIFO processing. On his frmFIFO form he displays the Purchases, Sales and FIFO Stock.

I noticed he had misnamed one of his PurchaseMain objects, he had 2 with Salesmain captions.

It's a bit confusing because of the lack of comments and error handling, but I could assist you understanding what he has.

[Analogkid]

Test.zipThanks Orange. My database was built in Access 2010 and can’t save it to 2003 so I just copied an example of the tables I'm working with. I get close but just can't grasp the concept on how this will work. If you could help me with the code or queries/tables, that would be awesome. So, I will have a Purchase form and sold form that can keep track of the Materials table items and, with a FIFO scenario. Hope this makes sense.
Thanks for your help!

[orange]

I am attaching a modified version of the original FifoStock.mdb that I have named FIFOStockjed.mdb

I have added some comments to the module vba code, and have created a few new queries.
I also "made some Purchases and Sales"[ on Jun 26 2012] to see how the results changed.

It seems to work fine for me.

The mdb is saved within the attached zip file

[ali4frnds]

I am not able to Open Module or Anything. Just a switchboard. Can i see the Modules / Queries?

[xlance]

I am attaching a modified version of the original FifoStock.mdb that I have named FIFOStockjed.mdb

I have added some comments to the module vba code, and have created a few new queries.
I also "made some Purchases and Sales"[ on Jun 26 2012] to see how the results changed.

It seems to work fine for me.

The mdb is saved within the attached zip file

It seem we don't have access to macros queries or anything please help?

[orange]

Hold Shift down when opening database

[Tim89]

Can somebody help me? I would like to show the purchase costs for each record in table SaleDetail using FIFO. (FIFOStockjed.mdb)

[orange]

I am attaching a revised zip. The database is in 2000 format.
Please read the readme_orange.txt also.

[mf.farooqpk@gmail.com]

I am attaching a revised zip. The database is in 2000 format.
Please read the readme_orange.txt also.

Thanks for this very useful example that really works on uptodate fifo stock. I am working on the database that is using weighted average inventory method. Can U share the post like fifostock.mdb.

Waiting for quick response