I have been given the task of creating a system for a hospital to keep records of their loan equipment. It needs to be able to keep records of what equipment has been borrowed, by whom and when, and also if any equipment is unavailable as it is in repair.
CANDIDATE NAME
PAPER 3 COMPUTING COURSEWORK
CENTRE NUMBER:
CANDIDATE NUMBER:
INTRODUCTION 5
SYSTEMS LIFE CYCLE 5
GATHERING DATA 6
PROBLEMS OF EXISTING SYSTEM 6
OBJECTIVES OF NEW SYSTEM 7
DESIGN 8
Feasibility of Using a Computer Based System: 8
Performance Requirements: 8
Costs: 8
Hardware 9
Software 9
Excel: 9
Access: 10
High Level Languages: 11
Evaluation of Software: 11
DATA REQUIREMENTS: 12
DATA FLOW 13
Data Flow Diagrams: 13
ENTITY-RELATIONSHIP DIAGRAMS 15
SYSTEM DESIGN 16
Top Down Design: 16
Jackson Structure Diagram: 16
User Interface: 17
Interface Styles: 18
. Command Line Interface (e.g. MSDOS) 18
2. Menu's 18
Full Screen Menu: 18
Pull-Down Menu: 19
Pop-Up Menu: 19
3. Natural Language 19
4. Forms & Dialogue Boxes 20
Advantages of Using a Common User Interface: 20
MY DESIGN: 21
Outputs: 21
Hard Copy of Equipment: 21
Weekly Report: 21
Menus: 22
Methods of Data Entry: 23
Tables: 24
Security and Integrity of Data: 25
Validation Rules: 25
Back-Ups 25
Security: 25
Passwords 25
Encryption 25
Password Locks 25
IMPLEMENTATION 26
Entering Data Stores: 26
Data Entry Page: 27
Loan Equipment Macro: 29
Validation Rules To Prevent Human Error: 30
Viewing Patient Details: 32
Mail Merge Letter 33
Patient Search Facilities: 34
Managers Area: 35
Main Menu 36
Making the Spreadsheet User Friendly: 37
Security 38
Testing the Buttons: 39
Navigation Buttons: 39
Operation Buttons: 40
Testing Validation Rules: 41
MAINTENANCE: 42
User Documentation 42
System Documentation 42
Introduction
I have been given the task of creating a system for a hospital to keep records of their loan equipment. It needs to be able to keep records of what equipment has been borrowed, by whom and when, and also if any equipment is unavailable as it is in repair.
Systems Life Cycle
We have already been given the information for a problem definition, feasibility study and analysis, but have been told that we do not need to do these stages of design. However if I were to document the whole system I would use the system life cycle, as it helps designers approach the design in a methodical way. I have shown the life cycle below:
. Problem Definition - the problem will be defined by the user
2. Feasibility Study - investigate the system and decide if there is need for a new system
3. Analysis - analyse the requirements and produce a specification
4. Design - the design is produced
5. Construction
6. Testing - system is fully tested
7. Implementation - system implemented and users trained
8. Maintenance - there are three types of maintenance:
a. Perfective maintenance, where you make the system easier to use or you add new facilities
b. Adaptive maintenance, where you make changes to suit the changes in working environment
c. Corrective maintenance, where you change something because of errors discovered in the original system
9. Evaluation - evaluation of the system and if it meets the requirements of the user
Gathering Data
If I had to collect the data on the requirements of the new system and the problems with the old one, I would use the following four techniques:
. Interviewing staff at different levels about the present system and what they would like to see from the new system.
2. Sending out questionnaires and analyzing results.
3. Observing current procedures with current system.
4. Examine current business and systems documents and outputs
Problems of Existing System
The present system in use at the hospital is a manual one, this means everything is recorded by hand on paper. This is an extremely inefficient way of recording the data as information can easily be lost or misplaced. We are also told that there is a need for a new system to "cut down on loss and wastage", so the new system must be reliable. This suggests that the secretary has a lot to do with the possibility of hundreds of different files.
Objectives of New System
There are several specifications for the new system, these are:
. Ability to store details of equipment (how many in stock and price of equipment)
2. An Item code has to be devised which identifies each piece of equipment individually, while also indicating what type of equipment it is.
3. Be able to find information for any individual piece of equipment:
a. Whether it is available, being repaired or out on loan
b. If on loan, to whom, and at what address or ward number
c. If on loan, when it is due for return or the loan renewed
d. If being repaired, when it was sent for repair
4. Be able to produce a hard copy of this information for any piece of equipment
5. To enable patients to have more than one piece of equipment out on loan at a particular time (e.g. 2 crutches and a wheelchair)
6. 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 must be contacted, with a standard letter.
DESIGN
Feasibility of Using a Computer Based System:
It has been required that I should produce a computer based system for the hospital. There are many advantages to a computer based rather than a non-computerised system these advantages include:
* Computer systems can be backed up on a regular basis very easily. So that in the event of a fire or if the computers were to contract a virus, the files are not lost altogether. However if the system was not computerised and there was a fire, the data would be lost. This could lead to a loss of customers due to a lack to reliability.
* A computer system can be modified and changed more easily than a non-computerised system. E.g. if you wanted to change the format of the customer data entry forms on a computer it could be done in a few minutes but manually on a non computerised system it would take a very long time to rewrite the records if they were held on card.
* A computerised system can find files within seconds where as on a card filing system it would take people a lot longer as it has to all be done by people and then the process is subject to human error.
Performance Requirements:
The client has stated that one of the main reasons to have the system is to save time and cut down on wages. This means that the system must be capable of retrieving data quickly and the system must be easy to use. The speed is one of the major factors, but must create a system that does not take up vast amounts of memory.
Costs:
The initial starting out costs will be the machine purchase, and basic unit for it to go on (unless already owned). Other costs that must be taken into consideration are training costs, staff will have to be trained how to use the system quickly and efficiently. Staff training costs should be low as the system is going to be designed so that it is easy to use and understand, this helps both the employee pick it up quickly and the employer spending less money on staff training. Maintenance is not going to be a major cost, the system will be designed to work effectively around the clock so will not need extra time and money spent on it.
I will now look at the hardware and software which are most appropriate for the system:
Hardware
The hardware of a machine is the physical machinery, which makes up the computer. In order to start to think about designing a system I need to first of all think about the possible use of different types of hardware: -
* Computer - The actual machine which processes all the data
* Keyboard - used mainly to enter in data about the customer, equipment and loan duration
* Visual Display Unit - A user-friendly way to display the system.
* Computer mouse - to select different function options.
* Screen Touch Screen - to speed up the input of data and other functions within the system.
* Screen pens - to speed up processes within the system especially the input of data.
I have decided to keep the hardware simple any only have a computer, keyboard, VDU and mouse. This is relatively cheep compared with the other options and will be easy for the user to understand.
Software
There are three main choices of software these are Excel a spreadsheet, Access a database or a high level language such as Pascal. All have advantages and disadvantages.
Excel:
Excel has many advantages including:
* Excel allows you to insert buttons or macros these allow you to open and close forms at the click of a button. They also allow you to do things like calculations and formulas.
* Excel allows you to highlight different cells in colour and fill effects.
* There is a formula bar at the top, which displays the formula of the cell you are viewing; here you can also edit the formula.
* You can create look-up tables.
* You can create graphs using the easy to understand Wizard tool.
* In Excel you can also copy the cell of a formula from cell you cell. This can be very useful when using a formula to calculate a certain equation.
* You can name cells, giving one a unique cell reference.
* Excel is a multi dimensional spreadsheet, this means that you can have as many sheets as you want, this is also known as a three dimensional spreadsheet.
* I have a relatively good knowledge of this software
Excel has disadvantages as well as advantages, these include: -
* If you change the name of a page after making a macro it can't find the correct page because it has changed names. This means you then have to go into Visual Basic and manually change the name of the page on the actual piece of code. This all takes time and if you change more than one page's name it will take a very long time.
* With the labels of the graph they will not change with the data so you end up having to go over the whole process of making a graph again.
Access:
I will now look at the advantages and disadvantages of access:
* Access can make forms to insert data.
* In Access you can have macros to go between forms and queries.
* Access is not a flat file database so you can also create relationships between tables of data.
* It has a query language, which means it can ...
This is a preview of the whole essay
* With the labels of the graph they will not change with the data so you end up having to go over the whole process of making a graph again.
Access:
I will now look at the advantages and disadvantages of access:
* Access can make forms to insert data.
* In Access you can have macros to go between forms and queries.
* Access is not a flat file database so you can also create relationships between tables of data.
* It has a query language, which means it can perform advanced searches.
* You can hide certain forms out of view.
* You can put a password on the system to prevent people tampering with it.
* There are writing tools including spelling and punctuation tools available.
* Access can have relational tables, which are linked together. This avoids repetition in entering in data as more than one department of the system can share one bit of data.
Access has disadvantages as well as advantages these include: -
* Access is more sophisticated than Excel and is therefore harder to use for a novice.
* Access is used by professional system designers who design systems for end users. This means the system must be very powerful and very detailed. However it is more complicated for a non-expert to use.
* I have little knowledge of this particular software
High Level Languages:
Advantages:
* Can be created to suit the customers need exactly
* I have a fairly good knowledge of this software
Disadvantages
* Takes a lot longer to code a program rather than using tables in Excel or Access
* I am not an expert at using high level languages so I will be prone to errors that perhaps would not happen with a package such as Excel or Access.
Evaluation of Software:
I have decided to choose Excel to use as I have a greater knowledge of this software and I think that the final product will be easier for the end user. Although Access offers a lot more features and functions I am not familiar with them so creating the system would take much longer.
Excel is a quick piece of software which can open files very fast, although Access is faster than Excel I think that the difference in speed is hardly noticeable so it hasn't affected my decision.
Excel is easily upgradeable, enabling the hospital to introduce new functions and modify it without any hassle.
I decided not to use a high level language such as Pascal, as although I have a good knowledge of it, the time requirements do not enable me to create the system this way. I would have liked to use this software as it can give the end user exactly what they want and I enjoy using and learning high level languages, but due to the time constraints it is not possible.
Data Requirements:
The data requirements for the system include the following:
* Data of all the equipment the hospital owns, including the cost of replacement and the unique item code for each piece of equipment.
* Data on where the equipment is, in which ward or at what address, which patient it is with, or if it is in repair and also when it is due back.
* Data on the patients, this will include patient ID, patient title, patient forename, patient surname, patient street name and number, town, postcode and patient contact number.
Data Flow
Data Flow Diagrams:
To help me with the design I have drawn a data flow diagram to show the logical movement of the data through a system, this is often used when creating systems as it helps to make the design as effective as possible. However it does not show how the data is stored.
There are different levels of data flow diagrams, I have drawn a level 1 diagram that can also be known as a context diagram. A data flow diagram has many symbols, which have different meanings, the symbols I have used are: -
* External Entities - can also be known as a data source or destination. In the following diagram the Patient is the External Entity.
* Processes - are operations performed on the data. In the following diagram the processes are each named P1, P2 ...etc.
* Data Stores - are logical stores for data, which are not physical in the following data, flow table they are labelled D1 and D2.
There are many different levels of DFD's (Data Flow Diagrams), the DFD below is a level 0 and the DFD on the following page is an example of a level 1 DFD.
Entity-Relationship diagrams
Entity-relationship diagrams show the relationship between entities. Entities are things in which data is held. In order to be able to draw an entity-relationship diagram you need to know the degree and the name of the relationship. The degree of the relationship will come under one of three categories:
. One-to-one, e.g. relationship between Person and National Insurance number which is unique to every person.
2. One -to-many, e.g. relationship between Tree and Leaf, the tree has many leaves but the leaves only have one tree.
3. Many-to-many, e.g. relationship between Books and Readers in a library, many books have many different readers and vice-versa.
I have demonstrated the entity-relationship diagrams below with ones that are relevant to my system.
The above many to many relationship can be broken down into:
System Design
Top Down Design:
Top-Down design is a way of breaking down a problem into the major tasks to be performed, each of these then being further broken down into separate sub-tasks, and so on until each sub-task is simple enough to be written as a self-contained module or procedure. The program then consists of a series of calls to these modules. This is mainly used in programming but it can be used to help me with my system.
Jackson Structure Diagram:
It is useful to have a way of representing this breaking down of modules and some way of showing how they all relate to each other. A Jackson diagram is a simple way of doing this.
I have included a Jackson diagram below:
User Interface:
This describes the method in which users communicate with a computer. Good interface is important because:-
. Safety Factors, e.g. If a pilot on an aeroplane can't use the computer it crashes
2. Need For Efficiency, in terms of time and money.
3. Enjoyment, Staff will not want to be stressed with an awkward system that they find difficult to use.
A good user interface is an important part of a successful system, the design must take into consideration:
* Who is going to use the system - experienced users, members of the public or even children
* What Tasks the computer is performing - life threatening procedures such as controlling a life support machine or flying a plane, or just using databases and spreadsheets.
* The Environment in which the computer will be used - calm and quiet or busy and noisy
* What technology is feasible
In particular, careful screen design is important as it can make a huge difference to the usability of a system. When designing my system I will take the following points into consideration:
* A title should be given to the display
* It should not be too cluttered, use spaces and blanks to make it look better
* It should indicate the size and format of data entry in each field
* Items should be in a logical order to make it as easy as possible for the end user
* Colour should be used carefully
* Default values should be entered wherever possible
* Adequate help facilities should be provided
* Have the ability to let the user go back and correct any mistakes made.
Interface Styles:
. Command Line Interface (e.g. MSDOS)
a) Not user friendly
b) Demands high computer knowledge
This is an example of command line interface, as you can see little help is given to the user, who has to type the command for the operation they require.
2. Menu's
a) User must have, and be able to use, a mouse
b) Very simple to use, no in depth knowledge required
c) Several different types of menu's, all defined below:
Full Screen Menu:
This type of menu is often used as the 'front end' of an application. It stays on screen until the user makes a choice.
Pull-Down Menu:
This type of menu is displayed along the top of the screen, and when the user clicks on an item, a submenu appears. This menu is always present whatever screen the user is looking at in the application.
Pop-Up Menu:
This menu "pops up" in response to an action by the user, for example a right click on a particular area of the screen.
3. Natural Language
a) This is a very attractive idea, having a computer that understands natural language, but unfortunately this is an extremely hard concept for the machine to understand. The English language is extremely complex, firstly the structure/syntax of a sentence may not always be clear, e.g:
James and Henrietta are married
This sentence can be interpreted a number of ways, for example how can you tell that they are married to each other?
Secondly many English words have more than one meaning, for example the word "match" has many interpretations.
b) Can make a naïve user believe a computer can think and converse
4. Forms & Dialogue Boxes
a) Again, easy to use, but only if designed appropriately. Spaces are important to avoid making it look cluttered
An example of both forms and dialogue boxes can be shown here in the print box used for Microsoft Office XP.
Advantages of Using a Common User Interface:
All the software packages that I have taken the above screenshots from use a consistent interface and have a similar 'look and feel' so that a user familiar with one package can easily use a second. For example, in each package a single click of the mouse selects an item and a double click activates the item. In each package the methods for opening, closing, sizing and moving windows is identical allowing users to use the software extremely easily. The advantages can be summarized easily:
. Consistency
2. Increase of Speed and Learning
3. Ease of Use
4. Confidence for Novices
5. Range of Software Tasks Increased
My Design:
Outputs:
There will be two main outputs of my system, both needing to be user friendly, the two are explained below.
Hard Copy of Equipment:
It is required that the system should be able to produce a hard copy of where any given piece of equipment is, and with whom, at any one time.
The hard copy would be a print-out on an A4 sheet coming directly from my system. It would be for managers checking stock lists, or for receptionists wishing to back-up the information. I will have to include the following details in the print-out:
* Whether equipment is available, being repaired or out on loan
* If on loan, to whom and at what address or ward number
* If on loan, when is it due to be returned or the loan renewed
* If being repaired, the date when it was sent for repair
The hard copy needs only a plain and simple design showing the necessary information, there is no need for any colour or images. The font will be "Arial" as it is extremely easy to read and will be in size 10 as this is again easy to read and not too big or small. The titles will be in a bold font as they will stand out and make it easier to find the information on the sheet that you are looking for.
Weekly Report:
The weekly report will show the location of all equipment and show which patients need to be contacted about the return of equipment. If the equipment is due for return or renewal in the following week a standard letter will be sent, by using the mail merge function.
The report needs to be well laid out so that it is easy to view the entire report quickly, to do this I will use "Arial" font and size 10 as these are the most appropriate for the desired effect.
Again, no colour or graphics are needed, but on the standard letter sent to patients that need to return equipment the layout will be different. A letterhead will be used to show that the letter is from the hospital and the font and font size will remain the same.
Menus:
I will have a main welcome page which all users will see when they run the program, from this they will be able to access many other menu's for different functions (for example to enter a new patient, or to search for a patient).
Throughout the program the macro buttons will have a consistent colour and the distinct font "Arial" in an easy to read size 10. All buttons will look like the example on the right, with the creamy colour background. The size of the buttons will vary as they all contain different text (the longer the text, the larger the button).
The opening menu will have two images to create a better user interface and make the program look more professional. I have decided to keep the background colour of light blue present in all spreadsheets throughout my system as it is easy on the eyes, looks pleasant and so that there is a consistency in colour.
Methods of Data Entry:
The user needs to enter data into the spreadsheet about the patient, equipment and the loan. To make data entry easy I have used validation rules on the data entry cells.
When the cell is selected the yellow information box appears, giving the user guidance on how to enter the data.
I have set the validation rule on this particular cell to allow only numbers and in the format dd/mm/yy, if the user enters anything else the following error message appears informing the user that they have entered incorrect information.
When the user is entering the equipment the user wants to loan, I have made a drop down menu so that it is easier for the user to enter the information and leaves less room for error.
The user can easily select the appropriate equipment with the mouse. I have set the validation rule so that it will only accept values in the cell from the drop down menu. As there are no other pieces of equipment available to loan this protects the system from typing errors and mistakes.
The user will only have to use a mouse and a keyboard to input the data and only the mouse for the operation of the system (e.g. using the buttons).
Tables:
My data on patients and equipment will be stored in tables on a spreadsheet. I have designed tables to show the equipment in stock, equipment ID codes, patient records and equipment due for return. All tables will have a bold outline with a thin cross-divide to show the separate cells. The titles will be in bold font with a red background, this is to clearly show the divide between titles and data.
Security and Integrity of Data:
Validation Rules:
A validation rule specifies a test, which has to be performed on any data entered into the cell. If that data does not pass the test an error message comes up on the screen, which contains the text specified in the validation rule box. I will use many validation rules to ensure the correct data is entered and make sure that any erroneous data that is entered is picked up straight away.
As I am using Excel to make the program I can use validation rules to create drop down menus so that the user does not have the option of making typing errors because it only allows them to pick a choice from a list rather than type it in.
Back-Ups
To ensure the safety of the data daily back ups should be made so that if the system for some reason crashes or gets hacked, the hospital will have a recent back up to fall back on. This type of a daily back up is called a differential backup. By backing up the data it will prevent aggravating patients by not knowing if there are any items of equipment available and also stop the possibility of equipment being lost due to the hospital having no records of where it is. The regular backup should be made to a floppy disk, as it is a small file that will easily fit onto a floppy disk. To ensure maximum safety the back-ups should be done via two floppy disks, one which held data of Saturdays, Tuesdays and Thursdays. The other holding back-ups of Mondays, Wednesdays and Fridays. This means that if for some reason the system goes down and the daily back up that day had gone wrong, there is always the other floppy disk which even though is dated may still be of some use.
Security:
Security is used by making sure there are safeguards to ensure that data is not accidentally or maliciously corrupted.
* Passwords: The system will have a password on it to prevent the user from altering any formula, rules, appearance or design of the program. The user will only be able to enter data in the appropriate sections, whereas the administrator will have total access to the entire program and have the ability to remove patients from the database.
* Encryption: Encryption is where the computer makes the data appear scrambled so that it is only available to those who have a decoder, this will be used to send patient information from one machine to another.
* Password Locks: This is where the computer will lock the keyboard and monitor if left idle for a set period of time. To re-activate the user will have to know a password else will not be able to get in. This is to prevent members of the public having a quick look through the computer when the receptionist is in the toilet for example.
IMPLEMENTATION
Entering Data Stores:
Firstly I devised an item code, it had to be a unique identifier for each piece of equipment. I decided to make it simple and use a code that although looked confusing was easy to understand. As I had different sizes of equipment I decided that "jun" in the code would mean junior, "sm", "med", "lar" and "ad", would mean small, medium, large and adult respectively.
I then shortened the description of the equipment from wheelchair to wc, crutch to cru etc.
For each different piece of equipment I gave it a number, e.g the first piece would be #1 and the second #2 etc. So I then put the code together and got:
Junior Wheelchair - wcjun#1, wcjun#2
This shows that there were two junior wheelchairs but both can be uniquely identified.
The next step was to enter all the data stores into the spreadsheet in tables, these were the item codes for each piece of equipment, the cost of replacement and the equipment that was available for loan. I incorporated all of these into one table, I have included a screen shot of this below:
The equipment codes were in the next column but hidden so that they couldn't be tampered with. The number in stock was calculated by using a "Count If" statement, calculating from the equipment ID's, the formula is used was:
Tells the computer The absolute
what function cell reference
to run where to look
=COUNTIF($G$3:$G$35,"wcjun*")
The criteria to look for. I have added the
* as a wildcard so that it will count all
the wcjun (junior wheelchairs)
This formula was entered in the "No. of stock" column, with the same formula used but just changing the criteria. The "$" signs mean that it is a unique cell reference and will not change as it moves down the cells.
Data Entry Page:
I then devised a sheet on which the user could enter all the information to allow patients to loan the equipment. It had to take their details, check to see if the equipment was available and then add the details to another sheet. I have included a screen shot of this below:
The user will have to enter the patient details into these easy to understand boxes and then to select the equipment click the macro button. The button selects the other sheet which has the equipment list on and the equipment ID's. The button deletes one of the ID's for the piece of equipment being loaned, for example if the patient wanted to loan a junior wheelchair and there were two in stock, the list would display wcjun#1, wcjun#2 and the button would automatically delete wcjun#1, so that the count if statement would show there only being 1 left in stock. I think this is the most effective way to do it as the count if statement instantly updates and it is easy to see how many are in stock.
This data entry sheet works and looks good, but I don't think that it holds enough information about the patients for the hospital. I broke down the address section as it was a bit long, I changed it into four entry boxes, (street name and number, town, county, postcode). This not only makes it easier for the user entering the data but makes it easier to read the tables and makes mail merging possible. I also added a return date to the entry form, I made this easy as well by having a box which the user only enters the duration of the loan and the computer automatically works out the date it was due for return. This works by having a hidden cell with the formula =TODAY() in it and in the equipment return cell I have just added the loan duration to the hidden cell, which displays the correct return date.
Loan Equipment Macro:
To make the information entered in the loan equipment sheet transfer into a separate patient details sheet I needed to code a macro button in visual basic. I have a good programming knowledge so this was not a problem, I have included the code I used below and what it means:
Sub loanequiment()
Range("D10").Select
firstname = ActiveCell
Sheets("Patients").Select
Range("A6").Select
counter = ActiveCell.Value
counter = counter + 1
ActiveCell.Value = counter
ActiveCell.Offset(counter, 0).Select
ActiveCell.Value = firstname
Sheets("Loan Equipment").Select
Range("D12").Select
dob = ActiveCell
Sheets("Patients").Select
Range("B6").Select
counter = ActiveCell.Value
counter = counter + 1
ActiveCell.Value = counter
ActiveCell.Offset(counter, 0).Select
ActiveCell.Value = dob
Sheets("Loan Equipment").Select
Sheets("Loan Equipment").Select
Range("D10").Select
Selection.ClearContents
Range("D12").Select
Selection.ClearContents
Range("D14").Select
Selection.ClearContents
Range("D16").Select
Selection.ClearContents
Range("D20").Select
Selection.ClearContents
Range("D22").Select
Selection.ClearContents
Range("D24").Select
Selection.ClearContents
Range("D26").Select
Selection.ClearContents
Range("D28").Select
Selection.ClearContents
Range("D10").Select
End Sub
Validation Rules To Prevent Human Error:
To make it easier for the end user to enter the data into my spreadsheet I set up some drop down menus and to prevent human error (e.g. typing mistakes) I added some validation rules.
The validation rules I used were on:
* Date of Birth - Only allowing dates to be entered
* Town - Allowing only values from the drop down menu. The hospital will only deal with patients from a certain attachment area, as the project brief gave me no clues as to what this is I have included all local towns and villages as patients from outside this area would use a different hospital
* County - This only allows Kent, for the same reason as the point above, if a patient lives outside Kent they will not travel all the way to this hospital to loan some equipment.
* Contact Number - Only allowing telephone numbers
* Ward Number - As I was given a list of all the wards that can loan equipment I have used a drop down menu here but also included n/a in the menu as home loans are also a possibility.
* Patient Equipment - This is also a drop down list, to prevent errors and stop patients loaning equipment that is not available.
* Loan Duration - I have set a validation rule on this cell to only allow positive integers. This is because it is not possible to have 1/2 a day or loan equipment for minus 2 days.
Whilst setting the validation rules I also set up input messages so that if the user has any problems it explains it very simply in a small yellow box.
The above screen shots show the data validation for the loan duration cell. You can see that it will only allow whole numbers that are greater than one and if not it displays the error message that I have set.
Viewing Patient Details:
To make the program as efficient as possible I had to make it as easy to understand and read as possible, this meant the interface was extremely important. I set the patient details sheet out under clearly labeled columns and used the consistent colour scheme that was used throughout my project.
These are the headings I used for the table, from these it is easy to look across and see the patients' details.
I also added a macro button to sort the patients by their equipment return date, this would give the user the chance to see which patients need to be contacted about return/renewal. This was simply done by recording the macro and then assigning it to a button.
To make the program have better capabilities I programmed another macro to select the top ten patients who need to return equipment (sorted by their equipment return date) and copy their details to another sheet in a table. The user can then view the dates of return and highlight the ones which need a reminder letter sent to them. I created a reminder letter in Microsoft Word (chosen as it is the best word processor currently available), using the mail merge function. It takes the data from the table of patients that are highlighted and automatically fills in the details on the letter. I have included an example below:
As you can see here I have selected the patient "Oli Johnson", when I press the "Issue Letter To Selected" macro button, it opens the Microsoft Word mail merge letter that I have created, I have included on the next page to show how it works.
Mail Merge Letter
Patients
Patient Address
Dear Patients ,
I am writing to inform you that your Equipment Due Back is due for return by Date Due For Return. If there are any problems with returning the equipment, or if you would like to renew your loan please contact the hospital on 01303 277288.
Please return all equipment to the department that issued you with your Equipment Due Back, for information on hospital transport to and from the hospital please contact the transport department on 01303 277289.
Yours Sincerely
Mr. O. Johnson
Equipment Manager
Royal Victoria Hospital
3 Radnor Park Road
Folkestone
Kent
CT21 5EQ
This letter is a standard one which can be sent to all patients when their equipment is due back. I have not given an example of it in use but the text with grey background automatically changes for each patient, this is an extremely useful function as if I had not included it, the user would have had to write this letter out hundreds of times for each patient that has to return equipment.
Patient Search Facilities:
As the table of patient details will get extremely large when it is put into operation I thought that a search function would be extremely helpful. A search function in excel is easy to do by using a VLOOKUP command. The search table is on the same sheet as the patient details, but it is at the bottom of the sheet, this way it is easy to search and it will not get confused with the details table. The formula I have used is explained below:
The command line The absolute cell The range for
Telling the computer Reference for the where the table
What to do lookup value array is
=VLOOKUP($F$65521,A8:F29,1,FALSE)
Which column (of the Whether or not
Table) the required it must be an
Information is in exact match
I used this formula to create the patient search option, shown below:
I have chosen to make the search criteria the postcode as this is one of the most unique criteria I have, I originally had set it up to search by name, but realized this maybe ineffective if two people share a name. All the user has to do is enter the postcode of the required patient and all the information automatically is brought up.
Managers Area:
I have created a managers area to protect the reports from being printed by anyone who does not have the required authority. I have password protected the sheet so that only users with the password can access it (e.g. managers). Below is a screen shot of the sheet:
I have created the macro buttons as managers will not want to have to do anything complicated to access the information. Macro buttons are an easy way of creating a good user interface which is easy to use. The buttons do exactly what they say, from here you can print a weekly report, this includes information on where each individual piece of equipment is, who it is with, when it is due back, and if any equipment is being repaired. I have also added to this report so that it shows all the loans and returns in the last week, this is so managers can easily keep track of what is going on. Below is an example of a weekly printout:
Main Menu
The main menu was an important part of the program, as it is the first thing users see when loading it up and should set a good impression to new users. I used some images to create the user friendly look and feel. It contains only a WordArt title, two images and some macro buttons, I think that this is the best way to keep a main menu page, basic and simple.
Making the Spreadsheet User Friendly:
Throughout the program I have used the same colour scheme, so as not to confuse users and to keep a consistency. I have used lots of spaces (e.g. between buttons) so that everything is spaces out and easy to read. I have used the bold type for headings so that tables of data are easily identified and can be read with ease. I have included some clipart images so that the look and feel of the spreadsheet is a friendly one that encourages the user to use it. I have used a WordArt title on every sheet, this is one of the first things users will see, and it instantly gives them the title of what the page has been designed for. All my WordArt titles are large, with spaced lettering so that they are not too complex to read.
The loan equipment page has been made user friendly by varying the colours. The background is blue so for the cells in which data should be entered in, I have set them as white. This gives a clear message as to where to enter the data and should help to avoid mistakes.
Throughout the entire program I have included macro buttons, not only to perform challenging task and to save time but also so that the user can easily navigate around the program. On every page there are buttons which can take you back to the main menu or to another menu.
Security
To keep the spreadsheet secure from hackers or from people who are not officially allowed access to the database I have put a password on the spreadsheet, which only the users will know. There is also an additional password once you are into the program if you need to access the managers' area, this is to prevent normal workers from accessing managers' data. By putting passwords on the system it will comply with the data protection act. There are several rules for passwords, these include:
* Passwords should be at least 6 characters
* Passwords must not be displayed on the screen but must be masked by asterisks.
* Files containing passwords must be encrypted
* Passwords must be kept confidential
* Passwords must be changed regularly
Also for security reasons the company should make sure that the system is regularly backed up using a zip file on to disk. That disk should then be placed in a secure place preferably off site and in a fire proof secure box.
Further security precautions include:
* Keeping the room with the computer systems in securely locked and alarmed room when they are not being used.
* Installing a virus checker.
* Use the hospitals CCTV system to watch the machine
Testing:
Testing the Buttons:
Navigation Buttons:
I first tested the macro buttons which allowed users to navigate around the program, I did this by using a simple table showing where the buttons started and where they were meant to go. The table is included below:
From
To
Worked
Welcome Page
Loan Equipment
Yes
Welcome Page
Return Equipment
Yes
Welcome Page
Patient Records
Yes
Welcome Page
Patient Search
Yes
Welcome Page
Managers Area
Yes
Item Codes
Loan Equipment
Yes
Loan Equipment
Main Menu
Yes
Loan Equipment
Patient Detail
Yes
Loan Equipment
Patient Search
Yes
Loan Equipment
Return Equipment
Yes
Loan Equipment
Managers Area
Yes
Patient Details
Patient Search
Yes
Patient Details
Main Menu
Yes
Patient Details
Loan Equipment
Yes
Patient Details
Return Equipment
Yes
Patient Details
Managers Area
Yes
Patient Search
Patient Details
Yes
Patient Search
Main Menu
Yes
Patient Search
Loan Equipment
Yes
Patient Search
Return Equipment
Yes
Return Equipment
Main Menu
Yes
Return Equipment
Loan Equipment
Yes
Managers Area
Main Menu
Yes
Managers Area
Loan Equipment
Yes
Managers Area
Return Equipment
Yes
Managers Area
Patient Details
Yes
The results from my table show that they all work and therefore do not need modifying.
Operation Buttons:
I then went on to test the operational buttons. The results are shown below:
* Loan Equipment Submit - This button was designed to transfer the data input from the loan equipment sheet to the table in the patient details sheet. I tested this by entering 5 different patient details and then selecting the patient details sheet to view the table. It was successful and the 5 patients appeared in the table
* Loan Equipment Clear - This button clears the contents of the data entry sheet for the loan equipment, I have set the previous macro to do this after transferring, but if some faulty information has been entered it is a useful button. To test this I entered some random text and then clicked the button. It passed the test, clearing the required cells.
* Patient Details Sort - This was designed to sort all the patients in the patient details table by the date their equipment was due back, it was meant to sort them by ascending order. To do this I needed many patients to have been entered in the table, so I entered 20 and pressed the button. It did not work properly, sorting the patients by descending order so the patients that had the longest loans at the top, rather than they way I had wanted it. This was easily changed, I edited the macro so that I sorted by ascending order.
* Equipment Due For Return - This macro copied the patient details from the patient details sheet into the expected returns sheet and table, the purpose of this was to show which patients needed to be sent a reminder letter. To do this, as I already had 20 patients in the table I clicked the button. It worked perfectly and transferred the details.
* Clear Table - This is supposed to clear the expected returns table, as I had a table full of patients I tested it by just clicking on it, and it worked fine, clearing the contents.
* Issue Letter - This macro was designed to automatically print a letter to the patient highlighted reminding them about the return of their equipment. I tested this by clicking on it and it worked but not as I had planned. It successfully opened the mail merged letter and input the required patient details, but didn't print. I could not find a way around this so the user just has to press the print button when the letter opens.
* Print Weekly Report - This button prints a weekly report of all the equipment and loans/returns. I tested it by inputting a few patients with return dates of this week and pressing the button. It worked and I got a printout of the required information.
* View Location of All Equipment - Again a macro from the managers area, this brings up a table showing the location of all equipment. I tested this by clicking on it and it successfully told me where every piece of equipment was and who it was with.
Testing Validation Rules:
I have set up several validation rules to prevent accidental errors and mistakes, to test these I have entered deliberate faulty data to see if my system picks up on it.
* Date of Birth - Should only allow dates to be entered, I entered "hello" and got the error message telling me I can only enter a date
* Town - This only allows values from the drop down menu, I typed in "Manchester" and an error message appeared telling me that this town was out side the attachment area for this hospital.
* County - This only allows Kent, so I entered Sussex, but again it picked it up giving me an error message explaining that the county was not in the hospitals attachment area.
* Contact Number - Only allows telephone numbers, I entered the value "123four", an error message appeared informing me that only phone numbers were allowed to be entered in this cell
* Ward Number - Only allowing values from the drop down menu I entered ward 11 (drop down menu goes up to Ward 10), it returned an error message saying invalid ward number
* Patient Equipment - This is also a drop down list allowing only the values from it. To test it I entered "bandage" but again the test proved correct and informed me that the value I entered was invalid
* Loan Duration - I have set a validation rule on this cell to only allow positive integers. To test it I entered first "-2" and then "1.5" but both times it returned an error message saying that they were invalid and to enter a positive integer value.
Maintenance:
Maintenance is important as it ensures the long life of the system, e.g. If the business changes so may the requirements of the system, by resolving bugs or program errors, which arise after the system has been implemented, and to enhance the functionality of the system when users require it.
I am not required to produce user documentation and system documentation. However if we had been required to produce documentation I would include the following:
User Documentation
* Installation Instructions
* Instructions on how to start and run the system. As well as a guide for using the different functions.
* Over view of the system and what it does
* Trouble shooting guide on possible errors and how users should deal with them
System Documentation
* Data Flow diagrams
* Data entity diagrams
Computing Paper 3 Coursework 1