PIMS Resource Center

 
Creating Queries in Microsoft Access
updated 3/21/12

The custom queries tool in PIMS gives you good flexibility in designing your own PIMS reports that are not part of the set of prepackaged reports. However, if you have the technical skills to design queries in Microsoft Access, you'll find that you'll have an even greater level of flexibility by working directly with PIMS data tables- especially with PIMS version 7.

To get started, you'll need to familiarize yourself with the PIMS tables, using the resources on the Evaluation page as a guide. Though you'll likely have to revise queries designed for PIMS 6 data, you should find that linking tables is much simpler in PIMS 7. Following are some brief pointers on working with PIMS 7 data.

Person IDs

  • All records for participants who have completed Screen, Assessment, OR Intake can be linked using the unique person_id field.
  • If you're working with an aggregated data set, the person_id field will be reassigned as a unique identifier in the new data set. To determine a given person's person_id in their original data set, you can reference the field person_id_local in the table tpeople_data.
  • The site_id for each participant should be referenced only in the tpeople_data table. Unlike in PIMS 6, you should NOT use the site_id field to link all child records. The person_id alone uniquely identifies each participant.
  • You may notice that site_id is still tracked in many PIMS tables for participants imported from PIMS 6 data sets. This is used only as a reference point during the process of upgrading data from PIMS 6 to PIMS 7, but should not be used for any querying.
Kid IDs
  • All records for children can be linked using the unique kid_id field. The identity of the child's parents (i.e., their person_id and partner_id) can be found in tbirth_data.
  • The site_id for each child should be referenced only in the tbirth_data table. Unlike in PIMS 6, you should NOT use the site_id field to link all child records. Likewise, you should NOT use the child_id and pregnancy_id fields for linking. The kid_id alone uniquely identifies each child.
  • You may notice that site_id is still tracked in many PIMS tables for children imported from PIMS 6 data sets. This is used only as a reference point during the process of upgrading data from PIMS 6 to PIMS 7, but should not be used for any querying.

 
  © Copyright 2007-2017 Prevent Child Abuse America. All rights reserved.