G
geert
Hello guys
This is my situation.
I have a database which serves me to output some new interest rate
propositions for clients (existing loans)
I have 3 tables (named KO6; KO7; KO8). Each of them contains 1 record.
Each table contains 3 fields (in these tables the field names are 5,
10, 15)
The value for each of the 3 fields in the table represents a (new)
interest rate.
Then I have different other tables which provide me existing loan
data, client identification etc.
Scope is to provide the client with a proposition for his existing
loan portfolio. So we have a report based on a query which contains
his loans, record by record, with data such as actual conditions and
also a new proposition (interest rate).
This new proposition gives him a new value (new interest rate) for
each KO (KO= knockout level, eg. @6, 7, 8 %; which means he wil get
those new interest rates as long as market rates are below 6, 7, 8%)
example: if a loan has a remaining duration of 6 years, for that loan
the value for the KO6 will be the value in table KO6 and more specific
this one under field 5. (5 stands for the new interest rate valid for
loans with duration between 5 and 10 years, 10 stands for the new
interest rate valid for loans with duration between 10 and 15 years,
15 stands for the new interest rate valid for loans with duration
between 15 and 20 years). In all cases there must be added a spread
(commission) towards each of these new interest rates.
I have this spread for each loan in a table. I made a query in which
the remaining duration for each loan is calculated. I determined also
the right interest by using iif function (in function of the
duration). I also added the spread to this new result. (I did this by
means of a query based on query) So I have for each loan 3 new values
which are based on the spread + the adequate field of each KO Table in
function of the remaining duration. So far no problem.
Now I've been asked to get a similar but different result. We had
those 3 time frames, 5-10, 10-15, 15-20 which gave different results.
What I want to get now is this: lets say for one client I have a
selection of 10 loans to be proposed. 5 loans fall under the 5-10
category, 4 loans under the 10-15 category and 1 loan has a remaining
duration of 18years, so it falls under the 15-20 category. In this
case all prices for each loan have to be the one from the 15-20 field.
So for each KO, it will be the values from the '20' field that will be
taken.
I cannot split the offer by category and solve this problem the way I
described before. It must be 1 proposition.
Generally spoken, when there are several loans and their durations are
not restricted to 1 and the same time frame, they all get the price
resorting under the highest time frame corresponging to the loan with
the longest duration.
Can somebody help me please.
I hope I made myself clear enough.
Thanks a lot!!
This is my situation.
I have a database which serves me to output some new interest rate
propositions for clients (existing loans)
I have 3 tables (named KO6; KO7; KO8). Each of them contains 1 record.
Each table contains 3 fields (in these tables the field names are 5,
10, 15)
The value for each of the 3 fields in the table represents a (new)
interest rate.
Then I have different other tables which provide me existing loan
data, client identification etc.
Scope is to provide the client with a proposition for his existing
loan portfolio. So we have a report based on a query which contains
his loans, record by record, with data such as actual conditions and
also a new proposition (interest rate).
This new proposition gives him a new value (new interest rate) for
each KO (KO= knockout level, eg. @6, 7, 8 %; which means he wil get
those new interest rates as long as market rates are below 6, 7, 8%)
example: if a loan has a remaining duration of 6 years, for that loan
the value for the KO6 will be the value in table KO6 and more specific
this one under field 5. (5 stands for the new interest rate valid for
loans with duration between 5 and 10 years, 10 stands for the new
interest rate valid for loans with duration between 10 and 15 years,
15 stands for the new interest rate valid for loans with duration
between 15 and 20 years). In all cases there must be added a spread
(commission) towards each of these new interest rates.
I have this spread for each loan in a table. I made a query in which
the remaining duration for each loan is calculated. I determined also
the right interest by using iif function (in function of the
duration). I also added the spread to this new result. (I did this by
means of a query based on query) So I have for each loan 3 new values
which are based on the spread + the adequate field of each KO Table in
function of the remaining duration. So far no problem.
Now I've been asked to get a similar but different result. We had
those 3 time frames, 5-10, 10-15, 15-20 which gave different results.
What I want to get now is this: lets say for one client I have a
selection of 10 loans to be proposed. 5 loans fall under the 5-10
category, 4 loans under the 10-15 category and 1 loan has a remaining
duration of 18years, so it falls under the 15-20 category. In this
case all prices for each loan have to be the one from the 15-20 field.
So for each KO, it will be the values from the '20' field that will be
taken.
I cannot split the offer by category and solve this problem the way I
described before. It must be 1 proposition.
Generally spoken, when there are several loans and their durations are
not restricted to 1 and the same time frame, they all get the price
resorting under the highest time frame corresponging to the loan with
the longest duration.
Can somebody help me please.
I hope I made myself clear enough.
Thanks a lot!!