Date Range Query

[GJT]

I have these two tables in a 1:M relationship.
One called CURRENCY and the other CURRENCY_HISTORY containing the exchange rates and the dates on which the revised rates are added to the table, respectively.

Problem I have is this :
For 'n' orders during the year, the exchange rates change (obv) . I need to write a query to return the appropriate GBP order value for all orders in that year in accordance with their exchange rate values. The corresponding rate that is used for each order will depend of course upon the CURRENCY ID (currency type) and the order date.

i.e.
USD rates in CURRENCY_HISTORY
10/1/2002 = 1.5
22/4/2002 = 1.6
16/10/2002 = 1.7
18/12/2002 = 1.8

if the order date was 10/5/2002, the USD rate to use would be 1.6 i.e between 22/4/2002 & 16/10/2002.

Does anyone know if this can be done relatively easily??

Thanks

 

[Jon K]

Assuming the Currency_History table contains the three fields:-
Currency ID, Date, Rate

and the Orders table contains the two fields:-
Order Date, Currency ID


based on the Currency_History table, I would use a query to return the fields:-
Currency ID, StartDate, EndDate, Rate

qryOne:-
SELECT [Currency ID], [Date] AS StartDate,
IIf(IsNull((Select Min([Date]) from Currency_History as S where [Currency ID]=Currency_History.[Currency ID] and [Date] > Currency_History.[Date])), Date(), (Select Min([Date]) from Currency_History as S where [Currency ID] = Currency_History.[Currency ID] and [Date] > Currency_History.[Date])-1) AS EndDate,
[Rate]
FROM Currency_History;

(In the query I have used today's date as the EndDate of the last history record for each Currency ID.)


I would then join the Orders table with the above query in a new query (based on Currency ID=Currency ID AND Order Date Between StartDate And EndDate) to return the required Rate.

qryTwo:-
SELECT [Orders].[Order Date], [Orders].[Currency ID], [qryOne].[Rate]
FROM Orders INNER JOIN qryOne ON ([Orders].[Currency ID]=[qryOne].[Currency ID]) AND ([Orders].[Order Date] Between [qryOne].[StartDate] And [qryOne].[EndDate]);


When qryTwo is run, it should return:-
Order Date, Currency ID, Rate
10/5/2002, USD, 1.6

Instead of using the query grid to build the queries, it would be easier to directly type/paste each Select statement in the SQL View of a new query.

Hope the queries help.

Note:
If the Currency_History table contains many records, running the queries may take time. One way to speed up performance is to use a Make-Table query to create a Temporary Currency History table to hold only records of the required Currency IDs and Dates. And build the query qryOne against this temporary history table instead of the Currency_History table.

 

[GJT]

Thanks for the info - looks good, however you have used todays date for the final day of 'change' period.

What I will need to generate is : the day before the proceeding exchange rate for each currency, as the EndDate. How would you do this?

 

[Jon K]

I should have made it clearer. By "today's date as the EndDate of the last history record for each Currency ID", I mean using the Date() function, which returns the day on which the query is run.

 

[GJT]

Jon

I have tried in vain to get this query to work. The resulting data set contains the currnecy information but each end date is the current system date eg. date on which the query is run. This of course is no good, I need the following :
START END RATE
USD 10/2/2002 15/2/2002 1.62
USD 16/2/2002 29/2/2002 1.65

what I am actually getting is :
START END RATE
USD 10/2/2002 10/2/2003 1.62
USD 16/2/2002 10/2/2003 1.65

To get this working is mission critical and may possibly mean I am going to have to revamp a large part of the accounting section of the database.

Thanks for your valued input.

 

[Jon K]

As stated in my post, the Date() function is used only for the last history record of a Currency ID.


In the attached DB, I copied the query statements from my post to two queries and put your two dates in the Currencry_History table.

When I ran the query qryOne, I found that the date ranges returned were correct (my system uses the US format). The EndDate returned for the first date range was 2/15/2002, not 2/10/2003 as you stated:-

Currency ID -- StartDate --- EndDate ------ Rate
USD ---------- 2/10/2002 -- 2/15/2002 -- 1.62
USD ---------- 2/16/2002 -- 2/10/2003 ---- 1.65


You can try the query qryOne in the attached DB to see if it works on your system.


Note: The attached DB is in Access 97 format. If you use Access 2000 or 2002, choose Convert and save as a new name.

 

[GJT]

Jon

Thanks for the info and the sample DB you have posted. I have now managed to get this query working as requested.
(Looks like its been of interest to a number of others as well!)

Thanks again - much appreciated.

GJT