struggling with query, plz help

[jimgros]

Hi guys, I'm a beginner and I can't get this query right. plz help.

I have 1 table that contians my stock. Say fields are:
Stock_ID
Size
Clarity
Shape
Color

I have another table which is a standardized price list. Fields are:


Size_Min
Size_Max
Clarity
Shape
Color
Price

So I'm trying to get a query going where I have "stock_ID" with it's matching price next to it coming from the standardized price list.

This is what I did
SELECT Stock.Stock_ID, Pricelist.price
FROM Pricelist INNER JOIN Stock ON (Pricelist.Shape = Stock.Shape) AND (Pricelist.Clarity = Stock.Clarity) AND (Pricelist.Color = Stock.Color)
WHERE (([Stock].[Size]>=[Pricelist].[Min_size] And [Stock].[Size]<=[Pricelist].[Max_size]));

But I get NADA! Can someone pls help? I really don't understand it sounds so simple...
The pricelist is really consistent and verified. There are no sizes or colors or clarities missing. All sizes/color/clarity combinations exist in the table and have a unique price.

Thanks

[June7]

A join is not going to work because can't join Stock size on PriceList size. Instead of joining the tables, do a lookup of the value. Build a query of the Stock table and create a field with a DLookup expression.

DLookup("Price", "PriceList", "Shape='" & [Shape] & "' AND Clarity='" & [Clarity] & "' AND Color='" & [Color] & "' AND " & [Size] & " Between [Min_size] And [Max_size]")

[jimgros]

Cool!!! Thx for quick reply. I get now why join might not work.