Peaks to Prairies Home

Comments to:

WB01639_.gif (1443 bytes)

Programs DB

 Meeting Discussions
Policy & Vocabulary
Database Model
Action Plan
Database User Guide

Last updated:
June 26, 2001



P2Rx Programs Directory 101 

User's Guide for the Access Database Template

draft updated June 2001

Field Key  |  Notes


The P2Rx Programs Database was initiated as a national project of the Centers in October, 1999. The purpose of the project is to create an on-line tool for locating business assistance and services -- maintained locally, available nationally. The Programs Database is also a storage place for "Where to Go for Help" information needed by P2Rx Topic Hubs.

A template (created by Greg Geyer and Fernando Parra) for Centers to use in creating their Programs Database was completed and distributed in December 2000. The template included an Access database and Access-based data entry form. In May, 2001, Fernando Parra created an on-line data entry form for the template. This guide defines fields in the database for common understanding and uniform data entry practices among Centers. It focuses on using the on-line data entry procedure and is intended to be useful for a data entry person as well as the database administrator.

Through use, needed modifications will become evident to enhance, standardize and simplify use of this database and use guide. Please forward all questions, comments and suggestions to Fernando Parra ( and Wesley Fleming (



The main administrative area for the database shows an alphabetical listing of all programs in the database. User's can search for, view the full record, edit or delete programs from here. New programs can also be added from this screen. The "edit" function takes users through the same steps as the "add new" function.

Note: data entry into some fields is required. This is indicated with an *. You will not be able to move to the next screen until all required fields are filled.

"Edit or Add Your Organization" Data Fields (presented on two screens)

Screen 1:
*Organization - Name of the program being described. Spell out all words in the name; the agency acronym can follow in parenthesis.

Division Name - a smaller or large unit of the organization, such as a bureau, unit, institution, etc. Spell out all words in the name; the acronym can follow in parenthesis.

Email - Organizational email only. There is space provided later for email addresses of individual contacts within the organization.

*Phone - This is a main contact number for the organization (other space is provided for phone numbers of specific contacts within the organization). If the organization maintains only a web presence, enter "n/a" into this field. (Also see "Hotline")

Extension - If the main phone # has an extension, record it here.

*Fax - Self-explanatory. If the organization maintains only a web presence, enter "n/a" into this field.

Hotline - Some organizations have toll-free phone numbers or hotlines. This field is intended for that number. If the main number and the hotline number are one and the same, enter the number twice - in "phone" and in "hotline". If a toll-free number only works "in-state," note this following the number.

Time Zone - Time zone where the organization is located; daylight savings time is not taken into account. Choose from "Atlantic" "Eastern" "Central" "Mountain" "Pacific" "Alaskan" or "Other".

*Service Scope - in general. Controlled vocabulary = national (1 or more entire countries), state (1 or more entire states), region (not currently defined), local (one or more local areas or cities). We discussed the benefits of specifically identifying the area served, i.e. "EPA Region 5" or "greater Chicago area" or "Gallatin County". A big benefit would be searchability. The challenge is controlled vocabulary to adequately describe all the possibilities. More research and discussion is needed.

Screen 2:
*Address 1 & 2
- both are available if needed to fully describe an address. Only "Address 1" is required to be filled in. The mailing address should be entered here.

*City, *State, *Country - self-explanatory, choose from pull down list for state, spell out country. United States is the default entry for country and can be changed by typing over it.

*Zip - space is provided for the four digit extension but it is not required. You just need to enter the digits not the hyphen.

*Profile - brief description of the organization. The organization's mission statement, a paragraph from their brochure, major services they offer, etc. It would be ideal if the organization would edit or compose this. There is a maximum of 255 characters allowed in this field even though the box appears to allow more. Note: Data entry can take place without filling in this field but records with no profile information will not validate (appear when data is shown).

*URL - If the organization has a web site, enter the web address here. Use the full address, starting with "http://..." Endings like "...default.htm" or "...index.htm" are usually not required. Check all URLs in a browser to insure accuracy. If the organization has no web site, enter "n/a" into this field.

Graphic URL - This field is intended to store the web address of the organization's logo - a piece of data that you may display when you are showing the organization's information on a web page. (Also appears when you view a record.) Include "http://..."

Sub-area: "Contact Info"
More than one contact can be added for each organization. While a contact is not required, Centers are strongly urged to include at least one "primary" contact for each organization. This greatly increases the value of the information being presented. If an organization has several contacts, only one may be designated as the "primary contact". In this area, you may edit existing contacts, add new contacts or delete incorrect contacts.

Primary Contact - check box. Designate only one contact as the primary contact for an organization.

Prefix - Name prefixes such as "Mr." "Ms." "Dr." etc. No controlled vocabulary, not required.

*First Name, *Last Name - If a contact is entered, at least first and last name information are required. P2Rx recommends adding at least one organizational contact.

Middle - The middle initial of the contact may be listed.

Name Suffix - Degrees or family information like "Ph.D." "Jr." etc.

Title - The use of this field is also recommended. It will help searchers put the contact into context of the organization.

Address, City, State, Zip - As defined under "organization;" fill these fields only if individual's address is different than that listed under the main organization.

Work Phone - The direct phone for this person. If the individual phone is the same as the organization main phone, this field can be left blank.

Work Extension - Phone extension. This field can be filled in and the "Work Phone" field left blank, e.g. organizations with one main number and extensions for various individuals within the organization.

Mobile Phone - Cell phone number

Fax Number - Leave blank if it is the same as "Organization Fax Number" above.

Email - While this field is optional, its use is strongly encouraged.

If you want to add another contact for this organization, click "submit" and another blank contact form will appear (also related to the original organization). When all contacts for the organization have been entered, choose "Click Here When Done Adding Contacts"


Sub-area: "Keywords"

*Program Type - More than one choice is allowed. Choose from the checklist of approved terms including: Federal Government, State Government, Local Government, Educational Institution, Non-profit, Vendor, Consultant, Small Business Assistance Program, Manufacturing Extension Program, Recycling, Small Business Development Center, Energy Efficiency, Materials Exchange. If you would like to propose another program type, choose "Add New Program Type." Entries will be added to the "Unreviewed" list and P2Rx librarians will consider adding the term or suggest alternatives.

Topic Hub Keywords - Use only to relate an organization to a topic hub. Checklist of terms specifies which P2Rx topic hub an organization is related to. This field allows a specified topic hub to pull information from the Programs Database to appear in the "Where to Go for Help" section. The publisher of a topic hub includes organizations of national importance to their topic in the programs database. These organizations are coded as "Service Scope - national" as well as by Topic Hub Keyword. Organizations with a Service Scope other than "national" may also be indicated with topic hub keywords to fulfill regional "Where to Go For Help" contributions.

Authority and Free Keywords- Optionally choose terms to further describe the organization's expertise in materials, processes or sectors or to describe a service provided by the organization in more detail than allowed by the "Services" field (see below). Authority Keywords are words currently included in the P2 Thesaurus. They are browsable in drop down alphabetical lists. You may also add Free Keywords (not currently included in the P2 Thesaurus), so P2Rx librarians can consider adding them to the Thesaurus or suggest alternatives.

*Services - Describes the services offered by an organization. Services are split into three components: Area of Expertise (of the organization), Resources Offered (by the organization), Program Clients (primary audience of the program being described). Choose a term or multiple terms for each component from the checklists provided. These controlled vocabulary terms are intended as broad, general descriptors. More specific descriptors can be added by using authority or free keywords as described above. If you find the current terms do not adequately describe the area of expertise, resources or clients of an organization (in the broad sense), choose "Add New Service" and describe the addition you suggest. P2Rx librarians will consider adding the term or suggest alternatives.



Database Structure and Quality Control

The template can be modified and enhanced to fit the needs of individual Centers. However, since each Center's programs database has potential to be used in national projects, a collective development effort is desirable. Please contact Kathy or Fernando if there are elements you choose to change. The rest of the group may benefit from your insight and it will be easier to relate databases to other P2Rx projects and from one Center to another if we have the same basic information structure.

Several minimum standards are proposed to make integration of regional collections into the National Programs Directory more uniform and useful. Three areas have been identified for data entry quality control:

  1. Scope of data collection - as a minimum, centers should identify and describe the state P2 programs, the state regulatory agency and the small business ombudsman for all states in their region. Others should be added from the list of "Program Types" (Federal Government, State Government, Local Government, Educational Institution, Non-profit, Vendor, Consultant, Small Business Assistance Program, Manufacturing Extension Program, Recycling, Small Business Development Center, Energy Efficiency, Materials Exchange) as dictated by the center's partnerships and the P2Rx marketing plan.
  2. Validation - For programs to appear in the National Programs Directory, all "required" fields must be filled. In addition, "Profile" information must be included (written and/or edited by the organization being described) and at least one individual contact name must be listed with contact data (like email address). Organizations considered to be key contacts for a topic hub must have appropriate "Topic Hub Keywords" and "Service Scope" entered.
  3. Keywords Allowance - The permitted number of authority and free keywords is unlimited when organizations are entered by P2Rx Centers. If organizations are asked to self-identify with processes, sectors and materials, give them a 15 word limit. This can be overridden by the P2Rx Center if necessary but will encourage organizations to consider their most valuable contributions rather than choosing "all" which is unhelpful for searchers.

The database consists of the following tables (data storage):

  • Contacts
  • GeoServices
  • Organization
  • OrgstoProgramType (relational)
  • PeopletoOrgs (relational)
  • Program Types
  • TimeZone
  • Switchboard Items (utility for switchboard navigation)

In addition to the on-line data entry forms, the database consists of the following Access-based data entry forms:

  • Switchboard (utility - opens when database is opened)
  • All (accessed from the switchboard "Add Programs" choice)
  • Contacts
  • Organization
  • OrgtoProgramtype Subform

Macros: LinkPrimary (A macro used within the "all" form that links primary contact when check box is selected)

The on-line forms are written using Cold Fusion to interact with the database on a web site. It is recommended that all related files be stored in a password protected "Admin" directory on the web site. The Access database can be upgraded to SQL Server (a more robust database) if site traffic warrants. Consult with your ISP for options available to you.