Query SELECT in SELECT?
in [Queries]
|
Prev: update a field by inputting data in a different table
Next: Creating a sum to include a particular day plus the previous 7 days
From: supicek on 21 Jan 2010 06:39 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
From: XPS350 on 21 Jan 2010 08:03
On 21 jan, 12:39, supicek <supi...(a)discussions.microsoft.com> wrote: > 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 |