Dentist Office Database
This database is being created for the receptionist/manager of a dentist's office. The office has one dental assistant and one dentist. Each performs various procedures and each procedure has a specific cost. Even though the patient is charged a set fee per procedure, the dental assistant is paid by the hour and also gets a percentage of the procedure price per procedure. While a real database would contain a lot more information, this database has been simplified to allow you to concentrate on its construction. It will contain the following information.
|
Table: tblPatients
|
Table: tblProcedures
|
Table: tblAppointments
|
|
Patient ID (autonumber)
|
Procedure ID (autonumber)
|
Appointment ID (autonumber)
|
|
Patient Last Name
|
Procedure Name
|
Appt Date (Input Mask)
|
|
Patient First Name
|
Procedure Price (currency)
|
Patient ID (foreign key)
|
|
Patient Address
|
|
Procedure ID (foreign key)
|
|
Patient Phone (input mask)
|
|
Appointment Duration (number of hours: .5, 1, 1.5, or 2)
|
|
Patient Age (integer)
|
|
Emergency Fee ($10-$50) (currency)
|
|
Patient Copay ($0 if no insurance, $15-$30 if have insurance; currency)
|
|
Paid (yes/no)
|
|
Indicator if Patient has Dental Insurance (y/n)
|
|
|
Step by Step Description:
1. Use all of the naming conventions discussed in this course.
2. Set the database to compact on close.
3. Create three tables as per specifications above with the proper formatting.
4. Set table relationships and referential integrity (including cascade update and cascade delete if applicable)
5. Enter the data into the tables.
- The tblPatients table should have a minimum of ten patients with one of them being you. Half of the patients should not have dental insurance. At least four of the patients should be children age 0-16. The rest should be over 16.
- In the tblProcedures table enter at least eight different procedures (cleaning, crown, filling, root canal, x-ray, etc.) that the dentist and/or dental assistant performs with a different price for each procedure.
Note: The procedure prices above should take into account work done by both the dentist and dental assistant, any supplies needed, the time it takes to perform the procedure, etc. These fees should be realistic.
- The tblAppointments table needs a minimum of 15 appointments
Note: All appointments should occur in the current month and the current year. Some patients will not see the dentist at all during this month, while others will see the dentist multiple times. Some patients will have multiple procedures on the same day. Each appointment has a duration time of .5, 1, 1.5 or 2 hours in length. At least 3 appointments should have an emergency fee associated with them of $10-$50 each.
6. Create Queries
- One that computes the amount due for each patient who has dental insurance (only those patients who have not yet paid). Name this Query: qryAmtDueIns
The fee for each appointment is defined as:
Fee = Patient Copay + Procedure Price + Emergency Fee
- A query that lists the total time for each appointment and the dental assistant's pay based on the following formula:
Assistant's Pay = Appointment Duration * $30 per hour + 10% of the Procedure Price. Name this query: qryAssistantPay
- One that will show the sum (one number) of all of the charges for patients 17 or older. Name this query: qryAdultSum
7. Create the following forms:
- One that allows the receptionist to enter the data into the tblPatients table. Name this: frmNewPatient
- One that allows the receptionist to enter data into the tblProcedures table. Name this form: frmNewProcedure
- One that allows the receptionist to enter data into the tblAppointments table. This last one assumes that the patient being treated is already listed in the database. Name this form: frmNewAppointment
- A form that lists all of the patients' names (first and last) in alphabetical order by last name and their ages with a subform that lists the procedures that patient has had in order by date. If a patient has had no procedures done then s/he should not appear on this form. Name this form: frmPatientProcedures. Name the subform: fsubProceduresPerformed.
8. Create Reports
- One that lists all patient names and the procedures (name, date) they have had done grouped by the patient ID. Name this report: rptPatientProcedures
- A report that shows the total outstanding amount (not paid by patients). This report should return a single value that is the sum of all outstanding amounts. Name this report: rptOutstanding
- A Report that lists appointments (with the patient's name) that involved an Emergency fee and the total amount charged for each visit (see Fee above). Name this report: rptEmergency
- One that lists the children (age 0-16) who have been to the dentist and include the name of the procedure that was performed on each. Name this report: rptChildren
9. Include at least one graphic in your database on one or more of your forms.
Please do in MS access.