Solved select record on a date between dates in another table (1 Viewer)

[OliWatkins]

Hi everyone, new here. Wondered if you can help please?

I have two tables- [exchangerates] and [custdates]

What I would like to find is the Rate applicable for each custdateid on their startdate. There may be more than one, but should not be more than one of each currency.

in [exchangerates] there are the following fields:

ID
Currency
Rate
DateTimeEntered

in [custdates] there are numerous fields, but the relevant ones for this are:

custdateid
startdate

I have created a subquery for me to calculate a DateTimeEnd for each row in the [exchangerates] table, which works and looks great using info from googling, but how do I find the rate(s) applicable on the startdate for each custdateid? When I try and add even a simple date criteria to the calculated datetimeend field, access crashes.

Here is my sql:

SELECT T.ID, T.xcurrency, T.datetimeentered, T.DateTimeEnd, T.Rate

FROM (SELECT ID,

xcurrency,

datetimeentered,

rate,

( SELECT MIN(Datetimeentered)

FROM exchangerates T2

WHERE T2.xcurrency = T1.xcurrency

AND T2.datetimeentered > T1.datetimeentered

) AS DateTimeEnd

FROM exchangerates T1

) AS T

WHERE (((T.datetimeentered)<[startdate] AND ((T.DateTimeEnd)>[startdate]))

ORDER BY T.xcurrency, T.datetimeentered;


Thank you! any help or advice is much appreciated.

 

[theDBguy]

Hi. Welcome to AWF!

It might be easier if you could post a sample database with some test data. Would that be possible?

 

[CJ_London]

always helps to provide some example data but sounds like you want for each customer start date you want by currency and rate
where the datetimented is less than and nearest the startdate i.e. the max datetimeentered where it is less than start time.

Perhaps something like this

SELECT custdates.PK,custdates.custName, custdates.startdate, ExchangeRates. xcurrency , ExchangeRates.datetimeentered, ExchangeRates.Rate
FROM custdates, ExchangeRates, (SELECT DISTINCT  xcurrency FROM ExchangeRates)  AS C
WHERE ExchangeRates.datetimeentered=(SELECT Max(datetimeentered) FROM ExchangeRates WHERE datetimeentered<startdate and xcurrency =c. xcurrency)

 

[OliWatkins]

Thanks for the welcome DBguy and CJ.

CJ- works like a dream. Thank you!