Office of the State Comptroller
BOND DATABASE INFORMATION
WARNING - NONE OF THE FIELDS IN THE DATABASE HAVE BEEN LOCKED. YOU HAVE COMPLETE ACCESS TO EVERY TABLE, QUERY, FORM AND REPORT. AS YOU MOVE AROUND THE BOND DATABASE TO REVIEW RECORDS, PLEASE USE CAUTION. IT IS POSSIBLE TO CHANGE DATA ENTRIES BY MISTAKE. THIS IS ESPECIALLY TRUE FOR THE LIST BOXES USED ON THE DATA INPUT FORM. A SIMPLE CLICK OF THE MOUSE IN THE WRONG PLACE COULD CHANGE THE CATEGORY OF RECIPIENT, USE OF FUNDS OR PROGRAM AREA.
The complete downloaded version of the bond allocation database is intended for users who want to customize their search capabilities. The complete version permits independent analysis by allowing users to design their own queries and create their own reports from any of the fields contained in the database. Therefore, the complete database will be most useful to those who have a working knowledge of Microsoft Access ® 97 or a similar database software package.
PRELOADED TABLES, FORMS, QUERIES AND REPORTS
The download file comes with a number of preloaded tables, queries and reports that are based on the information contained in the database.
In Microsoft Access, tables are used to store data. In total, there are seven tables pre-loaded onto the bond database file. They are:
- Bond Commission Data Input Table - This is the most important table in the database. It stores all the data entered about bond projects from the State Bond Commission meeting agendas.
- Agencies - This table contains state agency names and the corresponding four-digit agency numbers that are used for the state accounting system. For example, agency number 1326 corresponds with the Department of Public Works.
- Fund Names - This table contains the names of bond funds and the corresponding four-digit numbers that are used for the state accounting system. For example, fund number 1872 corresponds with the Capital Equipment Purchasing Fund.
- Program Area - This table contains thirteen program categories (e.g., environment, housing, transportation, etc.) and is the source for the list box for “program area” on the Bond Commission Data Input Form.
- Recipient - This table contains six recipient categories (e.g., state, municipality, non-profits, etc.) and is the source for the list box for “recipient” on the Bond Commission Data Input Form.
- Session - This table contains a list of the twelve months and is the source of the “special session month list box on the Bond Commission Input Form.
- Use of Funds - This table contains a list a seven possible uses of bond funds and is the source for the “use of funds” list box on the Bond Commission Input Form.
In Microsoft Access, queries are used to retrieve and categorize data. They are short programs that explain to the database what information you want to retrieve. A query can produce a subset of a larger table and categorize the information in a customized way. The bond database file contains a number of pre-loaded queries. The titles help explain what information each includes. Try opening a few of the queries to get a feel for the data they contain.
In Microsoft Access, forms are used for data input. The information that is entered is stored in a table (see description above). The bond allocation database contains only one form and it is called the “bond commission data input1” form. Try opening the form to see the various data fields it contains. In addition, you can view individual records in the database from the form view. Remember the data fields are not locked. As you move around the database records, please use caution; it is possible to change an entry by accident!
In Microsoft Access, reports are used to present information from the database in an understandable way. Often, reports are based on data contained in a query. The pre-loaded reports contain summary information about various categories of bond projects and include calculations (e.g., total allocations). The reports vary in length from 2-3 pages to several hundred pages and can be viewed on a computer screen or printed out onto paper. The report titles help explain what information each includes. Try opening a few of the reports to get a feel for the data they contain.
FIELDS IN THE DATABASE
The bond allocation database contains a total of 23 fields. Each field
contains information about bond projects that is taken directly from State Bond
Commission meeting agenda items. The following provides an explanation of the
various fields contained in the database. Two names are presented for each
field. The first is the field name label as it appears on the bond commission
data input form view of the database. The second (in parentheses) is the
corresponding field name as it appears on the datasheet view of the database.
Note, it may be helpful to look at the Bond Commission Data Input Form as you
read about each field.
1) Record Number (RecordNum) - This field contains a number automatically generated by the database as new records are entered. The number is unique and represents the order in which the records were entered.
2) BC Meeting Date (Date) - This field contains the meeting date on which the State Bond Commission approved the specific project.
3) BC Agenda Item (ItemNumber) - This field contains the item number for the State Bond Commission's meeting agenda for the date the project was approved.
4) Split Agenda Item? (SplitItem) - This field is a check box and a check means “yes” or “true.” When checked this field indicates that more than one record (i.e., project) is contained within a single State Bond Commission agenda item.
5) PA or SA (PA_SA) - This field contains a list with two pre-set choices to indicate whether the enabling bond authorization is a Public Act or Special Act.
6) Sp Session Month (Session) - This field contains a list of months that is used to indicate when a Special Act (Bond Authorization) was passed outside the regular legislative session. When this happens, a Special Act is often identified with the month during which that special session was held. An example would be Special Act 97-001 of the June Special Session.
7) Act Number (ActNumber) - This field contains the five-digit number of the Public Act or Special Act that provides legislative authorization for the bond project. The first two digits indicate the year in which the legislation was enacted. For example, 99-242 stands for Public Act Number 242, which was enacted in 1999.
8) Section (Section) - This field represents the section of the Public Act or Special Act that contains the specific legislative authorization for the bond project in question.
9) Recipient? (Recipient) - This field contains a list with six pre-set choices to identify the type of entity that is either receiving or directly benefiting from the bond funds. Analysts in the Office of the State Comptroller made judgments from a group of choices that included:
- State - includes state agencies except higher educational institutions.
- Universities/Comm-Techs - includes state higher educational institutions, such as the University of Connecticut, the University of Connecticut Health Center, the Connecticut State University system, or one of the state's Regional Community-Technical Colleges.
- Municipalities - This category includes Connecticut cities and towns, school districts, local housing authorities, or regional quasi-public entities. In addition, a judgment was made to include local economic development authorities or corporations in this category when a particular municipality benefited from a project within its borders.
- For-profits - includes private sector, for-profit firms.
- Non-profits - includes 501 (c) (3), not for profit organizations.
- Multiple recipients - this category captures multi-sector projects that are cooperative by nature. Examples include joint research and development efforts involving state universities and private sector firms.
10) If Muni, Which? (Municipality) - This field identifies the specific city or town that is receiving the bond funds. It is filled in when the recipient is a municipality.
11) Project # if avail (Project_Num) - This field contains the project number for the particular bond project. It is typically assigned by the state agency that is requesting the funds and is used for internal purposes. Many state agencies do not assign project numbers, so this field is often left blank.
12) Use of Funds? (FundsUse) - This field contains a list with seven pre-set choices to indicate what the bond funding is purchasing. Analysts in the Office of the State Comptroller made judgements from a group of choices that included:
- Buildings, facilities and grounds - primarily includes construction of buildings as well as remodeling or renovation projects, and improvements to property and grounds.
- Land acquisition - includes the purchase of land or property development rights.
- Equipment or information technology - Includes the purchase and installation of equipment, machinery and information technology.
- Infrastructure - Primarily includes construction or improvements to roads, bridges, dams and sewers or sewage treatment plants.
- Studies or research and development - Includes various types of studies, evaluations and assessments as well as research and development activities.
- Environmental remediation - Includes various types of environmental clean-up activities.
- Miscellaneous/other - Includes activities that do not fall within any of the other categories listed. Examples include seed money for a small business loan fund or funds for job training.
13) Program Area (ProgramArea) - This field contains a list of 13 pre-set choices that identify the specific program category under which the bond project falls. Analysts in the Office of the State Comptroller made judgements from a group of choices that included:
- Education or libraries - includes projects related to schools - primary, secondary and higher education - as well as libraries.
- Environment - includes projects promoting clean air or clean water, various environmental improvements and flood control activities.
- Parks, recreation and open space - includes projects that develop or improve parks, create recreational opportunities and increase the amount of land preserved for open space.
- Housing - includes projects related to developing or improving various types of housing.
- Economic development - includes projects that involve various economic development activities that promote business growth and job creation.
- Urban or community development - includes development projects that promote community revitalization within a specific municipality or region.
- Health, social or human services - includes activities involving health (clinics or hospitals) and various kinds of human and social service programs, including child care centers, senior centers, youth organizations and multi-purpose human resource centers.
- Corrections - includes projects involving prisons and correctional institutions.
- Justice, public safety or military - includes activities related to the courts and the justice system (other than prisons), the state police and the National Guard.
- Transportation -includes transportation-related projects involving roads, bridges, highways, ports, railways, and airports.
- Cultural resources - primarily includes projects related to the arts or historic preservation.
- General government/administration - includes general government administrative activities and items where several agencies are involved and it is not easy to separate out allocations by program area.
- Other - Includes activities that do not fall within any of the other
14) Total Earmarking (Total_Earmarking) - This field contains the total amount of bond funding authorized by legislation for a whole category of bond projects (e.g., grants or loans to municipalities for land acquisition).
15) Allocation Amount (Allocation_Amount) - This field represents the amount of bond funding the State Bond Commission is approving (allocating) to an agency for the specific project in question on a particular meeting date.
16) Previous Alloc (Previous_Allocation) - This field represents the amount of the “Total Earmarking” (see above) that has already been allocated by the Bond Commission for earlier projects.
17) Bal Unallocated (Balance_Unallocated) - This field represents the amount of the “Total Earmarking” that remains once “Previous Allocation” amounts are subtracted. An example follows:
|Total Earmarking||$14,000,000||(Total authorized for a category of projects)|
|Previous Allocations||- 2,870,000||(Amount already allocated to earlier projects)|
|Balance Unallocated||$11,130,000||(Balance remaining that can be allocated)|
18) Any use of previously allocated funds? Check = Yes (Any_Previous) - This field is a check box and a check means “yes” or “true.” When checked this field indicates that some or all of the bond funds currently requested have been previously allocated for other projects, but have not been used. Some of these bond projects have been completed under budget and others have been canceled altogether. Therefore, the funding becomes available for reuse or reallocation with Bond Commission approval.
19) If yes, how much? (How_Much) - This field, related to the previous one, contains the portion of the “Allocation Amount” that is made up of funds that are being reallocated for the project in question. This information is important because it helps to avoid double counting allocations over time.
20) Fund # (Fund_Num) - This field contains a four-digit number of the “Bond Fund.” This number is part of the code that the state uses for accounting purposes. Please see the table called “fund names” in the database for a complete list of bond funds and their corresponding numbers.
21) Agency # (Agency_Num) - This field contains a four-digit number that indicates the state agency that is receiving the bond funds. For example, “6100” is the agency number for the Department of Social Services. Agency numbers are also used as part of the state's accounting code. Please see the table called “agencies” in the database for a complete list of state agencies and their agency numbers.
22) SID # (SID) - SID stands for special identification number. It is a three-digit code that the state uses for accounting purposes.
23) Description (Description) - This field contains a brief description of the project that has received State Bond Commission approval.
Return to Bond Allocation Database Information Home Page
Return to Comptroller's Home Page