Home Ask Login Register

Developers Planet

Your answer is one click away!

scate636 February 2016

Access query: Average multiple cells in One column

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.

Answers


JJ32 February 2016

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.

Post Status

Asked in February 2016
Viewed 2,275 times
Voted 10
Answered 1 times

Search




Leave an answer


Quote of the day: live life