Find a value based on two fields: one matching, and one closest (d

[Matthew]

Hi,

I'm trying to find a value in table F (Foreign Exchange Rate) that is the
closest to the date I purchased a product specified in table P, so that I can
convert the purchase price into US $ from a foreign currency

So here's a simplified table structures:
F
Key Currency FDate ExchangeRate
1 RMP 1/1/2008 1.111
2 RMP 1/3/2008 1.122
3 CAD 1/1/2008 0.999
4 CAD 1/2/2008 0.998

P
Key Currency PDate PriceLocalCurrency
1 RMP 1/1/2008 10.00
2 RMP 1/2/2008 11.00

Finding the correct value (ExchangeRate) in Table F for the first record
(Key=1) in Table P is trivial. But how do I find ExchangeRate in Table F for
the second record (e.g., where the dates in the two tables don't match).

I'm okay matching on either the previous date (1/1/2008 in this case) or the
next date (1/3/2008).

Thanks,

Matthew

 

[Jeff Boyce]

Matthew

You'll first need to decide how you are determining "closest".

Mathematically, the "closest" uses the ABS function (absolute value) to
determine the shortest distance between two points.

But when you are working with "trigger dates" (e.g., "on/after this date,
that happens"), you may need the "next" date after your date.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Matthew]

Jeff,

I have enough data, that the biggest gap is about 3 days between data points
in Table F, so I'm ok using either the next date or the prior date -- I just
want to be consistent.

Does this help?

Matthew

 

[KARL DEWEY]

This should start you off --
Rates --
Curr RateDate Rate Low High
EUR 1/1/2006 1.41 10 15
USD 1/1/2006 1.8 16 20
USD 3/1/2006 1.81 21 30
USD 4/1/2006 1.82 31 45
USD 5/1/2006 1.83 46 99
Txns --
Refce TxnDate Curr Amount
10 3/21/2006 USD 10.87
11 4/10/2006 USD 20

TxnsRateDate --
SELECT Txns.Refce, Txns.TxnDate, Txns.Curr, Txns.Amount, Max(Rates.RateDate)
AS MaxOfRateDate
FROM Txns INNER JOIN Rates ON Txns.Curr = Rates.Curr
WHERE (((Rates.RateDate)<=[TxnDate]))
GROUP BY Txns.Refce, Txns.TxnDate, Txns.Curr, Txns.Amount;

TxnsDateRates --
SELECT TxnsRateDate.Refce, TxnsRateDate.TxnDate, TxnsRateDate.Curr,
TxnsRateDate.Amount, Rates.Rate
FROM Rates INNER JOIN TxnsRateDate ON (Rates.Curr = TxnsRateDate.Curr) AND
(Rates.RateDate = TxnsRateDate.MaxOfRateDate);

 

[Matthew]

Karl,

Thank you very much.

Your queries worked perfectly.

Cheers,

Matthew

This should start you off --
Rates --
Curr RateDate Rate Low High
EUR 1/1/2006 1.41 10 15
USD 1/1/2006 1.8 16 20
USD 3/1/2006 1.81 21 30
USD 4/1/2006 1.82 31 45
USD 5/1/2006 1.83 46 99
Txns --
Refce TxnDate Curr Amount
10 3/21/2006 USD 10.87
11 4/10/2006 USD 20

TxnsRateDate --
SELECT Txns.Refce, Txns.TxnDate, Txns.Curr, Txns.Amount, Max(Rates.RateDate)
AS MaxOfRateDate
FROM Txns INNER JOIN Rates ON Txns.Curr = Rates.Curr
WHERE (((Rates.RateDate)<=[TxnDate]))
GROUP BY Txns.Refce, Txns.TxnDate, Txns.Curr, Txns.Amount;

TxnsDateRates --
SELECT TxnsRateDate.Refce, TxnsRateDate.TxnDate, TxnsRateDate.Curr,
TxnsRateDate.Amount, Rates.Rate
FROM Rates INNER JOIN TxnsRateDate ON (Rates.Curr = TxnsRateDate.Curr) AND
(Rates.RateDate = TxnsRateDate.MaxOfRateDate);

--
KARL DEWEY
Build a little - Test a little

Hi,

I'm trying to find a value in table F (Foreign Exchange Rate) that is the
closest to the date I purchased a product specified in table P, so that I can
convert the purchase price into US $ from a foreign currency

So here's a simplified table structures:
F
Key Currency FDate ExchangeRate
1 RMP 1/1/2008 1.111
2 RMP 1/3/2008 1.122
3 CAD 1/1/2008 0.999
4 CAD 1/2/2008 0.998

P
Key Currency PDate PriceLocalCurrency
1 RMP 1/1/2008 10.00
2 RMP 1/2/2008 11.00

Finding the correct value (ExchangeRate) in Table F for the first record
(Key=1) in Table P is trivial. But how do I find ExchangeRate in Table F for
the second record (e.g., where the dates in the two tables don't match).

I'm okay matching on either the previous date (1/1/2008 in this case) or the
next date (1/3/2008).

Thanks,

Matthew