lookup actual exchange rate

[Guest]

Hi I have two tables:

tbExchangeRates
---------------------
erCurID 1 1
erValidFrom 01-JUN-2007 01-JUL-2007
erRate 1.44 1.55

tbDeliveries
-------------------
deDate 15-JUN-2007 02-JUL-2007
deCurrency 1 1


Querry should lookup the latest valid exchange rate based on delivery date
(tbDeliveries.deDate).

my qeurry is following:
---------------------------
SELECT tbDeliveries.deDate,
(SELECT TOP 1 tbExchangeRates.erRate
FROM tbExchangeRates, tbDeliveries
WHERE ((tbExchangeRates.erValidFrom<=bDeliveries.deDate)
AND tbExchangeRates.erCurID= tbDeliveries.deCurrency))

ORDER BY tbExchangeRates.erValidFrom DESC ) AS FX_RATE
FROM tbDeliveries;


Result from the querry is:
------------------------------
deDate FX_RATE
15-JUN-2007 1.55
02-JUL-2007 1.55


I would expect result like
---------------------------------
deDate FX_RATE
15-JUN-2007 1.44
02-JUL-2007 1.55

any idea what I did wrong

thank you for your help

 

[John Spencer]

I believe that you need an Order By clause in your sub-query. The Order by
will determine which record is returned as the Top 1 record. I think that
the following may be what you want.

SELECT tbDeliveries.deDate,
(SELECT TOP 1 tbExchangeRates.erRate
FROM tbExchangeRates, tbDeliveries
WHERE tbExchangeRates.erValidFrom<=bDeliveries.deDate
AND tbExchangeRates.erCurID= tbDeliveries.deCurrency
ORDER BY erValidFrom)

ORDER BY tbExchangeRates.erValidFrom DESC ) AS FX_RATE
FROM tbDeliveries;


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Hi John,
it does not seem to be working.

1. Your statement has a syntax error ORDER BY....FROM I think it should be
FROM...ORDER BY

IWhen I post my original querry I used confusing indent - what likely
confused you -as the original querry included prompt for sorting the output.

I have corrected your suggested statement:

SELECT tbDeliveries.deDate,
(SELECT TOP 1 tbExchangeRates.erRate
FROM tbExchangeRates, tbDeliveries
WHERE tbExchangeRates.erValidFrom < tbDeliveries.deDate

AND tbExchangeRates.erCurID= tbDeliveries.deCurrency

ORDER BY tbExchangeRates.erValidFrom DESC ) AS FXRATE
FROM tbDeliveries;

it returns still the same result for all lines pulls out exchange rate
from the latest date
- it seems to me like the condition
"WHERE tbExchangeRates.erValidFrom < tbDeliveries.deDate"
is being ignored.



Supicek

 

[John Spencer]

Since you want the earliest exchange rate where the ValidFrom date is less
than or equal to the delivery date I would try an ASCending Order By.

SELECT tbDeliveries.deDate,
(SELECT TOP 1 tbExchangeRates.erRate
FROM tbExchangeRates, tbDeliveries
WHERE tbExchangeRates.erValidFrom <= tbDeliveries.deDate
AND tbExchangeRates.erCurID= tbDeliveries.deCurrency
ORDER BY tbExchangeRates.erValidFrom ASC) AS FXRATE
FROM tbDeliveries;

My apology for the typo, not enough (or too much) coffee in my system. When
I review your original posting I see that I misread your query.

You might also try Allen Browne's code
extended replacement for DLookup():
http://allenbrowne.com/ser-42.html
This ELookup() function allows you to specify the sort order of the records,
so you can indicate which one you want returned.

 

[Guest]

No I have already tried ASC order but it pops up error message:
"At most one record can be return by this subquerry" - which I do not
understand what this should mean....

neverheless I think DESC order is right. My feeling is that
tbDeliveries.deDate in the subquerry is not updated for each record. It looks
like the value is filled by the very first record and then it remains the
same for all tbDeliveries records.

But I have no clue how to force the querry to update the value - might be
the querry is construct in wrong way.

I would prefer to make the calculation via sql as I would be limited further
in the projenct with subforms. I want to use the output in list boxes.

Supicek

 

[Gary Walter]

PMFJI

a subquery (in SELECT clause)
must return only one record

is it possible there are ties for the "TOP"
(just because you say "TOP 1" does not
necessarily mean there will be only "1" record)

one way to check would be to add DISTINCT
to your subquery

SELECT tbDeliveries.deDate,
(SELECT DISTINCT TOP 1 tbExchangeRates.erRate

 

[Guest]

How about:

SELECT D.deCurrency, D.deDate,
(SELECT TOP 1 tbExchangeRates.erRate
FROM tbExchangeRates
WHERE tbExchangeRates.erValidFrom<= D.deDate
AND tbExchangeRates.erCurID= D.deCurrency
ORDER BY erValidFrom DESC) as FX_Rate
FROM tbDeliveries D;
ORDER BY D.deCurrency, D.deDate


Dale

 

[John Spencer]

Access is a bear on this. It just says there is the possibility of returning
more than 1 record so it won't run the query at all.

I forgot that when I was giving advice.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks Gary, but this did not help neither. A lause DISTINCT is in cotradict
with ORDER BY, but even if I remove the clause ORDER BY the querry still
returns the same values for all records.

Pavel

 

[Guest]

thank you Dale,
I was already hesitating if this can be done by sql querry, but you got it.
the issue was in subquerry I should not repeat tbDeliveries in the subquerry.

THANK YOU VERY MUCH
Supicek

 

[John Spencer]

Your subquery has a cartesian join in it. SO that may be one problem.

=========================================
FROM tbExchangeRates, tbDeliveries
This combines every exchange rate with every delivery record
=========================================

This might work, but I suspect it will fail in at least some versions of
Access.

SELECT tbDeliveries.deDate,
(SELECT TOP 1 tbExchangeRates.erRate
FROM tbExchangeRates
WHERE tbExchangeRates.erValidFrom <= tbDeliveries.deDate
AND tbExchangeRates.erCurID= tbDeliveries.deCurrency
ORDER BY tbExchangeRates.erValidFrom ASC) AS FXRATE
FROM tbDeliveries;

This is more convoluted but probably will work (it will also probably be
slow)
SELECT deDate
, (SELECT erRate
FROM tbExchangeRates
WHERE erValidFrom = (
SELECT Min(erValidFrom) as FirstDate
FROM tbExchangeRates
WHERE erValidFrom<=tblDeliveries.deDate
AND erCurID= tblDeliveries.deCurrency)
AND erCurID = tblDeliveries.deCurrency) as FxRate
FROM TblDeliveries


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..