Select Query Parameter: How to Look Up a FieldName that is equal to a Form Entry?

[JBBurke]

I am working with an Insurance Mortality Table that uses specific Ages as Column Headings. To read the table, one uses these columns to peg an individual's current age and then cross-references a long list of statistical factors in the column beneath that correspond to his/her chance of reaching any date in the future up to age 100. Here is a small sample:

X-Age	Male-Mo	MoFactors	30	31
30	1	0.000	1.00000000000000000000
30	2	0.083	0.99988345681234000000
30	3	0.167	0.99976691362468100000
30	4	0.250	0.99965037043702100000

So, If I'm 30 years old, my chance of reaching month #1 of my 30th year is 100%, and as the months go by, my chance of living to that time falls by the decimal factors shown. The table extends far enough to the right to accommodate individuals up to age 76, and it goes downwards to show the chance of reaching any age up to 100 years, 0 Months.

I've created a select queary that pulls the correct data from the first three columns, but I would like to limit the factors-output to the one column relevant to the specific individual being evaluated. The needed FieldName will always equal the individual's current age in years (no months, no decimal), and that info is captured on the input Form behind the query.

Is there a Where or Parameter instruction that will tell the Query that the FieldName I want is = [CurrentAge] listed on the Form? (I don't think redesigning the mortality table is an option because of its size.)

[ssanfu]

Are you wanting to display the factor in a form, a report, or ???

[JBBurke]

Are you wanting to display the factor in a form, a report, or ???

No, I just want the factors to appear as an additional column in my select query.

[ssanfu]

Lets see if I understand.....

I am a male, current age is 30. You are interested in the factor when (if?) I am 55 for October - 10th month.
The form is named frmData and there is a control named "txtCurAge" for the age and a text box for the month nmber named "txtMthNum"

So the SQL would look like

SELECT tblMortality.[X-Age], tblMortality.[Male-Mo], tblMortality.[55]
FROM tblMortality
WHERE (((tblMortality.[X-Age])=[Forms]![frmData].[txtCurAge]) AND ((tblMortality.[Male-Mo])=[Forms]![frmData].[txtMthNum]));

To select a different age, say 65, you would have to change the select clause. This means editing the SQL string.
From

SELECT tblMortality.[X-Age], tblMortality.[Male-Mo], tblMortality.[55]

to

SELECT tblMortality.[X-Age], tblMortality.[Male-Mo], tblMortality.[65]

Otherwise you have to include ALL of the fields 25-100.


If you are creating the query in code to use for calculation, it is easy to change the field in the select clause.
If the query is a saved query for a form or report, it can be done, but requires code.
If a SQL string in a form or report, still possible, but again code to mofigy the SQL.

HOW the query is changed depends on what you are using the query for.

[JBBurke]

Steve, you have the concept, but it's the last Select segment, "tblMortality.[55]", that I want to automate. Is there a way to to change the "[55]" to some sort of variable, "[X]", that the Where clause clarifies? e.g., Where "X" = [Forms]![frmData].[txtRatedAge])

[ssanfu]

Is there a way to to change the "[55]" to some sort of variable, "[X]", that the Where clause clarifies? e.g., Where "X" = [Forms]![frmData].[txtRatedAge])

No. Not as the table exists now. The table is in a non-normalized form.

You have to edit the query. It is not like adding criteria.

But it can be done. Again:

HOW the query is changed depends on how you are using the query:
Is the query a saved query, a SQL string as record source for a form/report or in code?


If the query is the record source for a form/report, and the query is a saved query, write code to modify the query SELECT clause to change the field, then save the query and open the form/report.

If the query is actually a SQL string in the form/report record source, again using code, modify the SQL property of the form record source, then open the form/report.

If the query is in code, create the SQL on-the-fly. Open a record set, do whatever calculations and/or display the factor or result or .....

-----------

Looking at the current format, if the data was in Excel, from X-Age 30 to 100 and the columns (Ages) 30 to 100 and 12 months per X-Age, there would be approx 60492 cells with the factors. This is a "Short and Wide" structure.

Attached is a dB with the data in a (database) normalized structure. The table is only 5 columns wide, but 60492 records (rows).
This is a "Tall and Narrow" structure.

But this normalized structure allows you have the future age as a criteria that the current (spreadsheet) format does not allow. (see query1)

[JBBurke]

Steve, thank you! I was hoping for a different solution, but 60,000 rows of "skinny" data sounds like the way to go. Actually, after looking at your distribution, I think the number of rows will be about 30,000, once we subtract out the empty data points (i.e., someone who's age 60 will not have "future" ages of 30 - 59).

All best,

John

[ssanfu]

(i.e., someone who's age 60 will not have "future" ages of 30 - 59).

Oops , I was thinking programming, not real world.

If you have a "fat" table, you can use VBA to populate the "skinny" table (so don't have to retype).
Using the "skinny" table, your query is easy to do.

Good luck.