Urgent 9
studystudent
Wk8_Case_Data_-_better_format.xlsxPatients
| PatientSSN William J. Hitchcock: I would ADD a PatientNum field with AutoNumber and use it as the Primary Key rather than using the SSN. NOTE: if you keep the SSN in here, try using a custom format to display this with dashes. | PatientNameLast | PatientNameFirst | PatientCity William J. Hitchcock: this field does NOT belong here, as it's redundant data - you can generate it by using the Zip Code | PatientStreet | PatientST William J. Hitchcock: this field does NOT belong here, as it's redundant data - you can generate it by using the Zip Code | PatientZipCode | PatientDOB William J. Hitchcock: consider adding a Date of Birth field for all the patients |
| 123456789 | Smith | John | 329 N Main St | 17234 | |||
| 234567890 | Johnson | Alex | 1215 First Ave | 17078 | |||
| 345678910 | Adams | Jake | 564 Water St | 17345 | |||
| 456789101 | Jennings | Mary | 2956 Mill Rd | 17057 | |||
| 567891011 | Nelson | Sue | 3873 Second Ave | 17987 |
Appointments
| ApptID William J. Hitchcock: I would NOT use this "derived key" as a Primary Key, instead create an AppointmentNumber field and set it as AutoNumber | ApptPatient William J. Hitchcock: this is a FOREIGN key, use the Lookup Wizard to find the Patient you are making the appointment for. | ApptProc William J. Hitchcock: this is a FOREIGN key, use the Lookup Wizard to find the Procedure the Patient is going to have. | ApptDoc William J. Hitchcock: this is a FOREIGN Key, use the Lookup Wizard to find the Doctor you are scheduling the appointment with. | ApptDate William J. Hitchcock: You may need to set up a Date/Time field and format this as a Long Date, and use a Short Date Input mask. You may find you'll need to enter these dates in manually. | ApptTime William J. Hitchcock: set up a Date/Time field and format the times to display am/pm, and use an input mask with military time hh:mm you may need to re-enter the times manually, but you'll get the idea. | AppointmentLocation William J. Hitchcock: this field does NOT belong here, because you can find the location based on the Procedure being done. |
| ADAM8910002 | Adams | IMM | Miller | 04-Oct-19 | 12:00 PM | |
| JENN9101003 | Jennings | STRESS | Stewart | 03-Oct-19 | 7:00 AM | |
| JOHN7890001 | Johnson | YRPHS | Johnson | 02-Oct-19 | 10:00 AM | |
| NELS1011005 | Nelson | FLU | Johnson | 09-Oct-19 | 9:30 AM | |
| SMIT6789001 | Smith | YRPHS | Miller | 01-Oct-19 | 9:00 AM |
Doctors
| DoctorID William J. Hitchcock: I suggest adding a DoctorNum field as AutoNumber and use this as the Primary Key rather than this "derived key" code. AutoNumber handles many doctors if the hospital and clinics grow larger. | DoctorLastName | DoctorFirstName | DoctorSpecialty William J. Hitchcock: Consider making a SPECIALTY table, and then using the Lookup Wizard here to look this up. |
| DOC001 | Johnson | Virginia | Family Practice |
| DOC002 | Miller | Alexis | Pediatrics |
| DOC003 | Stewart | Dave | Cardiovascular |
| DOC004 | Young | Phillip | Rehabilitation |
| DOC005 | Wentz | Carson | Neurology |
Procedure
| ProdcedureCode William J. Hitchcock: use a 6 character Short Text field for the Primary Key of the Procedure table. Enter in alphabetic codes to make it easier for users to remember these. | ProcedureName | ProcedurePrep | ProcedureLocation William J. Hitchcock: this will be a FOREIGN key field, so you can look up the Procedure Location in the Location table. | ProcedureDuration William J. Hitchcock: you may have to set this up as a Date/Time field in Access, then use a Format and Input Mask to show it as time, and manually enter these times in. These have been formatted in Excel to display times in hours:minutes |
| YRPHS | Yearly Physical | get lab work done if needed | HGC | 0:30 |
| IMM | Immunization Shots | scrub arm with disinfectant | PFP | 0:30 |
| STRESS | Stress Test | Fast 12 Hours clear liquids no solid food | HH | 1:30 |
| PT | Physical Therapy | None | HRC | 2:00 |
| FLU | Flu Vaccination | scrub arm with disinfectant | HGC | 0:30 |
| XRAY | X-Ray | remove all metal objects on person | HH | 1:00 |
| BLOOD | Lab work - blood | scrub arm with disinfectant | HGC | 0:45 |
Location
| LocCode | LocName | LocAddress | LocZip |
| HGC | Hershey General Clinic | 845 Fishburn Rd | 17234 |
| PFP | Palmyra Family Practice | 1400 S Forge Rd | 17078 |
| HH | Hershey Hospital | 500 University Dr | 17234 |
| HRC | Hershey Rehab Clinic | 1135 Old West Chocolate Ave | 17234 |
States
| StateCode William J. Hitchcock: use a 2 char short text field for the Primary Key. Try > in the format to force all the values to display in CAPITAL letters | StateName |
| DE | Delaware |
| MD | Maryland |
| NJ | New Jersey |
| NY | New York |
| OH | Ohio |
| PA | Pennsylvania |
| WV | West Virginia |
ZipCodes
| ZipCode William J. Hitchcock: use this as your Primary key either a number or short text field will work (if short text, set the length to 5, if number, try a long integer) | ZipCity | ZipState William J. Hitchcock: this is a "foreign key" field, you'll set up the Lookup Wizard to look this up in the State table. |
| 08232 | Atlantic City | NJ |
| 14853 | Ithaca | NY |
| 17003 | Annville | PA |
| 17057 | Middletown | PA |
| 17078 | Palmyra | PA |
| 17234 | Hershey | PA |
| 17345 | Manchester | PA |
| 17987 | Cleona | PA |
| 44502 | Youngstown | OH |
| NOTE: add in more zip codes to give you some practice adding in records, as well as testing different queries. |
