Overview

Data are available on population, births, deaths, and marriages. These data are broken into four geographic levels: statewide, county, health district, and town. Birth and death data are delineated by race/ethnic status and type. These delineations are described in the “tables” section below.

The database is intended primarily for individuals who want to create custom views (i.e., queries) and datasets. However, a number of views have been generated for various purposes, as described in the “views/queries” section below. In addition, charts and pivot tables have been created in order to display the data and facilitate further analysis if desired. These are described in the “forms” section below. The pivot table functions require Microsoft Excel 97 or higher. 

Database Structure

Overall structure

The report, “Relationships for vitals” gives a quick overview of the database structure. Note that the database consists of eight tables (plus a lookup table which does not appear in the report). Every table has a primary or key field, designated by the word “ID.” The three tables at the left contain all of Connecticut’s counties, districts, and towns, respectively. The county table also has an entry for displaying statewide data. As the report indicates, each of the three tables is linked to the other five tables in a one-to-many fashion. In addition, there is a one-to-many relationship between counties and towns. A similar link between district and towns can be created if desired.

Tables

The tables that comprise the database are described in a separate document. For every table, an entry of “0” may indicate missing data, an actual 0 count, or a frequency fewer than 5.

The database has three kinds of tables:

• Category tables have the names of Connecticut’s counties, districts, and towns.

Note that in the county table, the name “Zstatewide” is for the purpose of displaying state-wide data. It is so-called in order to appear last on an alphabetic list. In many cases, state-wide data is not a simple sum of counties, districts, or towns because of missing data and inconsistent reporting. We recommend that state-wide statistics not be computed by summing county, district, or town data.

Note that in the towns table, there is always a county number and sometimes a district number. Most Connecticut towns are not in health districts.

• Resident tables are for population, births, and deaths for the residents of Connecticut counties, districts, and towns.

  Note that birth and death residence data are not the same as birth or death occurrence data. For instance, a Tolland County resident who was born in 1997 appears as a Tolland County resident in the Birth table. But if that resident was actually born in New London county, she or he appears in the Occurrence table (see below) as a New London birth.

Most residence data are broken down by race/ethnic status, as follows: First, residents are classified as Hispanic or non-Hispanic. The latter group is further classified as Black, White, and Other. Totals are designated as “All races.”

• Occurrence tables are for the marriages, births and deaths that occurred in a Connecticut county, district, or town. There are two occurrence tables: the Marriage table and the Occurrence table. The latter contains birth and death data.

  Note that data in the occurrence tables are not broken down by race/ethnic status.

Views/Queries

Microsoft Access uses the word “query” instead of the standard term, “view” to express a restricted, usually two-dimensional, structure that that includes data from one or more than one table. The term, “view/query” is used to designate such a structure.

Views/queries were created for several purposes: to display data, to create other views/queries, to create pivot forms or charts (see below), or to display simple lists.

• Generally, five views/queries have been created for each residence table, to represent county, district, town, state-wide, and county-town data. For these views/queries, residence tables are combined with category tables.

• For the occurrence tables, separate views/queries have been created for marriages, births, and deaths. In addition, rate queries have been created for births and deaths. Rates are frequencies per thousand. For these views/queries, occurrence tables are combined with category tables and the population table.

• Separate residence and occurrence queries have been created for New Haven county.

Forms

The database has three kinds of forms: 1. List forms, 2. Pivot charts, and 3. Pivot tables.

• List form: The town list form identifies every town and its related county and district.

• Pivot charts: There are two charts — one displays birth rates by county; the other displays death rates by county.

• Pivot tables: Roughly, each of the residence and occurrence views/queries is displayed as a pivot table.

Pivot tables and charts are dynamic structures created for the purpose of displaying and manipulating Microsoft Access data, using MS Excel (and the OLE or OLE-DB capabilities of Microsoft Office). For further information about pivot tables and charts, interested readers can consult the Microsoft Excel help file and the Microsoft Knowledge Base. The following Knowledge Base articles may offer a good starting point:

http://support.microsoft.com/support/kb/articles/q157/4/86.asp, XL97: Limits of PivotTables in Microsoft Excel 97

http://support.microsoft.com/support/kb/articles/q211/5/17.asp, XL2000: Limits of PivotTables in Microsoft Excel 2000

http://support.microsoft.com/support/office/content/q140151/introd.asp, Office for Windows An Introduction to OLE Automation

.

.

.