too many selections

[kennethburr]

I have a fields in Access 2003 labeled hsize, which holds the number of
people in a household. I have another field named hincome, which holds
the total annual income for that household. I am trying to develop a
calculated field in a query that will match up to a chart I have, which
shows for each size household ( 1 though 8) the median income for that
size of household, and then depending on the actual total household
income for that family size their income level and eligibiity. For
example, a household is considered low income if it has less than 50%
of the median income for that size family, or if 51% to 80% of the
median income for that size family, the income is considered moderate,
or if over 80% then the result is not eligible. I started trying to do
a nested IIF statement, but then I realized that with all the nesting
that would be required for three different possible outcomes for 8
different household family sizes there must be a better way. I would
greatly appreciate any suggestions.
Ken Burr

 

[Smartin]

I have a fields in Access 2003 labeled hsize, which holds the number of
people in a household. I have another field named hincome, which holds
the total annual income for that household. I am trying to develop a
calculated field in a query that will match up to a chart I have, which
shows for each size household ( 1 though 8) the median income for that
size of household, and then depending on the actual total household
income for that family size their income level and eligibiity. For
example, a household is considered low income if it has less than 50%
of the median income for that size family, or if 51% to 80% of the
median income for that size family, the income is considered moderate,
or if over 80% then the result is not eligible. I started trying to do
a nested IIF statement, but then I realized that with all the nesting
that would be required for three different possible outcomes for 8
different household family sizes there must be a better way. I would
greatly appreciate any suggestions.
Ken Burr

(Untested)

Put your "chart" into a table:

MedianIncomes (HHSize, MIncome)

Join your main table/query to MedianIncomes on hsize = HHSize.

You still need a nested IIf because you have three choices, but not 24!
In your query include the expression:

Eligibility: IIf (hincome >= MIncome * .8, "Ineligible", IIf (hincome >=
MIncome * .51, "Moderate", "Low"))

HTH

 

[John Vinson]

I have a fields in Access 2003 labeled hsize, which holds the number of
people in a household. I have another field named hincome, which holds
the total annual income for that household. I am trying to develop a
calculated field in a query that will match up to a chart I have, which
shows for each size household ( 1 though 8) the median income for that
size of household, and then depending on the actual total household
income for that family size their income level and eligibiity. For
example, a household is considered low income if it has less than 50%
of the median income for that size family, or if 51% to 80% of the
median income for that size family, the income is considered moderate,
or if over 80% then the result is not eligible. I started trying to do
a nested IIF statement, but then I realized that with all the nesting
that would be required for three different possible outcomes for 8
different household family sizes there must be a better way. I would
greatly appreciate any suggestions.
Ken Burr

Create a little 8-row table with its Primary Key the household size (1
to 8) and its only other field the median for that size. Create a
query joining this table to your table by hsize; you can divide the
hincome by the median income from the joined table.

You can use an expression like:

IncomeGroup: Switch([hsize]/[median] < 0.5, "Low", [hsize]/[median] <
0.8, "Moderate", [hsize]/[Median] >= 0.8, "Ineligible", True, "Missing
Data!")

to group the values into named ranges.

John W. Vinson[MVP]

 

[kennethburr]

My apologies, but in going over the chart and trying to get a formula
to replace the chart I have discovered that there is not a formula that
will work. For example the 30% median income is not 50% of the 60%
level. And if I double the 50% level and then take 80% of that figure
I do not get what is listed in the chart as the 80% level. And if a
family income is $1 over what is listed as the 80% level then the
family is not eligible. Part of the problem I found out is that while
the prepared chart is allowed to round to the nearest $50, I must round
off the family income to the nearest dollar and use the exact figures
listed in the chart. The chart is broken down to 15% level, 30% level,
50% level, 60% level and 80% level for each family size of one through
eight. I have created a seperate table that has all those figures in
it, but I don't know where to go from there. Any help would be greatly
appreciated.

Ken Burr