Looking at Clients over 125% of Poverty
Query
Several users have contacted me with questions about how to get information about clients whose income is over 125% of poverty. The requests varied slightly, so I will explain how to do a custom query that you can modify to suit your exact needs in about 10 steps.
The first question is ‘Where do you get this information from?’ There are several possibilities. The first is that you could move in the raw data and then compute the information. However, this is not necessary since the Eligibility slip stores the poverty percent information.
- First you need to define what you want. For my Example I want:
- Clients over 125 % of Poverty
- Who we provided services to during 2014
- The case could have been open prior to 2014 and could now be closed or still open. (it was a pending case in 2014.
- We might want to also see whether there was a waiver checked on them.
- We’ll make this into a parameter query that asks for dates. This allows you to run it for any period.
- Go to the top line menu and choose Create, then select New Query.
- When it asks for a Table, pick the Eligibility table and the ClientsW table. Then close the Show Table sub Window.
- If a line doesn’t join the Casenum to Casenum in the tables, point at one and drag it over and drop it on top of the other. A line should now be there.
- Put in Dclosed and Dopen from the ClientsW table into the grid by double clicking on them.
- Put in PovPercent, ExtElig, NetPovpercent, LSCElig and Casenum from the Eligibility table by double clicking on them.
- For pending cases we need to put
<=[Enter Ending Date of the Period]
Under the DOPEN column on the criteria line.
Then Put
>=[Enter the Beginning Date of the Period] or Null
Under the Dclosed on the criteria line.
- Under the NetPovPercent put
>125
On the criteria line to see only clients over 125% of poverty.
- Optional, to run for only ones that you gave a waiver to, put True under ExtElig. Put False for all the ones where there was no waiver. Leave blank for both.
- Run the query by either going to Design on the Ribbon Bar and choosing the Run Red Exclamation or right click on query and pick datasheet view. You should be prompted for dates and then see a listing of cases.
Explanation: The straight joinder line between Clients in the query says, ‘Only show Eligibility Records that match a Client Record. Only show Clients Records that match an Eligibility Record.’ If you don’t accept an applicant that way, you could put True on the criteria line under LSCElig to show only applicants that were accepted as clients by checking that box. You could put Officenum, Unit, or any other fields in that would prove useful. If people didn’t push the Compute button on the Eligibility slip, those figures will be blank.