Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Ephrata, Washington, USA
    Thanked 0 Times in 0 Posts

    query - 125% of poverty (2003)

    We have a database of our customers who need financial assistance. One of our programs provides assistance to people whose total household income is 125% of poverty based on the HHS Poverty Guidlines. The database has a customer information form. I've been asked to add a field to the form that identifies those households who meet the 125% over poverty criteria. At this point, they haven't asked me for anything other than to be able to run our regular customer report and include a field showing whether or not they meet the poverty assistance criteria. They want to see the field in the data entry form we already have as well as in the report. And, they said a simple YES or NO would suffice. They don't need to see the calculation.

    I have a query that has the total number of people in the household and the total household income.
    The HHS Guidelines are:
    1 person - income of $10,400
    2 people - income of $14,000
    3 people - income of $17,600
    4 people - income of $21,200
    5 people - income of $24,800
    6 people - income of $28,400
    7 people - income of $32,000
    8 people - income of $35,600
    each additional person over 8, add $3,600 to the 35,600

    So, how do I do this? I was originally thinking of an IF statement to determine if the houshold income and number of people qualifies them as having income over 125% of poverty. But, I can't find an IF statement in Access, and even if I could, I wound myself into circles trying to figure out how to build it. Thank you for helping me. I've been struggling with this for a couple of days now.

    I would greatly appreciate your help!

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Frederick, Maryland, USA
    Thanked 2 Times in 2 Posts

    Re: query - 125% of poverty (2003)


    You said that you have a query that gets the total number of people (lets call the field TNP) and I assume you have their total income (call the field TI)

    You could do the following in your query to generate your yes/no field: (Assumes TI and TNP are available fields in the query)
    IIF(TI > (10400 + (3600 * ([TNP]-1))),-1,0)

    Basically, this says if the Total Income is greater then the HHS Calculation then yes else no. You can calculate your HHS guidelines by the equation shown. 10400 + (3600 * ([TNP]-1))


    (It's been a while!)

  3. #3
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Chelsea, Gtr London, United Kingdom
    Thanked 0 Times in 0 Posts

    Re: query - 125% of poverty (2003)

    I would be tempted to store your Poverty Guidelines in a separate table and link to it from your totals query. This would have the advantage that when the guidelines change you only need to change the table values, rather than fussing with amending queries/code etc..
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts