Hi!
I have an apparently simple problem which I haven't been able to solve to my liking.
I have a table (AllDates) with consecutive dates:
Date
-------
Date 1
Date 2
Date 3
...
and a table (Rates) with rates for some of the dates that are in the table AllDates:
Date Rate1 Rate2 Rate3 ...
-----------------------------------
Date1 r1d1 r2d1 r3d1 ...
Date3 r1d3 r2d3 r3d3 ...
...
I would like to create a query that would list the rates for all of the consecutive dates that are in the table AllDates (showing previous date's values for the dates that are not in the table Rates) like so:
Date Rate1 Rate2 Rate3 ... Rate etc.
----------------------------------------------
Date1 r1d1 r2d1 r3d1 ...
Date2 r1d1 r2d1 r3d1 ...
Date3 r1d3 r2d3 r3d3 ...
...
First, I created a query that maps the dates in table AllDates to the dates in table Rates:
SELECT AllDates.Date, (SELECT Max(Date) FROM Rates WHERE AllDates.Date - Rates.Date>-1) AS MappedDate FROM AllDates
it runs ok and produces the following output:
Date MappedDate
-----------------------
Date1 Date1
Date2 Date1
Date3 Date3
But when I try to join to this query Rates table to add the rates:
SELECT *
FROM (SELECT AllDates.Date, (SELECT Max(r1.Date) FROM Rates AS r1 WHERE AllDates.Date - r1.Date>-1) AS MappedDate FROM AllDates) AS d1 INNER JOIN Rates AS r2 ON d1.MappedDate = r2.Date;
Access shows the first rows of the output but the query keeps on running and doesn't get to the end...
I would appreciate if someone could suggest how could I get my desired output with a query.
For now I am outputting the dates map into a new table and joining it with the rates table but I would like to do it all with one query.
EDIT: I also tried stacked queries but got the same result - a continuously running query. At the same time, the query ran without a hitch on MS SQL Server.
But I finally got it working in Access by adding a TOP clause with an excessive value in the subquery:
SELECT *
FROM (SELECT TOP 50000 AllDates.Date, (SELECT Max(r1.Date) FROM Rates AS r1 WHERE AllDates.Date - r1.Date>-1) AS MappedDate FROM AllDates) AS d1 INNER JOIN Rates AS r2 ON d1.MappedDate = r2.Date;
viktors
I have an apparently simple problem which I haven't been able to solve to my liking.
I have a table (AllDates) with consecutive dates:
Date
-------
Date 1
Date 2
Date 3
...
and a table (Rates) with rates for some of the dates that are in the table AllDates:
Date Rate1 Rate2 Rate3 ...
-----------------------------------
Date1 r1d1 r2d1 r3d1 ...
Date3 r1d3 r2d3 r3d3 ...
...
I would like to create a query that would list the rates for all of the consecutive dates that are in the table AllDates (showing previous date's values for the dates that are not in the table Rates) like so:
Date Rate1 Rate2 Rate3 ... Rate etc.
----------------------------------------------
Date1 r1d1 r2d1 r3d1 ...
Date2 r1d1 r2d1 r3d1 ...
Date3 r1d3 r2d3 r3d3 ...
...
First, I created a query that maps the dates in table AllDates to the dates in table Rates:
SELECT AllDates.Date, (SELECT Max(Date) FROM Rates WHERE AllDates.Date - Rates.Date>-1) AS MappedDate FROM AllDates
it runs ok and produces the following output:
Date MappedDate
-----------------------
Date1 Date1
Date2 Date1
Date3 Date3
But when I try to join to this query Rates table to add the rates:
SELECT *
FROM (SELECT AllDates.Date, (SELECT Max(r1.Date) FROM Rates AS r1 WHERE AllDates.Date - r1.Date>-1) AS MappedDate FROM AllDates) AS d1 INNER JOIN Rates AS r2 ON d1.MappedDate = r2.Date;
Access shows the first rows of the output but the query keeps on running and doesn't get to the end...
I would appreciate if someone could suggest how could I get my desired output with a query.
For now I am outputting the dates map into a new table and joining it with the rates table but I would like to do it all with one query.
EDIT: I also tried stacked queries but got the same result - a continuously running query. At the same time, the query ran without a hitch on MS SQL Server.
But I finally got it working in Access by adding a TOP clause with an excessive value in the subquery:
SELECT *
FROM (SELECT TOP 50000 AllDates.Date, (SELECT Max(r1.Date) FROM Rates AS r1 WHERE AllDates.Date - r1.Date>-1) AS MappedDate FROM AllDates) AS d1 INNER JOIN Rates AS r2 ON d1.MappedDate = r2.Date;
viktors
Last edited:
