working with calculated field based upon different tables

[geert.van.ransbeeck]

Hello

I have 2 tables on which I would base a query.
In this query I would create a calculated field based on both fields
fields from both tables.
Example: lets say in Table A I have 3 records with 3 fields. Field 1
contains values as '1-5', '5-10', '10-15'. Field 2 contains the
corresponding field values '5', '10' and '15' and field 3 contains the
values '4', '5' and '6'.
In table B I have 3 records with 2 fields, the field values for the
first field are '4', '9' and '12'. The second field contains the
values '1', '2', '3'.
Now I make a query in whivh I create a calculated field: If the value
of field 1 in Table B is between 1 and 5 (field 1 in Table A (1-5)),
so add the corresponding field 2 for this record in Table A to the
corresponding first field in table 2.

So if I have the value 9 in table B (in field 1), this lies between
5-10, so in this case add 2 to 10, with a result of 12 in my new
calculated field.
Can anybody help me please?

 

[sq75222]

your can use the IIF statement

 

[Marshall Barton]

I have 2 tables on which I would base a query.
In this query I would create a calculated field based on both fields
fields from both tables.
Example: lets say in Table A I have 3 records with 3 fields. Field 1
contains values as '1-5', '5-10', '10-15'. Field 2 contains the
corresponding field values '5', '10' and '15' and field 3 contains the
values '4', '5' and '6'.
In table B I have 3 records with 2 fields, the field values for the
first field are '4', '9' and '12'. The second field contains the
values '1', '2', '3'.
Now I make a query in whivh I create a calculated field: If the value
of field 1 in Table B is between 1 and 5 (field 1 in Table A (1-5)),
so add the corresponding field 2 for this record in Table A to the
corresponding first field in table 2.

So if I have the value 9 in table B (in field 1), this lies between
5-10, so in this case add 2 to 10, with a result of 12 in my new
calculated field.

Your TableA field 1 has one value that is a text string. If
you intended to use it as two values, it would be a
violation of the rules of relational database normalization
(google it if you don't know what that means). Assuming
it's no big deal to change tableA to have two integer fields
instead of one Text field, the records would look like:
f1 f2 f3 f4
1 5 5 4
6 10 10 5
11 15 15 6

I guess that it's just a coincidence that f2 and f3 are the
same. I changed the start of range value so the ranges
would not overlap.

Then you could try this kind of thing:

SELECT TableA.*, TableB.*,
TableA.field3 + TableB.field2 As newfield
FROM TableA INNER JOIN TableB
ON TableB.field1 >= TableA.field1
And TableB.field1 <= TableA.field2

Note: this kind of Join can not be done in the query
designer, it must be done in SQL view.

 

[geert.van.ransbeeck]

Date Credit Term Term Structure KO 5,5% KO 6% KO 6,5%
9/04/2009 < 5 years 0 0,00 0 0
9/04/2009 5-10 years 5 2,55 2,58 2,6
9/04/2009 10-15 years 10 3,03 3,1 3,16
9/04/2009 15-20 years 15 3,24 3,36 3,44
9/04/2009 20-25 years 20 3,34 3,48 3,59
9/04/2009 25-30 years 25 3,37 3,51 3,63


Hereby a copy of my table. What I want to achieve is a kind of
'Vlookup in excel'.
This table, table A, gives me the prices for loans in function of the
remaining time to maturity.
So If one of my loans, which are stocked in table B is maturing within
8 years (matching the credit term 5-10) for this loan we have a term
structure of 5. So I want to have as a result of the query that for
this loan I have for the KO 5,5% the rate of 2.55 + a margin which I
get from my table B, for the KO 6% the rate of 2.58 + a margin which I
get from my table B and for the KO 6,5% the rate of 2.6 + a margin
which I get also from my table B. Ans this reasonig forevery loan of
table B.

I hope this is clear enough. How do I proceed?
Thanks in advance
geert

 

[Marshall Barton]

Date Credit Term Term Structure KO 5,5% KO 6% KO 6,5%
9/04/2009 < 5 years 0 0,00 0 0
9/04/2009 5-10 years 5 2,55 2,58 2,6
9/04/2009 10-15 years 10 3,03 3,1 3,16
9/04/2009 15-20 years 15 3,24 3,36 3,44
9/04/2009 20-25 years 20 3,34 3,48 3,59
9/04/2009 25-30 years 25 3,37 3,51 3,63


Hereby a copy of my table. What I want to achieve is a kind of
'Vlookup in excel'.
This table, table A, gives me the prices for loans in function of the
remaining time to maturity.
So If one of my loans, which are stocked in table B is maturing within
8 years (matching the credit term 5-10) for this loan we have a term
structure of 5. So I want to have as a result of the query that for
this loan I have for the KO 5,5% the rate of 2.55 + a margin which I
get from my table B, for the KO 6% the rate of 2.58 + a margin which I
get from my table B and for the KO 6,5% the rate of 2.6 + a margin
which I get also from my table B. Ans this reasonig forevery loan of
table B.

I hope this is clear enough. How do I proceed?

Well, it's clear enough, but it looks like a spreadsheet.
That kind of table structure will not work in a database
without having all kinds of complications. Apparently, you
neither understood what I said about relational database
normalization, nor did the homework to figure it out. What
you have just plain will not work. In addition to my
previous comments about the Credit Term field, you also have
several KO fields, which look like a violation of another
rule of normalization.

I really don't know how to do what you want using that
table. I probably could figure out some kind of a complex
mess to do it, but the complexities will continue to pile up
until you run into a brick wall and throw the whole thing
away. If the table(s) were normalized then all this would
be fairly routine and you could proceed along the lines I
posted earlier.