Access query: Average multiple cells in One column

Question

What Im trying to do is create a shipping estimator if you will. At this point ups and fedex have volume based prices and unfortunately my vendor doesnt supply dimensions for each product. However they do offer weights.

In table one I have Part#, weight, and price of shipping of past shipments. In table Two I have Part#, weight of never been shipped items.

What I would like it to do: set up a query that will take products from table 2 and find like weighted items from table 1, average there shipping prices and us this new number as the estimated shipping for the new product. The part im having a hard time wrapping my head around is searching for a range of weights. Lets say my product is 5lbs and I want to get shipping prices of all products ranging form 4.75 to 5.25.

My knowledge of access is somewhat limited. I would really appreciate some guidance here.

JJ32 JJ32 · Accepted Answer · 2016-02-06T22:58:41

SELECT PartsUnshipped.PartNr, PartsUnshipped.Weight, 
(SELECT AVG([ShipPrice]) FROM [PartsShipped] 
WHERE [PartsShipped].[Weight] >=[PartsUnshipped].[Weight]-0.25 
And [PartsShipped].[Weight] <=[PartsUnshipped].[Weight]+0.25) AS Estimated
FROM PartsUnshipped

This is one way to do it. Another way would be to simply join the two tables and run an Avg on the Shipped price but then you would only return non-nulls.

Access query: Average multiple cells in One column

1 Answers