|
Using
Clients for Evaluating Your Program
A Step-By-Step Approach
By John Paul Kemp
1. What do you want to evaluate?
2. What tools are available?
3. Macro and Micro Approach
a. Macro Approach – Looks at a Large Scale View of Program Performance
b. Micro Approach – Looks at a Case or Advocate Level View of Performance
4. Caution – Lies, Damn Lies, Statistics
a. Best Doctors Lose the most number of patients
b. Do you measure a Minister by the number of souls saved?
5. The role of Context
a. Information in a vacuum is useless
b. Dead cat example
c. We’ve been gathering for years
d. Trends and comparisons lend meaning to the information
6. What do you need to manage your program?
7. Many different ways to get information out, experiment, may be better
ways than below reports
8. Caution: LSC DOES NOT REQUIRE YOU TO USE COMPUTERS FOR CERTAIN THINGS.
For example, you can always say, “We don’t track citizenship
on the computer.”
9. Macro Approach – This is an overview of program operations
10. Micro Approach
11. Refer to the Optional Report Manual to look for Reports you need
12. Tip: Take a copy of CFW2000.MDB and ClientsT2000.MDB and put it on
a laptop that has the full version of Access on it. You can then experiment
to your heart’s content without the danger of damaging the real
data!
13. Examples: Remember –You can get this information many different
ways! You can write grouping and totals queries that would give you just
the totals for some of these.
Macro Approach – An overview of the caseload
1. Compliance - Do we serve all our counties equally? Is there an equal
distribution of Resources?
a. Click on the Report Tab
b. Click on the Client Case Reports Button
c. Click on the Non QBF Case Reports combo box
d. Click on “Case Activity for time period by County”
e. Enter in dates and then S for Staff Cases
f. This shows cases active during your time period by county, how many
were open at the beginning date and how many were open on your ending
date. Repeat this for P for PBI cases.
2. How many cases were closed in a time period and a particular location?
a. Click on the Report Tab
b. Click on the first button, Clients Case Reports
c. Go to Apply to Only Closed Cases and select the report, “CSR-LSC
Case Statistical Report”
d. Click on the QBF button
e. Enter 1/1/00 – 12/31/00 next to Date Closed
f. Optional (add other restrictions such as city, county, zip code, advocate,
etc.)
g. Click on “Make Query”
h. Click on “Produce Form”
3. What is the cost of closing our cases in each problem code area based
upon number of hours spent? How much of your funding money was spent in
each problem area based upon numbers of hours?
a. Click on the Reports Tab
b. Click on the Client Case Reports Button
c. Click on the “Cost per Hour for a Period of Time”
d. Enter funding amount, e.g., 500,000, next to “$ Funding in Time
Period”.
e. Optional (next to date closed or date opened, enter a date range such
as 1/1/00-12/31/00). Enter a number next to Funding code to limit to a
specific funding code.
f. Press “Make Query”
g. Press “Produce Form”
4. What is the cost of closing our cases in each problem code area based
upon number of cases? How much of your funding money was spent in each
problem area based upon numbers of cases?
a. Click on the Reports Tab
b. Click on the Client Case Reports Button
c. Click on the “Cost per Case for a Period of Time”
d. Enter funding amount, e.g., 500,000, next to “$ Funding in Time
Period”.
e. Optional (next to date closed or date opened, enter a date range such
as 1/1/00-12/31/00). Enter a number next to Funding code to limit to a
specific funding code.
f. Press “Make Query”
g. Press “Produce Form”
5. How Long Does it take to close a type of Case?
a. Click on the Report Tab.
b. Click on the first button, Clients Case Reports
c. Go to General Reports and select the report, “Days a Case Remained(s)
Open.”
d. Click on the QBF button
e. Next to date enter a date range such as 1/1/00-12/31/00.
f. Next to Problem Code, enter a type of case such as 63 – Landlord
Tenant
g. Enter optional other criteria, such as an individual’s staff
number next to advocate.
h. Click on “Make Query”
i. Click on “Produce Form”
j. This is a list of number of days a case remained open in that type
period sorted by the longest to the shortest.
6. Cases Closed by Advocates
a. Numbers of Cases
b. Click on the Report Tab
c. Click on the Client Case Reports Button
d. Click on the Non QBF Case Reports combo box
e. Click on “Case Activity for time period by Staff Number”
f. Enter in dates and then S for Staff Cases
g. This shows cases active during your time period by staff member, how
many were open at the beginning date and how many were open on your ending
date.
7. Types of Cases closed by an Advocate and the Reasons Closed
a. Click on the Report Tab
b. Click on the first button, Clients Case Reports
c. Go to Apply to Only Closed Cases and select the report, “CSR-LSC
Case Statistical Report”
d. Click on the QBF button
e. Enter 1/1/00 – 12/31/00 next to Date Closed
f. Enter the Staff number of the person you want to look at next to Advocate
g. Optional (add other restrictions such as advocate, county, etc.)
h. Click on “Make Query”
i. Click on “Produce Form”
8. Compare the cases closed by Advocates during a time period
a. Click on the Reports Tab
b. Click on the Client Case Reports Button
c. Click on the Graphs button
d. A graph appears, click on the Report tab and select “Cases Closed
in Time Period by Advocate.”
e. Enter S for Staff Cases
f. Play with the other pull downs to modify the graph
g. Single click on the graph so small blocks appear around the outside
h. Press Ctrl-C to copy graph.
i. Open a new Word (or Word Perfect) document. Press Ctrl-V to paste the
graph into the new document.
j. Change to landscape in Word (if you wish), size the graph and add a
text explaining its meaning.
9. Type of Cases Closed / Priorities
a. Use the cost reports to look at # of cases closed vs. time spent in
areas. You may have 40% of your cases closed in housing, but only spend
10% of your time there.
10. How were your cases resolved? What were the Main Benefits to Clients?
a. This assumes that you use the Main Benefit and Phelped fields.
b. Click on the Reports Tab
c. Click on the Client Case Reports Button
d. Click on the Main Benefit button
e. A table appears showing main benefits
f. Enter criteria into the blanks above
g. Press the Search button
h. The ways cases were resolved appears
11. When are the busiest months for closing cases and how long to close?
a. This is a custom query, drop into the full version of Access.
b. Pick Queries and choose New. Select ClientsW to include above the grid.
c. Press the Totals (Sigma) Button
d. Column 3 - Year: Year([Dclosed]), ,GroupBy
e. Column 4 - Month: Format([dclosed],"mmmm"), ,GroupBy
f. Column 5 – Casenum, ClientsW, Count
g. Column 6 - Avg Days to Close: Avg(DateDiff("d",[dopen],[dclosed])),
, Expression
h. Column 7 – Dclosed, ClientsW, Where, Is not Null and (Between
[Enter Beginning Date Closed] and [Enter Ending Date Closed])
12. When are the busiest months for opening cases?
a. This is a custom query, drop into the full version of Access.
b. Pick Queries and choose New. Select ClientsW to include above the grid.
c. Press the Totals (Sigma) Button
d. Column 1 - Year: Year([DOpen]), ,GroupBy
e. Column 2 – Exp1: Month([Dopen]), , GroupBy
f. Column 3 - Month: Format([dopen],"mmmm"), ,GroupBy
g. Column 4 – Casenum, ClientsW, Count
h. Column 5 – Dopen, ClientsW, Where, Is not Null and (Between [Enter
Beginning Date Open] and [Enter Ending Date Open])
13. Do case types closed vary based upon time of year?
a. There are many ways of doing this. Try running the CSR reports by month
for the periods in question. (See 2 above) OR a list of average times
to close during each month (See 11 above) OR
b. Run 20 Below
14. How many applicants were rejected?
a. Click on Report Tab
b. Click on Reports on Other Tables, Select Eligibility
c. Select Available Reports and click on “Basic Tabular Report for
Eligibility”
d. Put in any restrictions, such as 1/1/00-12/31/00 next to Date Opened.
e. Click the box “Client Eligible” Twice (This is a triple
state box, check means only eligible clients, blank means only clients
that are ineligible and gray means ignore.”
f. Choose to sort it by “Reason Rejected” by selecting this
from the “Sort by” box.
g. Select “Make Query” and “Produce Form”
15. What is primary reason for rejecting applicants?
a. See above OR
b. Click on Search Tab
c. Choose the Eligibility Screen
d. Pick a limited list or press Show All
e. Highlight just the year 02’ in Date Open and press Filter by
Selection to see cases in 2002.
f. Go to the Elig? Column and click on a No and press Filter by Selection
to see cases where the client was not eligible
g. Click on any letter under the Rj (Reason Rejected) and press the A-Z
button to sort by the reasons rejected
h. Scroll through and see why people
16. What was the primary time of problem faced by rejected applicants?
a. See above but sort by Problem Code
17. Compliance – Do you only have one case of a group opened in
the same funding period with substantially the same fact situation marked
as non-duplicated?
a. Click on the Activity Tab
b. Click on the Check for Near Duplicates box
c. Click on the “All Cases by Name” report
d. Enter the dates you want to cover, usually the funding year
e. All cases with exactly the same name and problem code will show up,
use the Unduplicated and Funding Code columns to see if the cases are
being double counted.
18. Compliance – Did you document or reject this list of Clients
that were over the financial guidelines?
a. Click on the Activity Tab
b. Click on the Check for Errors In box
c. Click on the “Clients Over 125% Income” report
d. Enter the dates you want to cover, usually the funding year
e. All whose income is over 125% the poverty guidelines for their family
size will be listed. Caveat: This uses the current year poverty guidelines
so that running this test for past years might not list some clients who
were slightly over.
19. Where is Time being spent – This lets you look at how much
time is being spent on Cases vs. Matters vs. Supporting Activities.
a. Click on Report Tab
b. Click on Reports on Other Tables, Select Ttime
c. Select Available Reports and click on Time by Advocate and Case
d. Put in a time period next to Tidate, e.g., 1/1/02 – 12/31/02
e. Next to the Case Number, put in Matter
f. Select “Make Query” and “Produce Form”
g. Repeat for Sup. Activity
h. Repeat for cases, use Not “Matter”, Not “Sup. Activity”
next to Case Number
20. Amount of Time spent closing a type of case – Do you know how
long, on average it takes you to close a particular type of case during
the year?
a. This is a custom query, drop into the full version of Access.
b. Pick Queries and choose New. Select ClientsW and subPcode to include
above the grid.
c. Press the Totals (Sigma) Button
d. Column 1 – Pcode, ClientsW, GroupBy
e. Column 2 – Pname, subPcode, GroupBy
f. Column 3 - Year: Year([Dclosed]), ,GroupBy
g. Column 4 - Month: Format([dclosed],"mmmm"), ,GroupBy
h. Column 5 – Casenum, ClientsW, Count
i. Column 6 - Avg Days to Close: Avg(DateDiff("d",[dopen],[dclosed])),
, Expression
j. Column 7 – Dclosed, ClientsW, Where, Is not Null and (Between
[Enter Beginning Date Closed] and [Enter Ending Date Closed])
Micro Approach – Individual Cases
21. Compliance - Generate a list of any number of Random case numbers
a. Click on the Reports Tab
b. Click on the Client Case Reports Button
c. Click on the “Display 100 Random Clients” Button
d. Enter a beginning and ending Date Opened
e. 100 Random Clients are listed
f. Important: This is done only to provide you the ability to get whatever
random cases you want. To modify this, do the following:
g. Drop into the full version of Access
h. Go to the Queries Tab
i. Pull up the QRandom query in the Design Mode
j. Right Click in the gray area above the grid and choose top values
k. Change it from 100 to any number you want
l. Add any other restrictions you need to the grid, such as advocate,
etc.
m. Generate the report
22. Compliance – Choose every Nth Case between two dates opened
a. Click on the Reports Tab
b. Click on the Client Case Reports Button
c. Click on the “Every 10th Case Between 2 Dates” Button
d. Enter a beginning and ending Date Opened
e. Every 10th case between these two dates are listed
f. Important: This is done only to provide you the ability to get whatever
selection of cases you want. To modify this, do the following:
g. Drop into the full version of Access
h. Go to the Queries Tab
i. Pull up the QNthRecord query in the Design Mode
j. Go to the last column and drag it wider so you can see “PlusOne([Casenum])
Mod 10” Change the 10 to whatever number you want, e.g., 30 for
every 30th case
k. Add any other restrictions you need to the grid, such as advocate,
etc.
l. Generate the report
23. Compliance – Print out the CLASP checklist page for certain
cases
a. You could use the above 2 reports to automatically generate CLASP checklist
sheets for the cases selected
b. To generate specific CLASP Sheets, This is not documented anywhere!
c. Click on the Report Tab.
d. Click on the first button, Clients Case Reports
e. Go to General Reports and select any report, such as “Basic Tabular
Report for Clients”
f. Click on the QBF button
g. Go to Advanced Options in the lower right hand corner and type fprtCLASP
audit over the top of the fBasicCL1 that is currently there.
h. Enter your criteria and then press Make Query and Produce Form
24. Show cases that have Good Stories – Cases resulting in excellent
results
It’s time for the Annual Report or a report to a funder. How can
you find those great cases that you did?
a. The second page of the client intake sheet has a check box called Good
Story. Make sure some have been checked. Click on the Report Tab.
b. Click on the first button, Clients Case Reports
c. Go to General Reports and select the report, “Basic Tabular Reports
for Clients”.
d. Click on the QBF button
e. Click on the Good Stories Button
f. Optional other criteria (next to date closed or date opened, enter
a date range such as 1/1/00-12/31/00)
g. Click on “Make Query”
h. Click on “Produce Form”
i. This is a list of cases that have good stories checked. You can copy
the case numbers from each (Ctrl-C) and paste them into the Search Tab
for Client cases (press Case Number button then Ctrl-V) to look at each.
25. Case Notes Changed – You want to look at what was done in all
the cases between or since a particular date by looking at the changed
case notes.
a. Click on the Report Tab.
b. Click on the first button, Clients Case Reports
c. Go to General Reports and select the report, “Print the Client
Case Notes.”
d. Click on the QBF button
e. Next to Memo Change Field enter a date range such as 1/1/00-12/31/00
or ask for those changed after a certain date, >1/12/02.
f. Enter optional other criteria, such as an individual’s staff
number next to advocate.
g. Click on “Make Query”
h. Click on “Produce Form”
26. Cases looked at every X days - Many offices have a policy that every
case needs to be reviewed every so many days. If a case is reviewed, there
should be a time sheet for it.
a. Click on the Activity Tab
b. Click on the Check for Errors Combo Box
c. Click on the “Open Cases with No Time in Period” report
d. Enter number of days back, e.g., 180
e. All open cases with no time entered in the last 180 show up
27. Open Case Reviews – Time to review open cases with advocates.
a. Click on Search Tab
b. Choose the Client Screen
c. Click on a cell under Dclosed that is blank
d. Press the Filter by Selection button to see all open cases
e. Go to the Snum Column and click on any record. Then press on A –
Z to sort the open case list by Advocate. Use any other column and A –
Z to sort by a different topic.
f. Use the Print button to print the list. OR
g. Click on the Report Tab.
h. Click on the first button, Clients Case Reports
i. Click on the Non QBF Case Reports combo box
j. Click on “Open Cases by Advocate”
|
