Forum Discussion
Multiple Item price depends on remaining quantities
"... and its Remaining quantities from (purchaseDetail) table..."
How do you define "Remaining quantities"? What does that mean in the context of this work flow?
Are you pricing in bands? I.e. quantities up 4 get one price, quantities 5 to 9 get a lower price and quantities of 10 and more get a lower price?
I can see that, but what does this have to do with "... have previous orders of Item1 of quantity (4) ."
Is this "previous order" pertinent to the one you are currently adding, or does it refer to any and all "previous orders" for that item by any customer at any time?
If you can narrow down the picture, it'll be easier to offer ideas. Thanks.
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 .