Different type of inventory - maybe

[Lavens01]

Hello everyone -



I have been struggling to get a project off the ground for some time. I thought I was looking for a FIFO type of inventory tracking but, when I look at the threads related to that topic, they seem to be more involved than what I was thinking. Here is what I want to happen.

In qryTransactions, I have the items that have come in to the warehouse by container number (primary key) and date. Example:

TransDate ContainerID Qty
6/14/11 1254578965 20
6/13/11 9865784523 40

Then, on 6/20/11 an order comes in for 45 units. I want to create a query that will pull all 40 units from the container received on 6/13/11 and the remaining 5 from the container received on 6/14, therefore reducing the qty field to 15 for that container.

I am fairly new at writing code but have some experience with it.

Any help would be greatly appreciated. Thank you so much for your time.

[ajetrumpet]

you're going to have to look at inventory db's at some point. what you want is FIFO. FIFO is an accounting term. it's the most widely used inventory method but they're all complicated anyway. FIFO is by far the easiest.

And the other thing you may not realize is that the only reason businesses keep track of their inventory is so they can offer valuations of it to the balance sheet at the end of the accounting periods. It's also used for maximizing revenue generation and tax differentials.

I attached a small file. I wrote this long ago, but it's very small. If I remember right, it shows you how to use an orders table and sales transaction table together to produce data in queries that offer FIFO and LIFO insight. I hope it helps.

I'm not sure why I wrote it back then, but it's not tough to follow.

btw, I reported this thread to be moved to the ACCESS forum.

[Lavens01]

Hi Adam - Thank you so much for the sample. It wasn't exactly what I needed but it was a great jumping off point. I was able to tweak it a little and make it work perfect. Thanks again!!

[ajetrumpet]

well I'm glad you could, cuz I haven't looked at my small samples in years!

good luck to ya.