Solved FIFO STOCK

[Safari]

HOW CAN I MAKE FIFO STOCK IN MY CURRENT DB WSING QUERIES ?

I HAVE ALL DATA IN ONE FILED AND I WANT TO GET STOCK COST LIKE :

ITEM A 25 UNIT
10 WITH 10 $
10WITH 11 $
5 WITH 15 $

IF I SOLD 21 UNIT THE ACCESS CALCULATE ( 10*10 + 10*11 + 1*15 )

HOW CAN I DO IT ?

THANKS

 

[gemma-the-husky]

This is a hard task

You might want to look at this thread for a detailed discussion of a similar thing.
Average Cost Price Function | Access World Forums (access-programmers.co.uk)

 

[The_Doc_Man]

@Safari - as a matter of etiquette, typing a request in all capital letters is objectionable to some of our members.

Basically, the ability to do this FIFO inventory implies the ability to differentiate between items in stock (in some way) so that you can carry the price of each individual item along and do sums based on those prices. If your inventory is such that you actually CAN tell the difference between items, perhaps because of a serial number, then you simply carry the price along with the inventory item and THAT case is not too tough. But if you can't tell one item from another, it gets tougher.

It MIGHT be possible to instead use dates of when the stock items were obtained and have a separate table based on part number and date showing prices for that shipment of n items. Then you don't have to keep individual items in stock but DO have to know when certain batches were obtained and what they cost.

This would lead into building a JOIN query with the part info, batch number, price, and date linked to your sales invoice so that you could generate the correct pricing. You would also have to consider that once you made a sale involving that batch, you would have to track how much of it was left for another sale. In other words, did you sell all of the lot at price X and now have to start using the lot at price Y? This makes it necessary to keep more than one record for the same item, with the records differing by lot number.

This is a very complex process. Do not think I am telling you not to do it. But you SHOULD be aware that it will be difficult for a person not very comfortable with Access. Great care will be required to track the complexities involved. This will be a case where you would need to do a very thorough assessment of requirements before coding a single line. Walking into this while "shooting from the hip" would be unwise. I will make one general statement: If you don't do a thorough analysis and plan this very carefully, you will NOT be able to do this. I wouldn't want to do it without having the time to plan it either, and I've been programming for over 45 years.

 

[LarryE]

I looked at your table.
The first issue was that you do not have a Primary Key in your table to identify individual transactions, so I included a TransactionID. That will allow you to identify the first transaction for each item for each day since you could receive each item multiple times a day. And ACCESS requires a Primary Key in each table to maintain referential integrity in its tables.

Then, I created a query called QryFirstTransaction, which identifies the first transaction for each itemid. As you can see, it uses the Min of the Date and the Min of the TransactionID for each itemid.

Next, I created a query called QryItemFirstIn, which identifies the first quantity received and unit cost for each itemid.

These queries should give you the information you need to determine which transaction In should be used for FIFO for each itemid.

 

[arnelgp]

i don't think Query alone can do the job.
you need to add another table (cost_allocation table on the demo).
also i feel, you need VBA to compute the FIFO cost (see Module1).

 

[Safari]

i don't think Query alone can do the job.
you need to add another table (cost_allocation table on the demo).
also i feel, you need VBA to compute the FIFO cost (see Module1).

THANK YOU

 

[Galaxiom]

Don't forget to consider how you will deal with returns too.