Urgent 9

Instructions/Hints to follow:

It will help when you work on the Case Study project that you:

· set up the tables first - make sure you:

· use field names with a prefix and no spaces, e.g. docID, docNameLast, docNameFirst, etc.

· choose Data Types that make "sense", e.g. docNameLast should be Short Text

· adjust the properties of the field names, e.g. if Short Text, adjust the field length from the default 255

· include a "readable" Caption for each field name, e.g. Last Name, First Name, etc.

· Use the Lookup Wizard to establish the relationships between the tables (it's easier this way), e.g. on our project, the important relationships are all done from the Appointment Table, for example:

· apptDoc - look up the docID from the Doctor table

· apptPatient - look up the patientID from the Patient table

· apptProc - look up the procCode from the Procedure table

· Enter some TEST data into all the tables, e.g.

· try 5 or so Doctors

· try 5 or so Patients

· try 5 or so Procedures

· try 5 or so Appointments just to get started to see if things are working

· Set up 3 forms by doing something like the following:

· select the Doctor table (just click once on it)

· click Create, Form

· you should get a form that shows the Appointments by Doctor

· save this, and try doing the same for 2) Patients and 3) Procedures

· Set up 3 queries to try these out, e.g.

· make an alphabetized list of all your doctors

· make a list of all the Patients sorted by (choose a field that you included, e.g. Zip Code, or Outstanding Balance, etc.)

· try a "parameter query" for all the appointments for a particular Patient (e.g. all the user to enter the Patient's Last Name)

This should give you a great start on the database (and design) portion of the project - make sure you follow all the instructions, but I want you to experiment a little to demonstrate what you've learned throughout the session!

1. BTW, for the Doctor, Patient, and Appointment tables, I would use an AUTONUMBER field for the Primary keys - don't use the ID fields you may have seen in other lessons/documents.  For the Procedure table, I would use a Short Text field of 4 characters for the Primary Key, e.g. ANN for Annual Physical, XRAY for an X-Ray/Radiology procedure, etc.

2. And finally, it is BEST PRACTICE to use a Caption for each field

· try adding in tables for States and Zip codes, and use these as sources for the Lookup Wizard to link other tables, e.g. link the Patient table to the Zip code table so you don't need to enter the Patient's City & State, just their Zip code

· You can copy these tables (Zip and State) from some of the other sample databases we posted.  Just open up the two databases (an old one with Zip and States, and your Case study project), and copy & paste the tables.  Then update the Zip Code table with some more zip codes from around Pennsylvania (e.g. Hershey, Palmyra, Harrisburg, etc.)

· You might add in a Clinic or Location table to keep track of the address info for different Clinics (or Hospitals).  I'd link this table to the Appointment table, e.g. if I was setting up an appointment for an Annual check-up, I could decide if I wanted to go to the Hershey Clinic, or Palmyra Family Practice, etc.

· Be sure to add in a LOT of data to test things out.  It shouldn't take that long to make up records for 15-20 patients, 10 or so Doctors, several Clinics, and a "bunch" of appointments.

Once you get all that done, try setting up some different queries, forms and reports!