Access/SQL Query Help

[gmonroe2]

Hi, I'm struggling w/ a query, hoping for a little guidance...

I have 2 tables:
customer_price & vendor_cost

customer_price
cust_id product price_date markup
ABC widgets 10/11/08 +7.35
ABC widgets 10/16/08 +7.45
ABC widgets 10/20/08 +7.55

vendor_cost
vendor product cost_date cost
ZYX widgets 10/10/08 34.50
ZYX widgets 10/12/08 36.25
ZYX widgets 10/19/08 38.25

In order to calculate the customer's price I need to take the cost + the markup. The trick is that the cost_date must be dated before the price_date. Obviously there can only be 1 cost record for each price_date.

I have tried this:
SELECT c_price.customer_id, c_price.product, c_price.price_dat, c_price.markup, cost
FROM customer_price AS c_price ,
(select top 1 v_cost.cost as cost
from vendor_cost as v_cost inner join customer_price
on (v_cost.product=customer_price.product) and (v_cost.cost_dat<=customer_price.price_dat))

but this returns the same cost record for all price records. any help would be GREATLY appreciated.

Thanks,
gm

[gmonroe2]

got it figured out. i need the sub-query as a field not as the source :)

thanks though