intervals

[sean w]

I need to write a query that will return a specific avg depending on the
demand of a specific item. I have parts that are purchased over time and
some are at different prices. I also have demand for this part. What I would
like to see is the part number's "weighted average" based on the demand
given. The demand is housed in a seperate table as is the FIFO prices. In
excel this is a long string of IF statements to come up with the weighted
average. Here is an example of what I would like the out put to be.
p/n [total demand] say 500 pieces, purchased parts at 200 @ $10.00, 200 @
$15.00 and another 200 @ $20.00. A straight WA is $15.00, however with the
not using all of the last group @ $20.00 I would need to calculate what 500
pieces WA would be. We call it the consumption weighted average and wasn't
sure if this was possible in Access.

 

[S.Clark]

Anything is possible in Access, but it just may not be part of the built-in
functionality. You will probably not be able to complete what you need in a
single query, but through a series of queries and/or additional custom
procedures, you should be able to make it happen.

Also, Total Access Statistics may be of value:
http://www.fmsinc.com/microsoftaccess/statisticalanalysis.html

 

[Michel Walsh]

Note how it would be quite easy with a little change of the fields:


qtyFrom qtyUpTo priceDifferential
0 200 10
200 400 5
400 600 5


which stands for: for qty in excess of 200 but less than 400, the unit
price is 10+5, as example.


With such a structure, the total price, for a wanted quantity Q, would be:

SELECT SUM( (Q - qtyFrom)*priceDifferential)
FROM ...
WHERE Q <= qtyUpTo


As example, for Q=450, the total price is (450-0)*10 + (450-200)*5 +
(450-400)*5 = 6000
or, in a more conventional way: 200 @ 10$ + 200 @ 15$ + 50 @ 20$ = 6000


Now that we know what kind of structure can be easy, it is just a matter to
translate the actual data into the wanted one. I assume your actual data
has a date stamp:

qty unitPrice dateStamp
200 10 date1
200 15 date2
200 20 date3



with: date1 < date2 < date3

which defines the order. ***Untested***, but the following query should do
the job:


SELECT SUM(Nz(b.qty, 0)) AS qtyFrom,
qtyFrom + LAST(a.qty) AS qtyUpTo,
LAST(a.unitPrice) - Nz(LAST(c.unitPrice), 0) AS priceDifferential
FROM (yourOriginalTable AS a LEFT JOIN yourOriginalTable AS b
ON a.dateStamp > b.dateStamp) LEFT JOIN yourOriginalTable AS c
ON a.dateStamp > c.dateStamp
GROUP BY a.dateStamp, c.dateStamp
HAVING c.dateStamp = MAX(b.dateStamp)


Save it, and use it in the FROM clause of the first query.



Hoping it may help,
Vanderghast, Access MVP