PIMS Resource Center

Aggregating Databases
updated 9/23/11

PIMS allows you to combine two separate data tables using the data aggregation feature.  Note that this is not typically something you would use at an individual site.  However, state agencies frequently aggregate data tables when generating statewide or countywide reports on PIMS data.

How to Use the Data Aggregation Feature in PIMS

If you just want to aggregate a few data tables, you can use the data aggregation feature built into PIMS.  Otherwise, refer to the following below.

  1. Click on Main Menu.
  2. Click on DB Administration.
  3. Click on Aggregate Databases.  This opens the PIMS DB Aggregation Utility.
  4. In the PIMS DB Aggregation Utility window, click on PIMS DB#1.  The Select Database window opens.
  5. Browse to find your first database, and click Open.  On returning to the Aggregate Database window, you should notice the full pathname to your first source database.
  6. Click on PIMS DB#2.  The Select Database window opens.
  7. Browse to find your second database, and click Open.  On returning to the Aggregate Database window, you should notice the full pathname to your first source database.
  8. Click on Dest DB.  Browse to find an appropriate location for your new database, and create a name for your database in the File Name box.  Click Open.  On returning to the Aggregate Database window, you should notice the full pathname to your destination database.
  9. Click on Aggregate Standard to aggregate typical PIMS databases into a new database.  Note that this function will link to the new database at the end of the process.  (Aggregate Exported Data will aggregate exported PIMS data.  Note that you cannot link PIMS to an exported data table- this is designed for analysis in Access or other statistical programs).  You will see a “Please Wait” window while the new database is being created.
  10. When this process is done, you should get the message “Database aggregated successfully!  The new database has been attached to the current program.  Click OK, then click Close to close the Aggregate Database window.

How to Use the Data Aggregation Tool
  1. Download the data aggregation tool.
  2. A window with the title Opening pims_aggr.exe opens.  Click Save File to save the file pims_aggr.exe to your desktop.
  3. From your desktop, double-click on pims_aggr.exe.
  4. The WinZip Self-Extractor window opens.  Click Unzip.
  5. You should see a message, “Files unzipped successfully.”  Click OK.
  6. Close the WinZip Self-Extractor.
  7. Open the folder PIMS Aggregator and drag the shortcut files to your desktop.
  8. Open My Computer, then the C: drive, then Program Files.
  9. From the folder PIMS Aggregator, drag the files pims_aggregator and pims6_supp_code into the pims5e folder.
  10. Use the shortcut files to open the PIMS Aggregator.  Only one of the shortcuts will work, depending on which version of Microsoft Access you are using.  You can throw out the remaining shortcuts.

Note that it is recommended that you aggregate no more than five databases at a time.

If you notice any errors when aggregating databases, it is likely that the aggregation did not work correctly, regardless of what other messages you get.

Working with Aggregated Data Tables

Although PIMS 7 has been beta tested, very few PIMS users work with aggregated data sets.  Some testing has been performed on aggregated data sets, but not nearly as much.  It is important to check your results (particularly the number of families) for all reports to make sure that these numbers make sense and include all sites.

Given the limitations of Microsoft Access, you may run into some quirky issues related to memory.  It’s important to regularly back up your database.  You may also need to occasionally replace your PIMS front-end (pims6be.mde) in order to “clean up” the database.  Please report any errors you encounter as this information will help other states troubleshoot similar issues.

How Site Definitions are Handled with Aggregated Tables

It is important to understand how PIMS aggregates the various Site Definitions.

Immunization and Well Baby Schedules
The aggregation feature uses a master immunization schedule for all sites.  By default, this is the schedule from the first PIMS Database you select.  You can customize this schedule, and then click “Update All Children” to get all sites in synch with the same schedule.  Note that if you use the field “Required for Children Born After,” individual immunization events (including completion dates) will be deleted for anyone born before that date.

Well Baby schedules are handled the same way as immunization schedules.

Instruments and Service Level Definitions
The aggregation feature will include all instruments and service level definitions of all sites.  It will not assume that Level I for one site will be the same as Level I for another site.  However, you can scroll through all site definitions in the Service Level Definitions form and edit the number of visits required and caseload weights to make sure that all sites are defining each level the same way.

Enrollment Definition
Most likely, you will want to set all sites on the same Enrollment Definition.  There is currently no way to do this directly through PIMS.  However, you can link to PIMS from a blank database, and update the enrollment definitions from the tsite_lkup table.  Following are the codes for enrollment definition (enrollment_definition_code):





Date of screening


Date of assessment


Date HFA service accepted


Date participant agreement form signed


Date service level assigned


Target child's date of birth


Date of first home visit

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