It

2. ## Re: Inventory Calculation (A2K)

I'm confused indeed. You state "Cost per item on 1st order is \$5 & the quantity received is 10.", then in the calculation ( QtyRcvd * Cost ) seems to correspond to ( 12 * \$7.00 ). <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

3. ## Re: Inventory Calculation (A2K)

Hans, I've been glancing at this...I think 12*\$7.00 is the new shipment.

4. ## Re: Inventory Calculation (A2K)

Hans,

And confused you should be based on my explanation. It always looks OK until, even after previewing it, I post it. Something loses in the translation between my mind and my keyboard.

So, that being said, try, try again.

1. First order ever to go on the system:

Qty: 10
Cost per: \$5.
Therefore, QOH = 10
AverageCost: \$5.

2. 6 removed from stock
Therefore QOH = 4

3. Second Order ( and same logic to be applied to all future orders )

QtyRcvd: 12
Cost: \$7 per

This is the formula that they want to see for all future orders:

(((( QtyRcvd * Cost ) + ( AverageCost * QOH )) / (RcvdQty + QOH)

(( 12 * \$7.00 ) + ( \$ 5.00 * 4 ) / (12 + 4 )

\$84.00 + \$20.00 / 16 = \$ 6.50 = New Average Cost

How do I bring in the New Average Cost into my next calculation where it would be come the Current Average Cost?

Hope this helps. Thanks for looking at this for me,

5. ## Re: Inventory Calculation (A2K)

When you store the value "quantity", is it stored in a field, or is each item entered "quantity" number of times, linked to a PK, or something along those lines?

6. ## Re: Inventory Calculation (A2K)

Jeremy,

I appreciate your looking in. The setup is as follows:

There is a Master table of Parts with descriptions & stuff.

There is a linked Receiving table that stores the quantity and cost for each order coming in.

It's the manipulation of the first Average Cost vs the future calculation of Average Cost that has me confuzzed.

Hope that helps,

7. ## Re: Inventory Calculation (A2K)

From your explanation I think the average cost should be stored in the master table. In your related table, the average price for each order is the original price, so no calculation is needed there. I'm working on some sort of totals query or something similar, with a temporary field for average price, grouping by partid. Hopefully I'll be able to set it up to use the end result in an update query to update the average cost in the master table... perhaps my telling you what I'm attempting will give you new ideas (if you haven't thought of this already)

8. ## Re: Inventory Calculation (A2K)

Jeremy,

Good Thoughts!! I've bandied about some various scenarios, most of which came back and bit me, but I'm going to have a go on variations on your themes. Will let you know how it goes.

Thanks again,

9. ## Re: Inventory Calculation (A2K)

As soon as I figure out how to update your table from a totals query...I'll post a very simple example <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

10. ## Re: Inventory Calculation (A2K)

Thanks muchly, looking forward with eager,

11. ## Re: Inventory Calculation (A2K)

<P ID="edit" class=small>(Edited by NYIntensity on 29-Jul-05 01:22. Note to Hans: I know you detest storing calculated values...any suggestions?)</P>Alright, here's how it works...
I have two tables, tblParts, and tblOrders. tblParts contains your parts (obviously), the part ID, the quantity on hand, and the average price. tblOrders contains order ID, part ID, quantity recieved, and the price received at.

qryPartTotals is a simple totals query which groups tblOrders by Part ID, summing the quantity on hand and averaging the price. qryUpdatetblParts is an SQL query which updates tblParts using DLookup statements. I hope this is what you were looking to achieve.

12. ## Re: Inventory Calculation (A2K)

<img src=/S/cool.gif border=0 alt=cool width=15 height=15>
Thanks Hans.

13. ## Re: Inventory Calculation (A2K)

Note to Jeremy: Inventory databases are an exception to the rule that you shouldn't store calculated values: if you want to keep everything dynamic, the calculations can become very complicated.

14. ## Re: Inventory Calculation (A2K)

Jeremy,

Thanks kindly for your extra work. I guess the only question I was going to raise, and Hans has already answered it, and that is the storing back of a calculation in a table. I'm going to have a go at this first thing in the morning when I can see straight ( OK, not as crookedly ) and get back to you.

Again, it's appreciated,

15. ## Re: Inventory Calculation (A2K)

Andy,
Could you check out the formulas and tell me if they give you the result you wanted? For example,
an order for 1 piece of part 1 at \$4 and an order for 3 pieces of part 1 at \$8 gives the average cost of \$6.....is that what you reqest?

Page 1 of 4 123 ... Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•