Septic Management Database Template Help Document
DHEC-OCRM has developed a database template using MS Access. This template is designed to provide a demonstration of the type of management system tracking that might be required for your program. It contains fields for information that comes from DHEC's septic Permit to Construct - Certificate of Final Approval, from the septic inspection form template (found under the Ordinance Templates page of this tool kit), and from assessor's data, your municipal tax records or other municipal or billing records. A sampling of “dummy” data has been included to demonstrate basic functionality, but the database is set up to accept original input. THIS DATABASE IS FOR DEMONSTRATION PURPOSES. WHILE THE STRUCTURE AND FUNCTIONALITY OF THE DATABASE ARE SIMILAR TO WHAT MIGHT BE FOUND IN A PRODUCTION ENVIRONMENT, ERROR-TRAPPING, SECURITY AND COMMON DATABASE DESIGN PRINCIPLES HAVE BEEN SCALED DOWN TO MINIMIZED DEVELOPMENT TIME.
You must have MS Access software to utilize and modify this database. We recommend that you download the latest Microsoft Office service pack to avoid any problems utilizing the database. The demonstration database was developed in MS Office XP. To keep and modify the database you need to save it to your hard drive.
This database is made up of tables, queries, forms and reports.
Tables are the data storage components.
Queries are pre-built search parameters which allow specifically requested data groupings to be supplied to the forms.
Forms are the interfaces designed to simplify the data viewing, editing, and updating.
Reports are printable documents that are designed to interface with database tables and forms. When opened, the reports pull the appropriate data to populate the pre-built report format. In the case of this database, the Inspection Form is the only report included.
These tables, queries, forms and reports are all editable, but structural changes to these components, especially the forms, can cause the automated components of the database to malfunction.
We will briefly cover tables and queries, but the primary focus of this document will be the use of forms. In discussing the forms, we’ll make reference to a number of components of the forms. Let’s cover them briefly.
Screens – This term will be used as the primary descriptor of the forms themselves.
Text Boxes – This is a simple data entry component designed to contain text or numbers typed by the user.
Dropdowns – These components hold data and values pulled directly from database tables and/or queries. These components are used to simplify data input and to ensure that only valid values are input for specific fields of the database tables. Dropdown boxes look similar to text boxes, but feature down arrow on the right-side of the component. When clicked, the box expands to show all valid values.
Buttons – These components are used to launch screens (forms), print records or cause actions such as the editing, updating or deleting of records within the database tables.
Check Boxes – These components are linked to table fields in which YES/NO or TRUE/FALSE are the only valid values. If checked, then YES or TRUE is the value. If not checked, then NO or FALSE is the value.
Now that we’ve covered some of the key terms, let’s get started.
Using The Database
The first step is to copy the database to your PC. Then open it by double-clicking.
When the database is first opened, you will first see the “Splash” screen. Read the comments carefully. This screen has a timer which will close the screen and open the “Main Menu” screen. If you’ve finished reading the comments or for subsequent uses, just use the “SKIP” button to move past the “Splash” screen.
The next screen you’ll see is the “Main Menu”.
This screen features three buttons:
- SYSTEMS AND ACTIVITIES
SYSTEMS AND ACTIVITIES
Below you’ll see the “SYSTEMS AND ACTIVITIES” menu. It features three sections:
- SITES AND SYSTEMS – This screen is set to view and edit information about a site such as TMS, Address, Owner information, and specifics about the configuration of the septic system.
- INSPECTIONS – This screen is for viewing and input of inspection information.
- REPAIR AND IMPROVEMENT – This screen is for viewing and input of repairs and improvements to existing septic systems. It is not tied to system information stored in the SITE screen/table. It’s designed primarily for activity tracking.
All three of these sections (SITES AND SYSTEMS, INSPECTIONS and REPAIR AND IMPROVEMENT) function in essentially the same manner. Therefore, we will cover the basic functionality and operation by examining the SITES AND SYSTEMS section. We will not go into detail for INSPECTIONS and REPAIR AND IMPROVEMENT. However, screen shots will be shown.
SITES AND SYSTEMS
There are three menu options under SITES AND SYSTEMS:
- VIEW EXISTING
- EDIT EXISTING
- ADD NEW SITE
These three options function essentially the same way. There are two primary differences. First, the VIEW EXISTING screen allows for the printing of Inspection forms. The EDIT and ADD NEW screens do not. Second, the EDIT and ADD NEW screens feature dropdown boxes containing valid values which allow for quick edit and data input.
You can view site information by choosing either a TMS number from the “TMS Quick Search” or a DHEC permit number from “DHEC PERMIT Quick Search”.
You begin using the EDIT screen in the same manner as the VIEW screen. Choose either a TMS or DHEC permit number from the quick search dropdowns. Items such as “Facility Type”, “Occupancy Type”, “DHEC Code”, etc. appear as dropdowns. These dropdowns have predefined valid values which are pulled from parameter tables. We’ll get into how to edit parameter values later. To edit site information, simply choose a site, make the necessary changes, and click the “Update” button at the bottom of the screen.
You can also remove a site by choosing it using the “Quick Search” options and clicking the “Delete Site” button. The delete cannot be undone, so be careful.
ADD NEW SITE
The ADD NEW SITE screen is where new sites are added to the database. The same dropdown options present on the EDIT screens are present, but no “Quick Search” options are available because you’re adding a NEW site. When the site information data is completely entered, simply click the “Add” button at the bottom of the screen and the database will be updated.
INSPECTION and REPAIR AND IMPROVEMENT Screen Shots
The purpose of the PARAMETERS menu is to allow the user to edit the values that appear in the dropdowns on the screens under the SYSTEMS AND ACTVITIES menu.
You’ll notice that all the options that appear above correspond to one or more dropdowns that appear on the SITE, INSPECTIONS and REPAIR IMPROVEMENT screens, with exception of the “Generic Parameters” option. “Generic Parameters” is just what it says; parameters that have no specific category and/or do not require a separate table, but were necessary for use in one or more dropdowns.
For instance, if you want to add a new “Occupancy Type” to the dropdown menu that appears on the SITE screens, this is where the addition would have to be made.
All of the screens associated with these options are MS Access generated forms. They look and function differently than the screens under SYSTEMS AND ACTIVITIES, which were designed independently.
We’ll use the “Occupancy Types” as an example.
This screen allows you to edit the values that appear in the Occupancy Type dropdown. You’ll notice in the screen shot above that there are five records. You use the left/right arrows to navigate through the values.
In order to add a new value, click the button showing the >*.
This will clear the text box. You then type in the new value and click the button with the pencil on the upper left portion of the screen. This will write the record.
This option would also be used if you were editing an existing value. For instance, changing “Full Time” to “Full-time”.
To remove a value, you’ll need to navigate to the record using the left/right arrows. Once you’re at the value you want remove, you will click the Delete button on the main MS Access menu.
The value will be deleted, and not appear in the Occupancy Type dropdown on the SITE screen.
All the screens under the PARAMETER menu operate in this manner. The screens you most likely will edit are Inspectors, Pumpers, Installers, and Tank Manufacturers.
Some of the PARAMETER screens contain more than one field. Here are two examples.
“Design Types” has two fields, so adding a new value would require the completion of both fields in order to work properly: System Code and Design Type.
The second example is a little more complicated. “Generic Parameters” also has two fields, but this particular screen represents a table in which the values are brought to the SYSTEMS AND ACTIVITIES screens via pre-built queries.
The PARAMETER_TYPE text box identifies which query the corresponding value is associated with. Editing of these values requires familiarity with the GENERIC PARAMETERS table and associated queries.
Database Tables, Queries, Forms and Reports
Before being able to view the actual tables and queries, you’ll need to “UNHIDE” the database window.
The UNHIDE window will appear, and you’ll choose the Septic database.
You’ll then see this window.
From this window you can view all tables, queries, forms and reports contained in this database.
WARNING: CHANGES TO THE STRUCTURE OF ANY OF THESE COMPONENTS CAN RESULT IN A MALFUNCTION OF SCREEN FUNCTIONALITY.
If you want to close this window, you must “HIDE” the window using the same procedure used to “UNHIDE” it. If you simply click the “X” in the top corner of the window, it will close the database and all tables, queries, forms or reports you have open and you will lose all unsaved data.
If you are comfortable working with MS Access, modifying this database to suit your particular environment or to meet the requirements of your program is encouraged. Additional tables, queries, forms and reports can be added without disrupting the existing functionality. This database can also be interfaced with your own existing databases.
Beyond simple additions, you might be inclined to keep the basic structure of the database tables, but build your own queries, forms and reports. This database uses VBA (Visual Basic for Applications) to create much of the functionality. However, MS Access has a number of “Wizards” to assist the user in creating their own customized components. Having a working knowledge of VBA will only expand these possibilities.
Finally, the basic structure of these database tables can be applied to other database systems such as MS SQL Server or Oracle.
As stated in the beginning of this document, the primary goal of this database is to introduce you to the possibility of using a database to help manage your septic management system.
For additional information, contact: (803) 898-4329 Fax (803) 896-0645