intervall in query

[ChristopherL]

Hi!

I'm having an issue with my query that I can't even figure out where to start with!

Here is an example how my tables looks like.

dbo.Murex

[Instrument] [Duration] [Volatily]
OMX2 4 0.18
OMX3 9 0.17
CMD1 2 0.16
NAS1 5 0.14

and a second table

dbo.DurationLength

[InstrumentLengthD] [Volatily]
OMX3M 0.19
OMX5M 0.17
CMD3M 0.17
NAS6M 0.15


(Where the value and M after OMX is Duration and Month, so OMX 3 Months)
What I want to do is the following:

I want the volatility in dbo.Murex to change depending on what length is closest to it's duration. So take OMX2 for example who have a duration of 4, the closest InstrumentLengthD to that is OMX3M, which has a volatility at 0.19. Then I want it to show 0.19 in the query for OMX2.

So I want it to pick the volatility for the closest InstrumentLengthD.

I am guessing that it will be multiple iif statements? I've never done several iif statements in a query before so would love some help with that!

//
Sorry for long post..
Chris

 

[plog]

First, OMX3M isn't the record with the closest Volatily value to OMX2--its one of the closest records. It's a 3-way tie between OMX3M, OMX5M and CMD3M. They are all off by .1 from the volatily of OMX2. Does the larger value always get taken when there is a tie?

Second, assuming the larger value is the correct one, the most efficient way to achieve this is with a sub-query to get unique Volatily values in dbo.DurationLength:

SELECT Volatily FROM dbo.DurationLength GROUP BY Volatily;

For example's sake, call that query 'VolatilyValues'. Next create a query based on dbo.Murex and use a DMin() (http://www.techonthenet.com/access/functions/domain/dmin.php) into VolatilyValues:

SELECT Instrument, Duration, DMin("[Volatily]", "VolatilyValues", "[Volatily]<=" & [Volatily]) AS ClosestVolatily FROM dbo.Murex;

That will provide you with what you want.

 

[ChristopherL]

First, OMX3M isn't the record with the closest Volatily value to OMX2--its one of the closest records. It's a 3-way tie between OMX3M, OMX5M and CMD3M. They are all off by .1 from the volatily of OMX2. Does the larger value always get taken when there is a tie?

Second, assuming the larger value is the correct one, the most efficient way to achieve this is with a sub-query to get unique Volatily values in dbo.DurationLength:

SELECT Volatily FROM dbo.DurationLength GROUP BY Volatily;

For example's sake, call that query 'VolatilyValues'. Next create a query based on dbo.Murex and use a DMin() (http://www.techonthenet.com/access/functions/domain/dmin.php) into VolatilyValues:

SELECT Instrument, Duration, DMin("[Volatily]", "VolatilyValues", "[Volatily]<=" & [Volatily]) AS ClosestVolatily FROM dbo.Murex;

That will provide you with what you want.

Thank you, will definetly try this!

 

[Brianwarnock]

I agree with plog about querying your selection of OMX3M , my first reaction is that you are trying to emulate Excel's VLOOKUP, if so you might be interested in this

http://www.actuarialoutpost.com/actuarial_discussion_forum/archive/index.php/t-204968.html

Brian