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

OliWatkins

New member
Local time
Today, 20:25
Joined
Feb 14, 2020
Messages
2
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

I’m here to help
Staff member
Local time
Today, 04:25
Joined
Oct 29, 2018
Messages
14,071
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

Super Moderator
Staff member
Local time
Today, 12:25
Joined
Feb 19, 2013
Messages
12,897
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

Code:
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

New member
Local time
Today, 20:25
Joined
Feb 14, 2020
Messages
2
Thanks for the welcome DBguy and CJ.

CJ- works like a dream. Thank you!
 

Users who are viewing this thread

Top Bottom