EPI 218Queries and On-Screen Forms

Michael A. Kohn, MD, MPP9 August 2012

Outline

Revisit the concept of normalization Loose ends: Yes/No Field, Blank Row

for new records Select Queries Action Queries On-Screen Forms Start Lab 2

Lab 4 (8/23) uses REDCapYou need a REDCap logon

Web-based research data collection system developed at Vanderbilt

Available free through UCSF Academic Research Systems

http://tinyurl.com/yh5m6ka You are both the Principal

Investigator and User 1.

Final Project: Part ASend in or Demonstrate Your Study DatabaseDue 9/20/2012

Send in a copy of your research study database*.

We prefer a database that you are currently using or will use for a research study.

However, a demonstration or pilot database is acceptable.

*If you are unable to package your database in a file to email, you can send us a link or work out another way to review your database.

If you are doing secondary analysis of data collected by someone else,

obtain the data collection forms* used in the original data collection,

set up a new database that you would use for a follow-up study.

*Often easily obtained by doing a Google search or emailing the author of the original study.

Final Project: Part ASend in or Demonstrate Your Study DatabaseDue 9/20/2012

General description of database Data collection and entry Error checking and data validation Analysis (e.g., export to Stata) Security/confidentiality Back up

Final Project: Part BSubmit Your Data Management PlanDue 9/20/2012

Final ProjectDue 9/20/2012

Start thinking about this now.Build your own study database as

you work through the labs.Use extra time in lab to work on your

study database.Set up appointments with course

faculty early.

Normalization -- Lab Results

Occasionally, the subjects (in the Infant Jaundice Study) had blood tests.

Robert had a CBC on 1/30/2010.Helen had a CBC on 1/30/2010, LFTs on

2/28/2010, and a CD-4 count on 3/31/2010.

LabResultQry

Loose End: Yes/No fields

Binary fields are not very useful, because you can’t distinguish “No” from blank (not valued).

I create a combo box like we used for Race in Lab 1 with 0 for “No” and 1 for “Yes”. This allows blank.

Loose End: Blank Row for New Record

Datasheet view shows a blank row at the bottom for new records.

Demonstrate.

Select Queries

Select queries (aka “Views”) organize, sort, filter, and display data.

Queries use Standard Query Language (SQL), but you don’t have to learn it, because of graphical query design tools.

A query can join data from two or more tables, display only selected fields, and filter for records that meet certain criteria.

Demonstration

Age in months and BMI at exam of subjects who were examined in January and February of 2010.

QueryDemo

Select Queries Produce “Table-Like” Results

Note that the result of a select query that joins two tables, displays only certain fields, selects rows based on special criteria, and calculates age and BMI still looks like a table in datasheet view.

But, remember that it is a dynamic “view” of data from the underlying tables.

Undesirability of Storing Calculated Values

Store raw data, not calculated fields, e.g., store dates and times; calculate intervals.

 

Storing a patient’s birth date allows calculation of his or her exact age on the date of a particular measurement.

Figure 15. Storing calculated fields such as “AgeInMonths” is undesirable. What if the birth date for SubjectID 2322 (Helen) is corrected in the “Baby” table?

“Action Queries” Change Data

1) Update Query -- changes the values of specific fields in existing records

2) Append Query -- adds new records (rows) to a table

3) Delete Query -- deletes records from a table

Front End or Interface

On-screen forms

Advantages of On-Screen Forms

Data keyed directly into the computer data tables without a transcription step

Include validation checks and provide immediate feedback when a response is out of range

Incorporate skip logic

Standard Data Entry Conventions

•Several conventions for data entry and display have developed over time.

•Most users of screen forms have come to expect them subconsciously.

•mutually exclusive, collectively exhaustive choices are displayed as an “option group” consisting of several different “radio buttons” or as a dropdown list.

•choices which are not mutually exclusive are displayed as check boxes.

N.B. An “option group” of mutually exclusive choices is a single column or field. A group of N check boxes represents N yes/no fields.

Use check boxes when options are not mutually exclusive. (5 fields)

Use radio buttons when options are mutually exclusive. (1 field)

Computer chart abstraction form showing two common data entry conventions.

Demonstration

Option group for examiner’s medical specialty

MasterRaceAsFieldList, MasterRaceAsOptionGroup, MasterRaceAsAllThatApply

On-screen vs. paper formsMinimize the extent to which study measurements are recorded on paper

forms. Enter data directly into the computer database or move data from paper

forms into the computer database as close to the data collection time as possible.

When you define a variable in a computer database, you specify both its format and its domain or range of allowed values. Using these format and domain specifications, computer data entry forms give immediate feedback about improper formats and values that are out of range. The best time to receive this feedback is when the study subject is still on site.

Can only monitor data for outliers, systematic differences between data collectors or study sites, and study progress (I.e., query the data) once the data are in the computer.

You can always print out a paper copy of the screen form or a report of the exam/interview results once the data are collected.