This project is intended to expand your understanding of databases, and to provide an opportunity to study how the information system of an organization is created and customized to better serve its needs.

The task is to build the database and information system for an imaginary Seattle health clinic to be called the Central District Clinic (CDC). Though the clinic provides a variety of services and referrals, this assignment will be limited to the clinic's lab testing activities. It performs drug testing as well as testing for a variety of diseases: HIV, AIDS, Hepatitis B, etc. An important aspect of the database system is to assure privacy for the clients. A receptionist and several health professionals staff the clinic. The database system is intended to support their daily activities.

The focus of the database support is on processing clients who come to the clinic to have a test performed. During their visit they will give personal information, be seen by a health professional and provide one or more fluid specimens for testing. The actual test is not performed at the CDC, but rather is performed at one or more labs in Seattle. This means that the specimens must be sent out; when they are returned the client is notified. It is assumed that the CDC is a public health clinic, that its testing is free and its employees are volunteers.

These steps in the process can guide the design.

Arrival: When a client arrives, the receptionist interviews him or her to gather a small amount of personal data that is entered into the client’s record. If the client has visited the CDC before, the receptionist simply verifies the information in the existing record. The receptionist is responsible for scheduling the client with one of the health professionals.

Meeting With The Health Professional: After the client has waited for the health professional to be free, the health professional interviews the patient to find out a brief medical history and the type of test(s) sought. The health professional fills out a form that is a record of the clinic visit and the discussion. The form has space for "chart" information as well as check boxes for the tests to be given, including an "exceptional" situation.

If tests are ordered by the health professional, i.e. one or more boxes have been checked, then a specimen (urine, blood, etc.) will be collected from the client to be sent to the lab. A label must be generated to attach to the sample. The label must include the pertinent information about the test and the person requesting it. To assure privacy a tracking number is used whenever a client must be identified outside the clinic.

Lab Manifest: Each day the lab test samples will be grouped together by test requested. These are compared against a computer generated "lab manifest" that lists all of the requested tests by tracking number. The test specimens are sent to the appropriate lab with a paper manifest, and an electronic version of the manifest is also sent to the lab. An employee will print the manifest, check that all specimens exactly match, and ship them to the lab with the manifest.

Results Returned: The lab will return the manifest. The results of each test will be filled in. The outcomes are: positive, negative, failed --must be repeated, inconclusive – and exceptional. When the results of all tests are available, the letter to the patient is generated. In the case that the test failed, the client is encouraged to return to provide another specimen, which will produce a separate visit transaction. Once the letter is generated, the visit is "closed". To assist the clinic’s administration and public health officials, summary statistics are generated each quarter. These include the number of clients visiting, and summaries of the outcomes, i.e. number of positives, negatives, etc. in each category.

The processes described above imply the basic database design components needed to support the clinic’s daily operation. These components are numbered in the following description.

From the Arrival description, it is evident that a Client table (1) is required. This contains the client’s name, contact information and birth date. Additionally, a form (2) is required to assist the receptionist in recording the information. The form should be attractive, be embellished with the lab’s name and look suitably professional. Once the form is completed, the receptionist will schedule the client with a health professional, and so provision must be made for this activity.

From the Meeting description, it follows that there must be a Visit table (3), containing the pertinent information of the visit. This will include the patient’s ID from the Client table, fields for each test and the tracking number. The tracking number is not the unique record ID, which is also required, but a multi-digit number used to identify the specimens. This number preserves the anonymity of the client while the specimens and tests are being processed outside the clinic. One tracking number is associated with each visit of a client, and so if more than one test is ordered, the same tracking number will be used on each. There should be a field to record the outcome of the test, once it is returned. A form (4) for entering the data will be created for this table. It will include a check box for each test to be run. For each test requested, a label form (5) must be printed to be affixed to the specimen. This label includes the clinic’s name, address and phone number, the date and the tracking number. The "chart" information in the Visit table includes notes fields for "presenting symptoms", "medications" and "remarks". Additionally, the first and last name (with certification designation, as in Bill Jones, MD or Carol Jones, LPN) of the health professional who sees the client is included on the Visit data entry form. This implies that there is a health professional (Doctor) table (6). This table includes a health professional’s ID number, which is used in the Visit table. In addition to the ID, there is the professional’s name, certification designation, contact information, social security number, license number and a notes field. There is a custom form (7) for entering data for this table that is attractive and consistent in form with the other tables.

From the Lab Manifest description, it is necessary to create (8) a table for each test to be sent to the labs with the specimens. Each table, known as a Lab Manifest, lists the tracking numbers of all requests for that test for a given day. Though a paper copy is sent with the specimens, an electronic copy should also be sent to the labs to be filled out with the results. So, the Lab Manifest table contains a field for the results of each test, of which there can be the four outcomes.

The Results Returned description explains that the lab will return the Lab Manifest table for all tests submitted to it, and this should be done electronically. The Lab Manifest table will come to the receptionist who will transfer the file to the database system. The results fields will have been filled in for each test.

From the Notification description, there must be a letter (9) generated to the client once all of the tests from a visit have been received. The letter reports the results of each test. If any test’s outcome is "fail" or "inconclusive", a paragraph is inserted recommending that the client return to the lab for a retest. Notice that since a single letter is generated for (possibly) multiple tests, sorting the merged Lab Manifest table on the tracking number field will result in grouping together the tests of one visit. Once the letter and a mailing label (10) have been generated, the record can be closed and moved from the Visit table to the Visit Archive table (11). Similarly, all returned Lab Manifest table records that have been reported can also be moved to the Lab Manifest Archive table (12). The summary statistics report (13) is generated from the Lab Manifest Archive table. The statistics are (a) the number of visits, (b) the number of tests, (c) the numbers of tests of each type and the outcomes. In addition, there should be a summary of the number of visits by month for each month in the quarter.

Finally, because of privacy practices in the medical field and possibly state laws, the database must be password protected. Further, it should contain an "auto close" facility that exits the database when no activity has occurred for a while. This will protect unintended access when the health professional leaves

the computer.

The following components of the CDC information clinic were identified in the Needs Analysis section:

Client table

Client table entry form

Visit table

Visit table entry form

Specimen label

Medical Professional table

Medical Professional table entry form

Lab Manifest table for each test, created daily

Notification letter to client containing test results

Mailing label to client

Visit Archive

Manifest Archive

Clinic quarterly statistics

Notice that key aspects of these components, given in the Needs Analysis above, are not repeated below in the task specification. Also, there is not sufficient time to complete all aspects of this design.

There are three basic tables in the CDC database systems: Clients, MedPros and Visits. The conceptual database for these three has the structure shown in the associated "Relationships" diagram. The Clients table contains data about the people who come to the clinic. The MedPros table contains information about the nurses and doctors that staff the clinic. The Visits table contains information about a specific client’s visit to the clinic in which he or she saw a specific medical professional. The client information and medical professional information of the Visits table is referenced using the primary key fields of those two tables. Each table has a set of fields defined. Specifying those fields is the first task in defining the CDC database.

The Clients table should have fields as specified in the illustrated table. The field names are given, as are the properties that the fields should have. A convenient way to define a table like Clients is to use the Access table wizard. The wizard doesn’t know about the Clients table, of course, but it does know about tables that contain similar information. For example the Mailing List table contains contact information like names, addresses and phone numbers. It is possible to build the Clients table by using fields from the Mailing List table and renaming them so that they have the right name for the Clients table. Since the fields will generally have similar properties in the two tables, e.g. a phone number should have a similar structure, this is a fast way to get started. Of course, once the basic fields of the table have been borrowed from the Mailing Lists table, it is necessary to inspect each field’s properties to see that they match what is required for the Clients table. Notice that the date field must have a default value that is the current date.

The next table is the MedPros table. It must contain information about the volunteer health professionals that staff the clinic. A good standard table that could be used as the initial settings for the MedPros table would be the Employees table from the Access table wizard. Again, this will only give a starting structure for the table. The exact properties of the fields will have to be checked and if not quite right, must be adjusted manually.

The Visits table contains a unique key field, as usual, plus a field named ClientID and a field named MedProID. If these two names are entered exactly this way in the Visits table, then the Access system will figure out that the fields refer to the key fields of the other two tables, as we intend. There is also a date field in the Visits relation. The date field is to have as its default value the current date. The tracking number is the unique identifier that is used to label the client’s information when it is outside of the clinic. Then there is a series of field pairs of the form <test>R and <test>O used to request a test and to record the outcome of that test. There will be four outcomes: positive, negative, failed – must be repeated; inconclusive – and exceptional. The exceptional case is for visits that do not result in a test request, but nevertheless require further attention by the clinic personnel. An example is a person who is to be referred to another agency.

Task 1. Note the characteristics of the table’s fields described above and set up the three tables: Clients, MedPros and Visits.

You may build the tables from scratch, or pirate and rename similar fields from other tables available with the Table Wizard. In any case the fields must have exactly the attributes listed above. [The exact field names should differ from those shown, except that you may leave ClientID, MedProID, VisitID or Tracking as given, since they are involved in following instructions.]

Task 2. Create forms for Clients and MedPros tables to simplify entering data into their fields.

Task 3: Enter at least four fictitious clients and two fictitious medical professionals into the two tables.

You may choose backgrounds and colors that appeal to you. Do not spend time making the Clients form attractive, since it will be reconstructed. The MedPros table can be made attractive by giving the clinic name, address and logo, and reorganizing the information so that it is more convenient to use. In fixing up this and any automatically generated form, consider revising the organization of the information to make it easier to use. Decide if the field names should be right justified or left justified, etc.

Task 4: Delete the Clients form used in Task 3. Using the idea of the linked form covered in Lecture 26, use the Form Wizard to recreate the Clients form with a linked form, named Queue, that contains the three Visits table fields, VisitID, MedProID and ClientID. Using the Queue mechanism, create at least 4 Visits records by pairing your four clients with the one or the other of the medical professionals. [Notice that if your Queue form comes "filtered"; remove the filtering by right clicking on the Queue form.]

Task 5: Construct a Visits form by using the form wizard. All Visits fields except the ClientID, MedProID and <test>O fields should be included. (Because of bugs in the wizard, moving all fields and then "returning" the <test>O fields will not produce the right result. Explicitly move each field needed.) In addition to the Visits fields, include these other fields:

First Name, Last Name and Degree of the medical professional

Title, First Name, Middle Name and Last name of the client

The resulting form should use the "long date" format for the date at the top of the form. It should then list the medical professional seeing the client, the client’s name, and finally the other information.

An example form is shown indicating an appropriate format. Notice that the information has been rearranged to be convenient. Further, many of the field names have been removed or changed to reduce clutter. Remember that a medical professional will use this form all day. It should be clear and convenient.

Task 6: As described in Lecture 26, add an "undo" record operation button to the client form.

Task 7: As described in Lecture 26, add the "order" command button to the Visits form. The "order" button should set the tracking number field using the logic in the following figure. As in Visual Basic, double clicking on the button causes the event handler routine for the command button to come up

Option Compare Database
Option Explicit

Private Sub cmdOrder_Click()
On Error GoTo Err_cmdOrder_Click

    Tracking.SetFocus
    Tracking.Locked = False
    Tracking.Text = “CDC” & Hex((12*(Year(Date)-Year(“11/20/99”)
        + Month(Date)) * 1000000 + VisitID)
    Tracking.Locked = True

  DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_cmdOrder_Click:
    Exit Sub

Err_cmdOrder_Click:
    MsgBox Err.Description
    Resume Exit_cmdOrder_Click
    
End Sub

Task 8: Using the query wizard construct a query, called TodayTests, that selects the following fields from the Visits table:

Date, Tracking, and <test>R fields

provided that the following conditions are met:

1. The date is today’s date
2. The tracking number is nonempty
3. One or more of the <test>R fields are checked, i.e. have the value "yes"

These conditions can be specified in the Query Design view as described in Lecture 27. Further, the <test>R fields should not be visible