By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,910 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Access/SQL Query Help

P: 2
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
Nov 4 '08 #1
Share this Question
Share on Google+
1 Reply


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

thanks though
Nov 5 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.