When I'm entering the order Invoice I want to bring the buying price of (Item1) and its Remaining quantities from (purchaseDetail) table,(Item1) had multiple records in the (purchaseDetail) table depends on it's purchase invoice .In my continuous subform ordersDetailSubform I have a combobox cboItemsName .So , depends on the quantity I entered and the previous quantity of Item1 orders I want to bring the buying price list as a row source query of cboItemsName containing only the available quantity prices .| ItemId | quantity | buyPrice || 1 | 5 | 3$ || 1 | 10 | 2$ |Let's say I have previous orders of Item1 of quantity (4) .If I now entering my new order In my orders form , when I'm typing (Item1) , the cboItemsName row source query show me the Remaining quantities and its prices like that| remaining quantities | buying Price||>>>>>>1<<<<<<< |>>>>3 $<<<||>>>>>>10<<<<<<|>>>> 2 $<<<|Can help with the best Ideas to do that ?

if you want to go that path.then not the purchase that you want to involve.but you need a Separate Location for each purchase if the purchase price is notthe same.and on allocating for sales you should enter which Location you have withdrawn thesaid item.therefore, your combo should include the Location, Item, and price.and item is deducted from that Location.

Hamza_dm "... 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.

Hamza_dm 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 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.

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.

Multiple Item price depends on remaining quantities

George_Hepworth
Silver Contributor
Dec 23, 2021
Hamza_dm

"... 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.
- Hamza_dm
  Copper Contributor
  Dec 24, 2021
  Thank you
  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 .
  - George_Hepworth
    Silver Contributor
    Dec 24, 2021
    Hamza_dm
    
    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.
arnel_gp
Steel Contributor
Dec 23, 2021
if you want to go that path.
then not the purchase that you want to involve.
but you need a Separate Location for each purchase if the purchase price is not
the same.
and on allocating for sales you should enter which Location you have withdrawn the
said item.
therefore, your combo should include the Location, Item, and price.
and item is deducted from that Location.
- Hamza_dm
  Copper Contributor
  Dec 24, 2021
  Hello bro
  The items are in the same location , the same item has different buying price . So I want to know which item price I'm ordering now to give it the selling price and also to calculate profits.

Forum Discussion

Multiple Item price depends on remaining quantities

Share

Resources