Painless Data Collection Using Access

1
Painless Data Collection Using Access

• Kathryn Pereira, National AgrAbility Project
• Cally Ehle, AgrAbility of WI

2
Course Overview

• 830- 915
• Basic Demographic Data Collection
• 915-1000
• Using Access to Collect Demographics
• 1030-1130
• Beyond the 10 Demographic Points
• 1130-noon
• Highlight of SRAP experiences, general group
  discussion

3
The Basics of Collecting Client Demographics

• PART I

4
Why Collect and Report

• To assess how we are doing on a state and
  national level and how we are progressing over
  time.
• To provide justification to ensure that the USDA
  funding continues for all SRAPS.
• To convince the US legislature to provide
  additional funding for future state projects.

5
The Basics What to collect report

• We only require 10 pieces of information
• 1. Client ID
• 2. Client Status
• 3. Date of Birth/Age
• 4. Gender
• 5. Client relation to farm
• 6. Current work role on the farm
• 7. Type of agricultural operation
• 8. Origin of disability
• 9. Date of disability
• 10. What is the disability

6
The Basics How to Report

• Need to collect and report the same way.
• If you dont know something- do not make an
  entry, leave it blank.
• Collect information during first contact.
• Try to use an electronic data base.
• If collect information on paper, enter it into
  electronic data base ASAP.

7
The Basics Defining a Client

• An AgrAbility Client is an individual with a
  disability engaged in production agriculture as
  an owner/operator, family member, or employee who
  has received professional services from
  AgrAbility project staff during an on-site visit.
  

8
The Specifics AgrAbility Client Information

• Client ID
• a unique number
• assigned when enter program
• does not change
• essential

• Client Status
• New
• Ongoing
• Re-opened

• Gender
• Male
• Female

• Date of Birth (Age)
• Best to enter both

9
The Specifics Who is the Client?

• Please check one.
• Owner/Operator
• Spouse/partner
• Dependent adult
• Child (lt 18 years old)
• No longer actively farming/ranching
• Employee
• Migrant
• Seasonal worker
• Never farmed
• Planning a new agricultural career
• Other family member

10
The Specifics Client Work Status

• Check one.
• None
• Full time
• Part time (regular basis)
• Occasional (irregular basis, several times a year)

11
The Specifics Type of Agricultural Operation

• Choose three
• Agri-business
• Field/grain crops
• Nursery crops
• Orchard crops
• Vegetable crops
• Fruit crops
• Hay

• Dairy
• Livestock
• Poultry
• Swine/Hogs
• Other animal
• Specialized crops (mushrooms, flowers)
• Other please
  specify____________

12
The Basics How to Collect Sensitive Information

• Sensitive topics may include
• Type of disability and how it occurred
• age
• martial status
• Establish a relationship with the client.
• Save sensitive topics until end of interview.

13
The Specifics Cause of Primary Disability
14
The Specifics Disability Type

• Report primary, secondary and terciary
  disability.
• 6 Major Disability types
• Injury/amputation
• Neuromuscular diseases
• Neurological Condition/disorder
• Other disease
• Sensory impairments
• Other

15
The Basics Updating Information

• To ensure consistency in reporting update
• on-going and re-opened clients
• Age, work status, type of farm operation status,
  new or secondary disability
• Only cases that were active during the reporting
  year should be reported.

16
Client Information Example
17
Review/Advice/words of wisdom?

• Be systematic
• Strive for consistency
• Be aware of sensitive topics
• Explain why
• Understand definitions
• Be persistent, patient and friendly
• Use your best judgment, you are in control of
  your data
• Report all information annually
• Contact NAP evaluation specialist with questions!

18
AgrAbility Client Demographics 2001-2006

• All SRAPS reported in 2005 and 2006
• Five years of data
• We have 5,678 data points
• At least 50 are likely unique clients
• Lots of missing data
• Minimize this lost data in future

19
Age of All New Clients 01-06
20
Client Gender2001-06
n2,452 (new clients only)
21
Who is the client?2001-06
N 2,366 New clients only
22
Current work status on farm2001-06
N 2,147 New clients only
23
Top 10 Types of Ag. Operation2001-2006 (new
clients only)
24
Origins of Disabilities 2001-06
New clients only, n2,330
25
Origin of Disability Non-Agriculture Related
Incident
2001-06, new clients only, n1,291
26
What is other in Non-Ag Incident?

• Thats what wed like to know!
• Most do not explain
• A few reasonable explanations include
• Insect (bite?), work-related off-farm injury,
  object fell on client, military (combat?)
• Most explanations are not relevant
• Amputation, brain injury other, spinal cord
  injury, stroke, heart attack

27
Origin of Disability Agriculture Related
Incident
2001-06, new clients only, n523
28
What is other in Ag-Related Incident?

• Again we dont know!
• Less than 25 are specified
• These include
• ATV, burn, crushed, elevator, farm accident,
  hernia, logging, moving a heavy object, welding,
  lawnmower, farm accident, not available, back
  injury,
• Which of these are reasonable explanations?

29
Most frequently reported primary disabilities
2001-2006
n 2,461, new clients only
30
Quality of Data

• Important
• If we do not have good data how can we prove we
  are doing a good job?
• How do we know if we need to make improvements or
  changes?
• How will we be able to get money from non USDA
  sources?
• Reflects on the entire AgrAbility Project

31
Using Access to Collect Demographics

• PART II

32
Why Use a Database?

• Makes annual reports and renewal easy.
• Up-to-date information for newsletters, outreach
  materials and potential funders.
• Tracks and integrates the needs of both extension
  and non-profit partners.

33
Organizing Data Collection

• Who collects the data.
• When is data collected and entered.
• How many people access the database.
• Examples from SRAPS.

34
Assessing Access Experience

• Beginner
• No experience necessary.
• Intermediate
• Familiar with tables, relationships, queries,
  forms and reports.
• Advanced
• Knows how to use SQL, build multi-table forms,
  update and append data and more.

35
Basic Data Entry Exercise

• Mock client interviews with partner.
• Practice good interviewing skills
• Ask all questions
• Review answers before disconnect
• Thank the person you interview
• Enter data that you collect.

• Always makes a back-up copy of your database!

36
Tailoring the Database for Your SRAP

• Default state information for address
• Go to directory, under object column select
  Tables
• Highlight main table, tbl_ClientContactTable
• Go to design view
• Scroll to ClientState click
• At bottom, select General tab
• Click on Default Value box
• Type in US P.O. approved abbreviation
• Save table, then exit design view
• Check to see if your state automatically appears
  in the table.

37
Tailoring the Database for Your SRAP

• Changing the name of your SRAP
• Repeat steps 1-3 from previous slide
• Scroll to SRAP
• Move to General Tab
• Click Default Value box
• Type in SRAP code from the list on handout
  Client Demographic Info
• Save, exit design view, check

38
Tailoring the Database for Your SRAP

• Specifying county information
• Go to Directory
• Under objects go to tables
• Highlight tbl_counties and open
• Enter county name
• Hit tab, enter another county name
• Repeat until all counties are entered
• Close table

39
What is a Query?

• A request for information from a database.
• How many male clients?
• How many clients have arthritis?
• Average age of all clients.

40
Query Example 1How many male clients

• Go to directory
• Under Objects column click Queries
• Click New
• In the box click Simple Query Wizard
• Choose qry_ClientContactTable
• Highlight fields ClientID and gender
• Move fields using gt to right side

41
Query Example 1How many male clients (cont)

• Press next, choose detail
• Press next
• Rename qry_MaleClients
• Finish
• Look at results

42
Query Example 2 Average age of all clients

• Repeat steps 1-5 from example 1
• Highlight age field
• Move age using gt to right hand side
• Press next, choose summary
• Check options, average, min, max
• Next, rename qry_AverageAge
• Finish, look at results

43
Overview of the NAP Query

• Designed to make annual reporting easy
• Run query on the last day of your grant cycle
• Send in data to NAP Evaluation Specialist.
• Update information to prepare for the next grant
  year cycle
• On first day of new grant cycle begin collecting
  new client information.

44
The NAP Query Specifics

• Data Maintenance
• Consistently update client closures.
• To Close a case
• In client record, change client status from new,
  re-opened or on-going to closed in current grant
  year.
• Do this as soon as you close a case.
• Do not wait until the last day of the grant cycle
• This keeps your records up to date and will
  ensure ease of reporting.

45
Send Demographic Report to NAP
Run NAP Annual Demographic Report

• Press the button!
• Check the table for obvious mistakes.
• Close the table.
• Press the send button.
• Enter the email address for the NAP evaluation
  specialist
• kmpereira_at_wisc.edu

Send Annual Demographic Report to NAP
46
After Sending in Annual NAP Report

• Update Client Status information
• After you push the button immediately go to the
  switchboard
• Click view open clients
• Go to Client Status
• Change all new clients to on-going
• Sorry, this is manual, but crucial busy work
• Close the form

47
After Sending in Annual NAP Report

• Update Client Status information (cont)
• Return to switchboard
• Click View clients closed in current grant year
• In form, go to Client Status
• Change all clients closed in current grant year
  to closed
• Close form
• You are ready to begin adding new client data in
  the new grant year!

48
What is a Report?

• A formatted and organized presentation of data
  usually for paper copies.
• Go to directory, under objects column select
  reports
• View rpt_ClientSummary
• This report can be used by an individual case
  worker to take to the field.

49
Creating a Simple Report

• Go to directory
• Under object column, select Reports
• Choose New
• Select Auto Report Columnar, Choose
  qry_NAPSurveyData
• View report
• Notice that this report has number codes

50
Creating a Simple Report (cont)

• To make words appear on your report
• Select design view (upper left corner)
• Go to gender box, right click on drop down box
• Open data tab, Go to Row source type
• Change drop down box to value list
• Go to Row Source
• Delete entry
• Enter 1Male2Female
• Check to make sure bound column 2
• View report

51
Creating a Simple Report (cont)

• To make words appear on your report
• Select design view (upper left corner)
• Go to wrk stat box, right click on drop down
  box
• Open data tab, Go to Row source type
• Change drop down box to value list
• Go to Row Source
• Delete entry
• Enter 1None2Full time3Part
  time4Occasional
• Check to make sure bound column 2
• View report

52
Creating a Simple Report (cont)

• To make words appear on your report
• Repeat these steps
• codes s for all NAP required information are
  found on the handout entitled
• Client Demographic Information
• Good luck! Dont be afraid to play with this to
  see what happens.
• Dont worry- Non-NAP required info will always
  appear as words.

53
BREAK _at_ 10 AM
54
Importing an Excel Spreadsheet Into Access

• toolbar file get external data
• Click import
• Select Excel file
• Follow directions on import spreadsheet wizard
• Make sure to import data in a new table with no
  primary key
• Check that all records import

55
Beyond the 10 NAP Data Points

• Why collect more data?
• There is so much more to know!

• Additional client details
• Ethnicity
• Guardian/Spouse/Partner
• How the client heard about AgrAbility
• Method of Contact
• Information and Referral Cases
• AgrAbility Contacts

56
Adding Information to FormsExercise

• Intermediate/Advanced group only
• Add a VR Client check box.
• Add a Social Security Benefits check box.
• Refer to handout for directions.

57
Connected Forms

• Eliminates double/multiple data entry.
• Increases integrity of data
• Relationships tie tables and forms together.
• You can add new forms by creating relationships.

58
Connected Forms

• You might want to add forms to collect
• Client Satisfaction Data.
• Impact Evaluation Data.
• Farm visit dates.
• Education and outreach effort.
• Recommendations for client.

59
IRB

• We do have IRB approval to use this data
• Confidentiality vs. Anonymity
• Password protected computers
• Locked offices and file cabinets

60
More on Client IDs

• Providing ID s and birthdates do not violate
  confidentiality. Most IRBs on this type of human
  subject research require confidentiality, not
  anonymity.
• However, do not allow the ID numbers to represent
  something meaningful.
• Examples putting in client initials or
  birthdates as a part of the ID system

61
Getting Help

• Using Help in Access
• University Training
• Contact Cally or Kathryn

• Cally Ehle
• ehle_at_wisc.edu
• 608-262-9336

• Kathryn Pereira
• kmpereira_at_wisc.edu
• 608-265-9935