Access query: Average multiple cells in One column -

what im trying create shipping estimator if will. @ point ups , fedex have volume based prices , unfortunately vendor doesnt supply dimensions each product. offer weights.

in table 1 have part#, weight, , price of shipping of past shipments. in table 2 have part#, weight of never been shipped items.

what do: set query take products table 2 , find weighted items table 1, average there shipping prices , new number estimated shipping new product. part im having hard time wrapping head around searching range of weights. lets product 5lbs , want shipping prices of products ranging form 4.75 5.25.

my knowledge of access limited. appreciate guidance here.

select partsunshipped.partnr, partsunshipped.weight,  (select avg([shipprice]) [partsshipped]  [partsshipped].[weight] >=[partsunshipped].[weight]-0.25  , [partsshipped].[weight] <=[partsunshipped].[weight]+0.25) estimated partsunshipped 

this 1 way it. way join 2 tables , run avg on shipped price return non-nulls.