Project 4

Snyder 24 May 1999

Project 4
"Here's To Your Health"

This is the second part of Project 4, construction of the Central District Clinic's information system.

The three tables constructed in Part I are essentially unconnected. (The same names have been used for the ClientID and the DoctorID in the tables, but these are not yet linked together.) Further, there are operations mentioned in the Needs Analysis that require the tables to be "connected". An example is that when a client arrives at the clinic, the receptionist "queues" that person to be seen by a health professional, in effect initiating a Visit table record following the completion of the Clients record. Our database should support such "cross table" operations. Finally, certain tables, such as the laboratory manifest tables, are "derived" from the other tables. It is necessary to create a table from the data in another table. These operations are the subject of this part of the assignment.

Relationships

It is our intent that if in the Visits table a ClientID field has value v_c in it, then the client involved in the visit is the client of the Clients table with ClientID v_c. References like this are used to avoid having to repeat all of the personal data about the client in the Visits record. A (relational) database system is designed on the assumption that such relationships exist (why else would it be called a "relational" database?), and facilities exist to make these kinds of connections.

One aspect of the connections is how many things are being "related". For example, the ClientID value in the Clients table is unique, but there could be many occurrences of that ClientID value in the Visits table, since the client may return to the clinic more than once. One item in Clients is related to many items in Visits. This would be called a "one-to-many" relationship in database jargon. It is not necessary to become an expert on such things, but don't be surprised to be asked about this topic by the wizards.

The Access database software provides a tool for defining relationships. (Find "Relationships" under Tools.) As an interesting introduction, open an existing database and look at its relationships. (The Students and Classes database is good for this, and if you do not have it around, it is possible to have the wizard set it up for you in a moment.) To relate the ClientID field of Clients and the DoctorID field of Doctors to the fields of the same name in Visits, click on the Relationships tool, load in all three tables, and drag the ClientID of Clients to the ClientID field of Visits. Leave the relationship dialog box unchanged. Similarly, drag DoctorID from Doctors to Visits. This will establish the relationships.

Task 4.9: Set the two relationships just described.

Queuing The Visit Record

Once the client’s record has been filled out, the client is "queued" to see one of the health professionals. Queuing is accomplished by creating a new Visits record with the client’s number and the health professional’s number in place. The health professional will fill in the remaining fields (except the date, which you should have set in the last assignment to be generated automatically.) The queuing activity should be performed by the receptionist from the Client form, but this facility must be implemented. Since there is already a default Client form, delete that form (with the right click menu) in preparation for constructing a new Client form with the queuing property.

Proceed as follows to implement the Queuing facility:

Click New from the form tab, enter the Form Wizard, choose the Clients table and move all of the Clients table’s fields into the form.

Switch to the Visits table, and move the VisitID, ClientID and DoctorID fields to this form.

When asked how the data should be viewed, choose "By Client" and the "Linked Form" option.

Select your standard form background.

Name the second linked form "Queue" and finish the wizard.

Open the form, and notice that at the top of the form is a "Queue" button. Click on the Queue button and notice three fields, two of which (ClientID and DoctorID) can be filled in.

Assign a client to a doctor by setting the fields appropriately. (This uses the fictitious data you entered in Part I) Advance the database to the next record. This sets the Visits record for this visit.

Close the Queue window and the Client form, and check the Visits table to see that this visit record has been created.

Task 4.10: Reconstruct the Client Form to have the Queue linked form, as just described. Make the Client form aesthetic and consistent with the Doctors form (Task 4.7).

Completing The Clients Form

In addition to making the Clients form attractive, its completion requires that a "Clear" button be placed on the Client form to aid in situations when the data entry operation gets messed up.

Select a command button from the Tool Box bar and place it on the form. This action will immediately put you into the Command Button Wizard.

Explore the various categories to see what kinds of buttons could be chosen, and then select the Record Operations category and the "Undo Record" option.

When asked to choose what appears on the button, explore the various picture options. The choice is yours. It can be the text "Clear" or the eraser, but it should be appropriate. (The toilet picture is probably not professional enough for this application!)

Name the button appropriately, e.g. cmdClear or cmdUndo.

After closing and saving, try out the "Clear" button.

Notice that it doesn’t work to delete records that are already in the database, but it will clear information that has been added to an existing record.

Task 4.11: Add the "Clear" button to the Clients form.

The Visit Form

The Visit form is the most important form for the clinic, since it is probably the form most extensively used. In addition to being attractive in the same style as the other forms, the Visit form must be properly constructed to fulfill other aspects of the design. To assure that the Visit table is ready for its form to be constructed, assure that it has these properties, some of which may differ from your result in Part I:

Tracking is a text field with blank as its default field, i.e. " ".
The date field is automatically generated.
There are "Symptoms", "Medications" and "Notes" fields whose field size is 255 character.
There are yes/no fields (TestR) for requesting the 7 tests and the exception.
There are number fields (TestO) for the outcomes of the 7 tests. There are four outcomes: negative, positive, failed and inconclusive.

There is also a schema for the Visit table available on the web with these properties.

In the Visit form the client’s name, not his or her ID, must be used, so that the health professional can refer to the client by name. Further, the health professional would like to be known in the "first name/last name/credentials" form, e.g. Larry Snyder, LPN. These names are not stored in the Visit record, but they can be found by the database system using the ID fields. [This is one of the reasons for having established the relationships.] In constructing the form, the wizard will be directed to track down this information.

Enter the Form Wizard and select the Visits table. Referring to the screen snapshot below, proceed as follows: Move all fields except ClientID, DoctorID, and the seven TestO fields. It is best if the TestR fields come last, the three big text fields go together, and the ID and Date are at the top. Switch to the Doctors table and move the doctor’s first and last names and the title initials. Later the form should be edited so that the health professional's name follows the date and is arranged horizontally. Switch to the Clients table, and move the client’s first, middle and last names. Later, the form should be edited so these follow the health professional’s name and are listed vertically. Specify to view data By Visit. Reply that you want a single form, that is columnar and in your standard background. These features are illustrated in the companion form, which has not yet been fully beautified.

Additional modifications to the Visit form should be made. Enter the Design view. Change the tracking field to be locked i.e. so the user cannot enter data in this field. This simply preserves the integrity of the number. To make the change, right click on the Tracking field, select properties at the bottom of the pop-up menu, and select the Data tab. The "Locked" property is listed, and should be changed to Yes. Similarly, set the Date field to have the "long date" format. Finally, as a convenience include a button to skip to the end of the Visits table. This is useful since the most recently queued clients will be stored at the end of the table. The skip-to-end button is a command button constructed as was done with "Clear" above. This time, from the Command Button Wizard, select Record Navigation and Go To Last Record as the options. Label and name the button appropriately.

Task 4 12: Construct the Visit form according to preceding description.

Tracking Number

The tracking number is used to identify the samples and tests requested when they are outside the clinic. The number is computer generated, and should be unrelated to the client, e.g. it should not be generated from letters in the client’s name. The number that we will use has the structure

CDCxxxxxxxx

where the xxxxxxxx is a number constructed from the date and the sequence number converted to "hexadecimal", an internal data representation format used by the computer. For computing the xxxxxxxx we use

Hex((12 * (Year(Date) - Year("5/5/99")) + Month(Date)) * 1000000 + VisitID)

which computes the number of months since this software was written 12*(Year(Date)-Year("5/5/99") + Month(Date) times 1000000 plus the VisitID. The purpose of including the months x 1000000 is simply to incorporate more data into the number than is included in the VisitID alone. The purpose of expressing the number as hexadecimal is so that it is not trivially decoded by humans, though it can be easily inverted. That is, given a tracking number it is possible to figure out the VisitID and the Date. But to go further, the Visit table is needed. Better forms of encryption are available, as has been discussed in class, but this will suffice for this assignment.

The tracking number will be generated in response to clicking on an "Order" button. This is expected to be the final action of a visit.

The first step in implementing the tracking number generation and "Order" button is to place a command button, as described above with the "Clear" command. This time, however, select the Form Operations, Refresh Form Data selections. Label the button "Order" and name it something standard like cmdOrder. Finish the Wizard, resize and position the button if necessary.

Like the command buttons used in earlier assignments, this button is associated with an event-handler. The main difference is that cmdOrder_Click() event handler already has (error handling) code in it, and will look like the following when you right click on the command button and select Build Event.

Private Sub cmdOrder_Click()

‘ Code to set tracking number goes here

On Error GoTo Err_cmdOrder_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_cmdOrder_Click:

Exit Sub

Err_cmdOrder_Click:

MsgBox Err.Description

Resume Exit_cmdOrder_Click

End Sub

You do not have to understand what this code is doing.

In the position indicated by the comment in the text above, you must enter the code to generate the tracking number. The proper code is

Tracking.SetFocus

Tracking.Locked = False

Tracking.Text = "CDC" & Hex((12 * (Year(Date) - Year("5/5/99")) + Month(Date)) * 1000000 + VisitID)

Tracking.Locked = True

What is happening in this program text is that the focus – the control that is being processed and usually being highlighted – is set to refer to Tracking. Changes cannot take place on a form until it has focus, which is why you must click on a field before you make any editing changes to it. Next, the code unlocks the control. It then assigns the right value to the field and locks it again so that it cannot be changed. (Remember, no VB6.0 statements can be multiline.)

The tracking number cannot be connected to the client except through the Visits relation. (Of course, there must be some way to connect the test and its results to the client, or it wouldn’t be possible to return the results.)

Task 4.13: Add the Order button and the tracking number logic just described.

Task 4.14: Using the facilities constructed so far, generate at least six visit records so that there are at least three tests requested for each type of test over at least two different days. Perhaps the easiest thing to do is generate the data and then go back and edit the dates to achieve these requirements.

Lab Manifest

At the end of each day the tests of each type that have been requested are collected together into a Lab Manifest. This table includes the date and tracking number for each test requested. (It is assumed that different labs might perform different tests.) The Lab Manifests are tables, one for each test, derived from the Visits table using a database query. Queries are usually written in SQL, but since the queries needed for the manifests are so simple, a wizard can do this for us. Set up a query-produced table for each test as follows:

Enter the Query tab and click New.

Request the Simple Query Wizard.

Apply the wizard to the Visits table. Request the Date field, Tracking field and the two fields for the test, say DrugR and DrugO. (Recall that TestR is for the test request and TestO is for the outcome.)

Give the query a name like LabManTest.

Complete the wizard and notice the characteristics of the table that has been produced.

The query table contains more information than is needed for the lab manifest. Specifically, all that must be transmitted to the lab is the set of tracking numbers for those Visits records that selected the Test. So, enter the query in the Design view. Notice that it shows the fields of a table in yet another form. This structure is easy to use, however.

The goal of the query modifications is to print out for the LabManTest table only those records that have selected this test, i.e. that have a "yes" in the TestR field. To accomplish this selectivity, simply type "Yes" in the Criteria field of the TestR column. This tells the query editor to modify the query to restrict attention only to records with a "Yes" in this column. All others are ignored. Since the resulting records will all be the same in this field, i.e. the box will be checked, they don’t have to be displayed. Clear the check in the Show box for this field.

Another aspect of the LabManTest query must be edited. Specifically, if the medical professional requested some test fields, but didn’t actually "Order" the tests, then the Tracking field will still be blank. Such records should also be eliminated. This is accomplished by editing the Criteria row for the Tracking field, entering the text, <>" ", which is read, "is not equal to blank". Thus, the query will be revised so that nonblank records only are considered. The revisions to the query can be saved.

Now, before exiting the query editor, right click and select the SQL View. (Recall that you worked a little with SQL in Assignment 5.) It should be reasonably obvious what the query is doing.

SELECT Visit.Date, Visit.Tracking, Visit.HepBO

FROM Visit

WHERE (((Visit.Tracking)<>"") AND ((Visit.HepBR)=Yes));

It is "selecting the three fields, Date, Tracking and HepBO, from the Visit table such that the Tracking field is not equal to blank, and the HepBR field of Visit (not one of the fields that will be saved) is equal to ‘Yes’."

Finally, since the manifests are "run" at the end of each day, the tables should only include the new entries for that day. To achieve this restriction, enter Date() as the criteria for the Date field. That should complete the design. Test it out to be sure it fulfills the requirements. Such a manifest table is required for each test for the CDC information system. Making four of the seven tables suffices for Project 4.

Task 4.15: Construct LabManTest queries for four of the tests.

The exception field must be handled just a little differently. This box is for clients that need further "processing" by the health professionals. So, a table like the manifest table should be built, except that it should list the client's first and last name, ID, doctor's first and last name and VisitID. With this information the health professional can find the record, read the chart information and follow up on the outstanding issue.

Task 4.16: Construct the Exception table query

Labels and Manifests

The Needs Analysis of Part I requires that for each test, a label must be generated to be affixed to the specimen drawn by the medical professional. The label can be constructed from the Label Wizard. Operating with these wizards should by now be standard, so only a few directions will be offered: Click on the Reports tab, click New, select Labels Wizard and choose the proper table. Which table is correct? Since the manifest is supposed to list off the specimens sent to the lab, the LabManTest would be the right choice. Step through the wizard, picking the Avary sheet feed label 5386. The label should have the information mentioned in the Needs Analysis as well as the Date, Tracking Number and the name of the test, e.g. HEPITITIS A, all in a large, bold, easy-to-read font. A label for each test is required for the CDC information system, but for this Project, three labels suffice.

Task 4.17: Construct label reports for three of the tests.

Use the Reports Wizard to generate a "Tabular Report" for each test. (Only three are required for the Project.) These would be printed and sent with the specimens.

Task 4.18: Construct Lab Manifest reports for three of the tests.

Notification

The next step is for the labs to return the results by returning the LabManTest tables with the outcomes filled in. The data for these tables is merged with the Visits table to construct a table from which a notification letter is generated. Though this step is similar to the queries discussed above, the operation is not elaborated upon further in the interest of simplifying the Project.

Summary

Quarterly summary statistics are to be generated from the Visits table by counting the various outcomes in each of the TestO fields. Again, in the interest of simplification this final step will not be further elaborated.