Forum Discussion
Multiple Item price depends on remaining quantities
All what I mean that I have many Items In the store , every item entering to store by different buying invoices , so that it has different buying price , when I'm selling , the quantities in the store will decreased , so I want to know the price of the current quantity every time I'm selling , to be able to do some calculations like profits .
Thank you for clarifying.
You are, then, creating a FIFO inventory system, in which you must track each incoming inventory item as it is received and then allocate those items to purchases in the same sequence: First In, First One.
That's a non-trivial requirement.
Before we get too far into the logic of the calculation, though, we need to be sure your tables are appropriately designed to handle this sort of inventory system.
Please provide a screenshot of the tables in the relationship window so we can look them over. If they support the requirement it's okay to move on to the next step.
George_Hepworth thank you so much
I'm sorry for being late.
this is my database tables , the "TblQaid" stores the additional purchases costs , this costs(ratio) + buying price will give us the actual cost for each item of any purchasing invoice, so the "PurDetail" tabel has multiple "actual cost" for the same item . I want to calculate profit of every item in "orderDetails" table depending of the actual cost , that cost differ depend on the soled quantities from the "purchases details " as I explained in above comments.
George_Hepworth thank you again
Although a more appropriate, complete response depends on the actual tables in YOUR database, I went ahead and set up a sample with some queries to calculate a running inventory and cost at transaction dates as a first try at something that might work for you.
thank you George_Hepworth
I will check it to understand how its work .
- My research indicates this is a pretty sophisticated process. The best response I found were based on SQL Server, where you have the advantage of using CTEs to do some of the preparatory calculations to fold into the final calculation. I did some more work on Access queries but ultimately, I think this will be a VBA function in Access if you can't move the data into SQL Server or an equivalent server-based database.
I think you can find a lot of references by searching on "FIFO" inventory and cost of goods sold calculations that do reflect the varying profit margins resulting from varying costs of goods purchased.
At the end of the day, I came to the conclusion that this is one of those infrequent situations where hiring a specialized consultant for this one phase of your project might be a cost-effective solution.
If you can move your data into a more robust RDBMS, like SQL Server, other approaches are possible.