237849 Simple Microsoft Access Job
N/A
Maksettu toimituksen yhteydessä
The following tasks refer to the database: "Australia Park Medical [url removed, login to view]".
"Australia Park Medical Clinic is a new local clinic. The Access database helps the staff work with clients faster and more accurately. The database keeps data about patients, doctors, drugs, appointments, insurance, and prescriptions. A graphical interface is expected via forms, complemented with reports."
Examine the tables in the database " Australia Park Medical [url removed, login to view]".
Using the MS Access tools, establish correct relationships between them.
Field properties must also be addressed in each table.
Part 1. Queries
---------------
1. Show how many drugs manufactured by Amcal are listed in the database.
2. For each doctor retrieve the doctor ID, name, and area of specialization along with all his/her patients ID, name, and medical history.
3. Retrieve the names of patients who made more than two appointments.
4. Find the name, address and Insurance company for all patients living in Fitzroy
5. Retrieve the drug name, quantity and usage for the patient with the first name "Keith"
6. Find the manufacturer of the following drug: Aropax; and its cost
7. Find the total cost of appointments for the following patient: Wilma Wallaby
8. Show the manufacturer name that produced the greatest number of drugs listed in the database.
9. List patients' names and appointments dates for the appointments in April 2005.
10. Show the total cost of all appointments for each doctor.
11. Retrieve the average cost of appointments listed in database.
12. Show the cost of all drugs prescribed for each patient.
13. Retrieve the patients' ID and names of patients who made the appointments between 3 pm and 5 pm in March or in April.
14. Make a list of all patients who are in either Medibank or HBA.
15. Make a list of patients with the following medical complaint: Alopecia.
Part 2. Forms and Reports
--------------------------
1. Create a form: EnterPatient which allows a person to enter a new patient and a new appointment to the database.
Three new patients have appeared at the clinic and need to be added to the list:
• Terri Irwin's ID is L789. She lives at 45 Senate Drive, Bentleigh, 3165 and is member of Allcare Pyt Ltd health Insurance. Phone numbers: 9926 9898 (Work) and 9923 4321.
• Margaret Keating is a member of NBI Health insurance. She lives at 41 Boronia Road, Ringwood, 3134 and has an ID number of W988. Her phone numbers are: Work - 9870 4545 and Home - 9879 2323. She has a cardiac condition.
• You are one of the patients - enter your relevant details; you have a workcare related chronic back injury and are a member of HBA. Your ID is assigned as X421.
2. Create a form DoctorsPatients showing all the details of doctors with a subform showing all the details of patients for each doctor. Choose the columnar layout for the parent form and the tabular layout for subform.
3. Create a report Prescriptions showing patient ID, name, address, appointment ID, appointment date, doctor's name with subreport showing the prescription, namely drug name, quantity, and usage. Add the current date to the report.
Projektin tunnus: #1984095
