A
Andy Smith
I'm trying to track the shapes (number of dimensions) and values of variables
called "assumptions" to be fed into cash flow and credit models. I have an
"Assumption Master" table of all assumptions that tells me whether they have
one, two or three dimensions, and a "Dimension Lists" table telling me what
each dimension could be.
For example, an assumption called "Product Lien Factor" has two dimensions
called "Lien Position" and "Rating": Lien Position could be 1 or 2, and
Rating could be AAA, AA+, AA, AA-, A+, A, A-, BBB+, BBB, BBB-, BB+, BB, BB-,
CCC, CC, C, D or NR. But another assumption, "Default Curve" has three
dimensions, two dates and the period, an integer 0-90.
So in Assumption Master I have this (I'm writing it here in CSV with headers):
AName, Dim1, Dim2, Dim3
Product Lien Factor, Lien Position, Rating, (null)
Default Curve, Start Date, End Date, Period
And in Dimension Lists I have (same format):
DName, Choice
Lien Position, 1
Lien Position, 2
Rating, AAA
Rating, AA+
Rating, AA
...
Rating, D
Rating, NR
Period, 0
Period, 1
...
Period, 90
Note that there are no entries for Start Date or End Date.
Now I have "Assumption Data", which holds all data for all assumptions in
the structure Name,Dim1,Dim2,Dim3,Value, and I'm trying to use combo boxes
whenever I can. So when I choose "Product Lien Factor" and tab over to Dim1,
I want a combo that shows *only* 1 or 2, the two records in Dimension Lists
corresponding to Dim1 of Product Lien Factors. Likewise, when I tab to Dim2,
I want *only* ratings (AAA...NR) to show up.
Similarly, when I choose "Default Curve" and tab to Dim1 or Dim2 (start date
and end date), I want *nothing* to appear, because there are no entries in
Dimension Lists for those dimensions, but in Dim3 I want *only* the list 0-90.
What it boils down to is this: in the design of "Assumption Master" I have
SQL queries defined in the Lookup pages for fields Dim1-3 which determine
what shows up in their combo boxes -- the queries join Assumption Master's
Dim1, 2 or 3 field with Dimension Lists's DName field. But I need to specify
a filter on Assumption Name based on the name I just entered: "Product Lien
Factor" or "Default Curve", which isn't yet stored, because I'm in the middle
of data entry. Can this be done?
called "assumptions" to be fed into cash flow and credit models. I have an
"Assumption Master" table of all assumptions that tells me whether they have
one, two or three dimensions, and a "Dimension Lists" table telling me what
each dimension could be.
For example, an assumption called "Product Lien Factor" has two dimensions
called "Lien Position" and "Rating": Lien Position could be 1 or 2, and
Rating could be AAA, AA+, AA, AA-, A+, A, A-, BBB+, BBB, BBB-, BB+, BB, BB-,
CCC, CC, C, D or NR. But another assumption, "Default Curve" has three
dimensions, two dates and the period, an integer 0-90.
So in Assumption Master I have this (I'm writing it here in CSV with headers):
AName, Dim1, Dim2, Dim3
Product Lien Factor, Lien Position, Rating, (null)
Default Curve, Start Date, End Date, Period
And in Dimension Lists I have (same format):
DName, Choice
Lien Position, 1
Lien Position, 2
Rating, AAA
Rating, AA+
Rating, AA
...
Rating, D
Rating, NR
Period, 0
Period, 1
...
Period, 90
Note that there are no entries for Start Date or End Date.
Now I have "Assumption Data", which holds all data for all assumptions in
the structure Name,Dim1,Dim2,Dim3,Value, and I'm trying to use combo boxes
whenever I can. So when I choose "Product Lien Factor" and tab over to Dim1,
I want a combo that shows *only* 1 or 2, the two records in Dimension Lists
corresponding to Dim1 of Product Lien Factors. Likewise, when I tab to Dim2,
I want *only* ratings (AAA...NR) to show up.
Similarly, when I choose "Default Curve" and tab to Dim1 or Dim2 (start date
and end date), I want *nothing* to appear, because there are no entries in
Dimension Lists for those dimensions, but in Dim3 I want *only* the list 0-90.
What it boils down to is this: in the design of "Assumption Master" I have
SQL queries defined in the Lookup pages for fields Dim1-3 which determine
what shows up in their combo boxes -- the queries join Assumption Master's
Dim1, 2 or 3 field with Dimension Lists's DName field. But I need to specify
a filter on Assumption Name based on the name I just entered: "Product Lien
Factor" or "Default Curve", which isn't yet stored, because I'm in the middle
of data entry. Can this be done?