Query SELECT in SELECT?

[supicek]

Hi I would appriciate if somebody can help me with the following query:

tbDeliveryLines
- dlID
- dlDate
- dlCurrencyID
- dlQTY
- dlUnitPrice

tbExchangeRates
- erID
- erCurrencyID
- erValidFrom
- erFXRate

I would like to build a query which would return all my delivery lines +
lookup a valid FX rate from tbExchangeRates for a entered curreny
(dlCurrencyID) and date (dlDate)

Example:

tbExchangeRates

erID, erCurrencyID, erValidFrom, erFXRate
1, 1, 01-JAN-2010, 31
2, 1, 01-FEB-2010, 32
3, 1, 01-MAR-2010, 33
4, 2, 01-MAR-2010, 50

Query should return:
dlDate, dlCurrencyID, erFXRate
15-JAN-2010, 1, 31
10-JUL-2010, 1, 33
10-JUL-2010, 2, 50

I think I should use SELECT in SELECT statement, but I am a bit lost in
this.Thank you for your help
supicek

 

[XPS350]

Hi I would appriciate if somebody can help me with the following query:

tbDeliveryLines
- dlID
- dlDate
- dlCurrencyID
- dlQTY
- dlUnitPrice

tbExchangeRates
- erID
- erCurrencyID
- erValidFrom
- erFXRate

I would like to build a query which would return all my delivery lines +
lookup a valid FX rate from tbExchangeRates for a entered curreny
(dlCurrencyID) and date (dlDate)

Example:

tbExchangeRates

erID, erCurrencyID, erValidFrom, erFXRate
1, 1, 01-JAN-2010, 31
2, 1, 01-FEB-2010, 32
3, 1, 01-MAR-2010, 33
4, 2, 01-MAR-2010, 50

Query should return:
dlDate, dlCurrencyID, erFXRate
15-JAN-2010, 1, 31
10-JUL-2010, 1, 33
10-JUL-2010, 2, 50

I think I should use SELECT in SELECT statement, but I am a bit lost in
this.Thank you for your help
supicek

I think you could make a function to find the right rate. Something
like:

Function RateOnDate(CurrencyCode As Long, CurrDate As Date) As Variant
Dim tDate As Date

tDate = Format(CurrDate, "mm-dd-yyyy")

RateOnDate = DLookup("erFXRate", "tbExchangeRates", "erCurrencyID=" &
CurrencyCode & " AND erValidFrom=#" & Format(DMax("erValidFrom",
"tbExchangeRates", "erCurrencyID=" & CurrencyCode & " AND erValidFrom
<=#" & tDate & "#"), "mm-dd-yyyy") & "#")

End Function

In your query you use this function:

SELECT *, RateOnDate([dlCurrencyID],[dlDate]) AS Rate FROM
tbDeliveryLines


Groeten,

Peter
http://access.xps350.com

 

[Roger Carlson]

You need to Join your tables on dlCurrencyID=erCurrencyID.

If you're unfamiliar with Joining tables, take a look a this blog post:
http://rogersaccessblog.blogspot.com/2009/08/what-is-join-part-1-introduction.html
and the following 4 related posts for a good introduction.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L