Database Practical Record
TASK 3: CREATE QUERIES (10 MARKS)
1. Query1_PatientAppointments
Aim: Display Patient’s Full Name, DoctorName, Department, and AppointmentDate.
Procedure:
1. Open the Query Design view and add the Patients, Doctors, and Appointments
tables.
2. Select the required fields: [Link] , [Link] ,
[Link] , and [Link] .
3. Save the query as Query1_PatientAppointments.
Result (SQL Code):
SELECT
[Link] AS [Patient's Full Name],
[Link],
[Link],
[Link]
FROM
Patients AS P
INNER JOIN
Appointments AS A ON [Link] = [Link]
INNER JOIN
Doctors AS D ON [Link] = [Link];
2. Query2_DoctorEarnings
Aim: Display DoctorName and Sum of Fees grouped by doctor.
Procedure:
1. Open the Query Design view and add the Doctors and Appointments tables.
2. Select [Link] and [Link] .
3. Activate the Totals row. Set DoctorName to Group By and Fee to Sum.
4. Save the query as Query2_DoctorEarnings.
Result (SQL Code):
SELECT
[Link],
SUM([Link]) AS SumOfFees
FROM
Doctors AS D
INNER JOIN
Appointments AS A ON [Link] = [Link]
GROUP BY
[Link];
3. Query3_FemalePatients
Aim: Display all female patients from the Patients table.
Procedure:
1. Open the Query Design view and add the Patients table.
2. Select all fields ( * ) or the necessary patient fields.
3. Add the Gender field and set the Criteria to “Female”.
4. Save the query as Query3_FemalePatients.
Result (SQL Code):
SELECT
*
FROM
Patients
WHERE
Gender = 'Female';
TASK 4: CREATE FORMS (10 MARKS)
Aim: Create a data entry form for the Patients table with a linked subform for
appointments, including a title and professional formatting.
Procedure:
1. Use the Form Wizard to create a main form based on the Patients table, saving it
as Patient_Form.
2. Open Patient_Form in Design View and insert a Subform control based on the
Appointments table. The wizard automatically links the forms on PatientID .
3. Insert a Label control in the Form Header and set the text to “Patient
Appointment Entry Form.”
4. Apply professional formatting, including font styles, colors, and control
alignment, to enhance usability.
Result (Form Structure):
Element Property Value
Main Form Data Source Patients Table
Main Form Name Name Patient_Form
Title Label Caption “Patient Appointment Entry Form.”
Subform Data Source Appointments Table
Subform Link Link Master/Child Fields PatientID
TASK 5: CREATE REPORT (10 MARKS)
Aim: Create a structured report based on Query1_PatientAppointments with specific
grouping, sorting, and footer elements.
Procedure:
1. Use the Report Wizard and select Query1_PatientAppointments as the data
source.
2. Set the grouping level to DoctorName.
3. Select the detail fields: Patient Name , Appointment Date , and Fee .
4. Set the sort order to AppointmentDate (Ascending).
5. In Design View, set the Report Title to “Appointments Summary Report.”
6. Insert the Page Number and Date/Time controls into the Report Footer or Page
Footer.
7. Preview and adjust the layout for clarity and readability.
Result (Report Structure):
Report Element Specification
Data Source Query1_PatientAppointments
Report Title “Appointments Summary Report.”
Grouping Grouped by DoctorName .
Detail Fields Patient Name , Appointment Date , and Fee .
Sorting AppointmentDate (Ascending).
Footer Page Number and Date Printed.