msaccess find price at date

Question

Trying to find a solution that works in MSAccess SQL (which is a little quirky)

I have a source table SalesPrices with data in the following form:

Product | Price | Time Effective
25      |  3.49 | 25/01/2013
25      |  3.65 | 02/02/2014

Now, what I want to do is for a given range of dates, produce a table of the price for a product and the week commencing date:

WC         | Product   | Price
13/01/2014 | 25        | 3.49
20/01/2014 | 25        | 3.49
27/01/2014 | 25        | 3.65
03/02/2014 | 25        | 3.65

So, to do this I have created a table of dates, with my desired date range

ID  | WC   
1   | 13/01/2014
2   | 20/01/2014
3   | 27/01/2014
4   | 3/02/2014
5   | 10/02/2014

I'm not entirely sure how to write the query though, it needs to produce a row for every product and every date in the tableofdates, with the price changing at the appropriate time

Something like a cross join of the tables, but I can't quite get the syntax

EDIT to include my query so far

SELECT [SalesPrices].[Product], [SalesPrices].[time eff], TableOfDates.WC, [SalesPrices].[Price]
FROM [SalesPrices], TableOfDates
WHERE ((([SalesPrices].[Product])=25) AND (([SalesPrices].[time eff]) 
Between [tableofdates].[wc] And ([tableofdates].[wc]+7)));

Answer 1

You can do this using a subquery:

select tod.wc, 25 as product,
       (select top 1 price
        from SalesPrices as sp
        where sp.TimeEffective <= tod.wc and product = 25
        order by TimeEffective desc, id
       ) as price
from TableOfDates as tod
where . . . <-- date conditions go here;

I'm not sure where the 25 comes from, so I just made it a fixed value in the query.