How to use subquery to populate column in Access query

How to use subquery to populate column in Access query

I am working on an Access database which is used for forecasting purchases
and I am trying to create a query in which would give me list of records
with valid prices and row sums.
I am running into problems when I try to combine prices to quantities. I
have following tables
Table that contains forecasting data (columns not relevant for this query
omitted)
need_rows
ID product_id qty use_date
----------------------------
1 1 100 1.1.2014
2 1 50 15.1.2014
...
And table for prices
prices
ID product_id price valid_from
----------------------------------
1 1 1 1.12.2013
2 1 2 24.12.2013
3 1 5 10.1.2014
...
Query resulst should be something like below:
result of query
product_id use_date qty price sum
---------------------------------------
1 1.1.2014 100 2 200
1 15.1.2014 50 5 250
...
Meaning that I need to fetch valid price to each of the rows based on the
use_date from need_rows and valid_from date from prices. Valid prices is
the one that has valid_from date equal or most recent to use_date.
Below is one of the approaches I have tried with no luck.
SELECT prices.price
FROM prices
WHERE (((prices.product_id)=[product_id]) AND ((prices.valid_from)=
(SELECT Max(prices.valid_from) AS valid
FROM prices
WHERE (((prices.product_id)=[product_id]) AND
((prices.valid_from)<=[use_date]));).));
Any help is appreciated!