Dentist: Database and Appointment

Access Case: Dentist’s Office
Brief:
You will create an Access Relational Database for a dentist’s office.

Database Objects:
Tables:
1. Patients – keeps a track of patient information (Create at least 7 patients).
2. Dental Procedures – The dentist has 5 procedures.
Procedure No. Procedure Type
Rate
1
Cleaning
Rs.1000.00
2
Crown
Rs. 2000.00
3
Filling
Rs. 2000.00
4
Root Canal
Rs. 3000.00
5
X-Ray
Rs. 700.00
3. Appointment – keeps a track of the each appointment, Patient, Date, Time-in & Time-out.
4. Appointment Details - Some patients can have multiple procedures in one appointment.
(Have each patient have at-least one appointment in the week. Have at-least 3 patients, have more than one procedure in an appointment. Have at-least one appointment every day of the week; Mon to Fri).
Forms:
1. The dentist wants a form to record new patients.
2. The dentist wants an ‘appointment’ form to record each appointment, patient, date & time and a sub-form for the above appointment, with procedures and cost.
3. Menu switch-board form.
Queries:
1. Create a Select query for all the days of the week, with appointments, their customers, date, in & out times and billing.
2. Create a Summation Query that counts the number of times each procedure has been performed, sorted in descending order.
3. Create a Parameter Query that lists appointments for a patient for the patient ID input by the user.
Reports:
1. Create an itemized billings report for each patient.

Prof. Nitin Bolinjkar