The disadvantages are that when the form of the spreadsheet has to be designed the cells have to be stretched or shrunk to fit the correct size, unlike a database in which the form can be easily changed. To sort on in a spreadsheet you must remember to highlight the required area otherwise it sorts on one column only.
Word processor
This will be used to write up all the documentation about the project. A word processor would be used to produce the hardcopy letter to be sent to each centre. Microsoft Word will be used because it gives us the ability to retrieve information from the database using mail merge so thus producing hardcopies for each centre. Alternatively the letter could be saved as a text document and linked to visual basic.
Programming language
This could be used to set up the layout of the user interface and perform the functions of the task. The programming package that could be used is Visual Basic. Visual basic allows you to match the interface to the system requirements. The advantages are that the program can be changed to fit the task exactly. The program allows the least computer literate person to use the program. This is because all of the complicated workings are hidden behind a friendly user interface. Visual basic could be used to launch other applications and process several types of information for example word or excel.
The disadvantages are that a lot of time would have to be put into the programming of the program to get it to run well and not have too many bugs. Updating of the program by an untrained person is more difficult.
Inspiration
The software package called Inspiration will be used to create the flowchart and link diagrams in my documentation. Inspiration is a useful tool because it gives you all the flowchart boxes that you require and you can produce arrows with writing along them so you can produce the yes/no arrows. Inspirations will also be used to create the top-down design. The program gives you a top-down design option, which produces a very professional piece of work.
Using a combination of application programmes will solve the problem. The database called Microsoft access and a programming language called visual basic will be used for the interface. These will be linked by an ADODC in visual basic. A letter will be produced in word using mail merge with database.
User interface design including outputs, forms and reports.
Four different types of internal reports will be produced:-
- A daily list of any re-marks completed where a mark change has affected the grade. The printout needs to contain the unique number, centre number, candidate number and name, subject, original grade and new grade.
- A daily list of re-marks still outstanding, i.e. that have not been completed within a three-week period. The printout needs to contain the unique number, centre number, candidate name and number, subject and the date it was requested.
- The remark list for a particular subject will contain subject number, centre number, centre name, candidate number and the unique number.
- The remark request form for a particular centre will contain the centre number, centre name, candidate number, subject number and unique number.
All forms will have a clear title and a similar format. The exam result grade form will allow examiners to edit grade boundaries and add additional subjects to the menu. The forms will also all have common command buttons such as add and exit.
The re-mark exam report letter to be sent to the centre is shown below: -
Logo
UKAB Details
Date
School Name
Address
Centre Number
Dear Examination Secretary,
Please find below the remark results, for the named candidate.
Candidate number,
Candidate name,
Subject,
Original mark,
Original grade,
Re-mark mark,
Re-mark grade.
Add one of the following sentences:
The candidates grade has not changed.
The candidates mark and grade have changed.
Yours sincerely,
Signature (scan in).
Name
UKAB Re-mark Co-ordinator.
Method of data entry, including validation.
It is assumed that the system will be live and a trained operator will be entering data into the programme. To reduce data input error a pick list, for the subject code, has been placed in this field, see print screen below. Errors in entering data are reduced by the use of validation checks on the system and also visually by an experienced operator. Data validation for the mark and re-mark results will be between 0 and 100. If the number typed in doesn’t fall within this range an error message will occur as shown below.
The centre number range is between 10000 and 99999. If the number entered doesn’t fall within the range an error message will appear on screen, as shown below.
The print screen below shows the set up for the candidate validation checks.
The exam centre could also set the system up so that a letter is generated and sent to the exam secretary of the centre requesting the remark informing them of all the information they have about the candidate. This can be used as an additional validation check. The secretary would contact UKAB if there were any incorrect data.
Record structure, file organisation and processing.
The fields in my database for the candidates are:
ID
Centre Number
Centre Name
Address 1
Town
County
Post Code
Candidates Number
First Name
Surname Name
Subject Reference Code
Original Mark
Original Grade
Re-mark Mark
Re-mark Grade
Date re-mark Requested
Date re-marked
Re-marked inside three weeks
Return Script
More fields have added to the required ones because these would be needed to be stored in a database on the Candidates. These are things such as address of the centre, the town and county. The address is made up of four fields. To help identify candidates more easily their forename and surname have been added. These extra fields are required because the UKAB would need to send back the re-marked results to the centre and would need to know the address of the centre. Date re-mark requested and date re-marked has also been added to allow for the calculation of the number of weeks and days that it has taken to re-mark the work. This will tell us if it has been completed within the three-week period. A unique number has been added because candidate numbers are only unique in centre but not between centres. It is therefore easier to search on unique number rather than candidate number.
A table called grade boundaries has also been created. This contains the subject reference code and grade boundaries for each grade in that subject. The candidate and grade boundary tables are linked together via the subject code field, see print screen below.
Security and integrity of data.
To improve security a password could be added to stop unauthorised users opening and editing the data. The print screens below show the security check that has been added to the system. It currently works on one individual name only.
The network manager could add more security by giving different levels of access to employees thus allowing certain individuals the ability to view and not change the data. Employees will also have their own log on identification code, which will also help to improve security. The system should be housed in a secure room with limited access. Regular backups are required and need be stored in a separate fire safe unit. The trained operator will also need to make regular saves of the information to reduce loss of data due to unforeseen incidence. Data entry errors have been reduced by the use of validation checks. The forms will be clear and easy to use thus reducing input error.
System Design.
Implementation / Testing.
Below is the user interface showing how all the pages link to each other.
Most of the problems have been solved using queries in access. The following queries have been generated: -
The following screen shot shows the query for overdue scripts. The query used to calculate late returns was : DateDiff(“d”,[candidate]![Date re-mark requested],[Todays date]) with the sort criteria>21.
The following query shows the subject reference code. The query criteria is you enter the subject reference code into an input box.
The change of grade query compares the original grade to the re-mark grade and shows any grades that have changed.
The following print screen shows the different reports generated in access.
The design view of the report shows how the report has been set up. All the reports have a current date = now( ) in the footer to allow the exam board to analyse the current reports.
Test Data.
The following data will be used to test the system.
Comments about test data used.
Error messages are expected for the following records: -
- Unique record number 11 – too many digits in centre number.
- Unique record number 14 too few digits in centre number.
- Unique record number 9 too many digits in re-mark grade.
All these errors should be picked up by the validation checks.
Unique records numbers 1, 7 and 16 all have the same candidate number but come from different centres. The system should be able to cope with this because of the unique number field. Whilst unique record numbers 2 and 4 have the same surname and come from the same centre. The system should be able to distinguish between these two candidates because of the unique number field. The same applies to unique record numbers 10 and 12. Whilst unique record numbers 3 and 9 have the same surname but come from different centre numbers.
The following two print screens show that the system can cope with these problems through the use of unique numbers.
Same exam centre / same surname.
Same candidate number / different exam centres.
Expected Printouts.
The following printouts are expected from the test data:-
- Re-marks where a mark change has affected the grade – Grade up - unique record numbers 4,6,9. Grade down – unique record numbers 2,8,and 15.
The expected results were obtained. See print screen below.
- Re-marks not completed within 3 weeks – unique record numbers 3, 5, 10 and 16.
The expected results were obtained. See print screen below.
- Re-marks requested for subject code 20094 – unique record numbers 2,8 and 13.
The expected results were obtained, see print screen below.
-
Re-marks requested for centre number 11000 – unique record numbers 5, 6, 7 and 8.
The expected results were obtained
The ABUK will be returning scripts to the following exam centre.
The expected results were obtained from the data using the access query fomr. See appendix A for hard copies of the reports produced.
The comment “the grade remains the same” should be printed on letters to candidates 1, 7, 11 and 13. The comment “the grade has changed” should be printed on letters to candidates 2,4,6,8,9 and 15.
Insert text was used to generate am if then else statement. An extra field was placed into the database – candidate. The user enters 1 if the grade changed and 0 if the grade did not change. The statement used was: “If grade changed = 1 then write “ the candidates grade has changed” else write “the candidates grade has not changed.” The test data worked as expected. See appendix A for printout of reports generated.
Hard copy of visual basic code.
The following shows the visual basic code for each separate form. The intention is that the user interface will run and carry out all the processes via buttons on the form. In the final solution the user will not have to enter anything via access. In form 1 there is the add, print and calculation buttons. The add button allows you to enter a new candidate into the database. The calculation button calculates the re-mark grade from the re-mark mark. The forms are as follows: -
Form 1 – candidate information and calculation of grade.
Form 2 – allows users to view centre information
Form 3 – allows user to view the grade boundaries
Form 4 – allows user to view script return requests
Form 5 - allows user to view work not remarked within 3 weeks
Form 6 – re-marked requested for a particular subject
Form 7 - re-marked requested for a particular centre
Code for front page – The first part of the code incorporates a password for security reasons.
Private Sub CmdBoundries_Click()
Load Form3 'open form 3
Form3.Show
End Sub
Private Sub CmdCandidate_Click()
UserName = InputBox("Enter your first name.")
If UserName = "Michael" Then
MsgBox ("Welcome, Michael")
Else:
MsgBox ("Sorry you can not use this program")
End
End If
Load Form1 'open form 1
Form1.Show
End Sub
Private Sub CmdCentre_Click()
UserName = InputBox("Enter your first name.")
If UserName = "Michael" Then
MsgBox ("Welcome, Michael")
Else:
MsgBox ("Sorry you can not use this program")
End
End If
Load Form2 'open form 2
Form2.Show
End Sub
Private Sub CmdQuit_Click()
End 'End program
End Sub
Private Sub Command1_Click()
UserName = InputBox("Enter your first name.")
If UserName = "Michael" Then
MsgBox ("Welcome, Michael")
Else:
MsgBox ("Sorry you can not use this program")
End
End If
Load Form4 'open form 4
Form4.Show
End Sub
Private Sub Command2_Click()
UserName = InputBox("Enter your first name.")
If UserName = "Michael" Then
MsgBox ("Welcome, Michael")
Else:
MsgBox ("Sorry you can not use this program")
End
End If
Load Form5 'open form 5
Form5.Show
End Sub
Private Sub Command3_Click()
UserName = InputBox("Enter your first name.")
If UserName = "Michael" Then
MsgBox ("Welcome, Michael")
Else:
MsgBox ("Sorry you can not use this program")
End
End If
Load Form6 'open form 6
Form6.Show
End Sub
Private Sub Command4_Click()
UserName = InputBox("Enter your first name.")
If UserName = "Michael" Then
MsgBox ("Welcome, Michael")
Else:
MsgBox ("Sorry you can not use this program")
End
End If
Load Form7 'open form 7
Form7.Show
End Sub
Private Sub Form_Load()
End Sub
Code for form 1
Private Sub CmdQuit_Click()
Form1.Hide 'Hide form 1
End Sub
Private Sub Command1_Click()
promt$ = "Enter the new record, and then click the left arrow button."
reply = MsgBox(Pompt$, vbOKCancel, "Add Record")
If reply = vbOK Then 'if the user clicks ok
Text6.SetFocus 'move cursor to Title box
Adodc1.Recordset.AddNew 'and get new record
End If
End Sub
Private Sub Command2_Click()
PrintForm
End Sub
Private Sub Command3_Click()
If Text8 <> "" Then
If Text3 = 1325 And Text8 >= 75 Then Text5 = "A"
If Text3 = 1325 And Text8 < 75 Then Text5 = "B"
If Text3 = 1325 And Text8 < 67 Then Text5 = "C"
If Text3 = 1325 And Text8 < 60 Then Text5 = "D"
If Text3 = 1325 And Text8 < 54 Then Text5 = "E"
If Text3 = 1325 And Text8 < 48 Then Text5 = "U"
If Text3 = 20094 And Text8 >= 70 Then Text5 = "A"
If Text3 = 20094 And Text8 < 70 Then Text5 = "B"
If Text3 = 20094 And Text8 < 60 Then Text5 = "C"
If Text3 = 20094 And Text8 < 50 Then Text5 = "D"
If Text3 = 20094 And Text8 < 40 Then Text5 = "E"
If Text3 = 20094 And Text8 < 30 Then Text5 = "U"
If Text3 = 28181 And Text8 >= 90 Then Text5 = "A"
If Text3 = 28181 And Text8 < 90 Then Text5 = "B"
If Text3 = 28181 And Text8 < 78 Then Text5 = "C"
If Text3 = 28181 And Text8 < 66 Then Text5 = "D"
If Text3 = 28181 And Text8 < 54 Then Text5 = "E"
If Text3 = 28181 And Text8 < 42 Then Text5 = "U"
If Text3 = 54821 And Text8 >= 85 Then Text5 = "A"
If Text3 = 54821 And Text8 < 85 Then Text5 = "B"
If Text3 = 54821 And Text8 < 79 Then Text5 = "C"
If Text3 = 54821 And Text8 < 74 Then Text5 = "D"
If Text3 = 54821 And Text8 < 64 Then Text5 = "E"
If Text3 = 54821 And Text8 < 55 Then Text5 = "U"
If Text3 = 64773 And Text8 >= 68 Then Text5 = "A"
If Text3 = 64773 And Text8 < 68 Then Text5 = "B"
If Text3 = 64773 And Text8 < 60 Then Text5 = "C"
If Text3 = 64773 And Text8 < 52 Then Text5 = "D"
If Text3 = 64773 And Text8 < 46 Then Text5 = "E"
If Text3 = 64773 And Text8 < 40 Then Text5 = "U"
End If
End Sub
Code for form 2
Private Sub Command1_Click()
Form2.Hide 'Hide form 2
End Sub
Private Sub Command2_Click()
promt$ = "Enter the new record, and then click the left arrow button."
reply = MsgBox(Pompt$, vbOKCancel, "Add Record")
If reply = vbOK Then 'if the user clicks ok
Text1.SetFocus 'move cursor to Title box
Adodc1.Recordset.AddNew 'and get new record
End If
End Sub
Private Sub Form_Load()
End Sub
Code for form 3
Private Sub Command1_Click()
Form3.Hide 'Hide form 3
End Sub
Private Sub Command2_Click()
promt$ = "Enter the new record, and then click the left arrow button."
reply = MsgBox(Pompt$, vbOKCancel, "Add Record")
If reply = vbOK Then 'if the user clicks ok
Text1.SetFocus 'move cursor to Title box
Adodc1.Recordset.AddNew 'and get new record
End If
End Sub
Private Sub Form_Load()
End Sub
Code for form 4
Private Sub Command1_Click()
Form4.Hide 'Hide form 4
End Sub
Private Sub Form_Load()
End Sub
Code for form 5
Private Sub Command1_Click()
Form5.Hide 'Hide form 5
End Sub
Private Sub Form_Load()
End Sub
Code for form 6
Private Sub cmdFind_Click()
prompt$ = "Enter the subject reference code"
SearchStr$ = InputBox(prompt$, "Subject Search")
Recordset.Find = "Subject code"
Recordset.Seek "=", SearchStr$
If Recordset.NoMatch Then
Recordset.MoveFirst
End If
End Sub
Private Sub Command1_Click()
Form6.Hide 'Hide form 6
End Sub
Private Sub Form_Load()
End Sub
Code for form 7
Private Sub Command1_Click()
Form7.Hide 'Hide form 7
End Sub
Private Sub Command2_Click()
prompt$ = "Enter the centre number"
SearchStr$ = InputBox(prompt$, "Centre Search")
Recordset.Index = "Centre no"
Recordset.Seek "=", SearchStr$
If Recordset.NoMatch Then
Recordset.MoveFirst
End If
End Sub
Appendix A – Hard Copies of Test Data.