OliWatkins
New member
- Local time
- Today, 13:13
- Joined
- Feb 14, 2020
- Messages
- 3
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.
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.