equipment.
- Equipment is lent out for a 6- week period, this can be renewed for
periods of 6 weeks.
- Patients may have more than one piece of equipment on loan at a
particular time.
- At any one time, about half the equipment will be out on loan and 1 or 2
pieces will be in for repair.
- Names and addresses of patients are to be invented. Ward numbers are
W1- W10.
- At the end of each week, a full report of the location of all equipment is
to be produced. If equipment is due for return or renewal during the
following week, that patient will be contacted. There is a standard letter
for this purpose.
Design
Definition of Data Requirements
I need to design tables, forms and queries which will enable me to meet the above aims.
Record Structure
Patient Table
Figure 1 Patient Table Record Structure
Equipment Details Table
Figure 2 Equipment Details Record Structure
Stock Table
Repair Table
Figure 4 Repair Table Record Structure
Loan Table
Figure 5 Loan Table Record Structure
Sub Loan Table
Figure 6 Loan Table Record Structure
Design of Forms
Patient Form
Figure 7 Design of Patient Form
Equipment Details Form
Figure 8 Design of Equipment Details Form
Welcome Screen Form
Figure 9 Design of Welcome Screen
Repair Form
Figure 10 Design of Repair Table
Design of Reports
Locations Report
Figure 11 design of Locations Report
Equipment on Loan at End of Week Report
Figure 12 Design of Equipment on Loan at End of Week Report
Security and Integrity of Data
Security and integrity are very important aspects of a database. I have made sure the database is secure from hackers, thieves and from general mistakes messing up the system. Security can be done using passwords to keep out people who shouldn’t access areas. There are different levels of access rights these are read-only, Read/Write and No Access. Backup can help keep hold of important data in the event of the system being destroyed. Therefore the more frequently that backups are done the better. Integrity will minimize the amount of errors going into the system. Validating data in the database will help keep it in good working order and make it easy to search for data when needed.
Overall System Design
Figure 13 Overall System Design
Implementation
Tables
Patient Table
Figure 14 Patient Table
Equipment Details Table
Stock Table
Figure 16 Stock Table
Repair Table
Loan Table
Figure 18 Loan Table
Sub Loan Table
Relations
Figure 20 Relations
Queries
Date Due Query
Figure 21.1 Date Due Query Design View Figure 21.2 Date Due Query Datasheet View
Available Query
Figure 22.1 Available Query Design View Figure 22.2 Available Query Datasheet View
Location Query
Figure 23.1 Location Query Design View Figure 23.2 Location Query Datasheet View
Update Location Query
Update Repair Query
Figure 25.1 Update Repair Query Design View Figure 265.2 Update Repair Query Run
Update Stock Query
Figure 276.1 Update Stock Query Design View Figure 286.2 Update Stock Query Run
Forms
Figure 27 Patient Form
Welcome Screen Form
Equipment Details Form
Figure 29 Equipment Details Form
Repair Form
Reports
Locations Report
Figure 31 Locations Report Design View
Equipment on Loan at End of Week
Figure 303 Equipment on Loan at End of Week Design View
Figure 314 Equipment on Loan at End of Week Datasheet View
Letter to patients mail merge template
Letter to patients
Figure 336 Letter to patient mail merge
Testing
Validation of gender
Validation of Ward Numbers
Invalid value for date field
Figure 369 Invalid value for date field
Inappropriate value for input mask
By Leo Kadis