Table design
Timetable Design
Total Wage Table Design
Letter design
Hand drawn plans of the letters, as stated in ‘Output’, are on separate pieces of paper. The data source is a database stored in MS Access, which I can select certain parts of the applicant’s data and mail merge it onto the following letters:
- Interview
- Success
- Rejection
- Rejection but hold
In order to mail merge certain bits of data successfully on each of the letters above, the following letters:
On all of the letters
- Title
- First
- Last
- Address 1
- Address 2
- Address 3
- Post Code
- Post Applied
Interview
Success
- Working Hours per Week
- Hourly Wage
- Start date
- Start time
The system must select certain applicants by using parameter query. The query must be simple to use, but the user must have sufficient spelling skills. This is because the search can only show records of certain applicants with certain jobs applied if the spelling of the job title is correct.
The toolbar design should be simple to use. The toolbar will have the following functions:
- Parameter query
- Mail merge to new document
- Save new document as
- Print mail merged document
These functions will be used because the user can still do the mail-merging, save and printing tasks without the system being modified or altered unauthorised. Only Enricé is allowed to alter or modify the system.
On the envelopes, provided with each letter template, the sender’s address is positioned on the top left underneath ‘If undelivered, please return to’. The recipient’s address is positioned in the middle of the envelope. Since the envelope is a template, the recipient’s address consists of mail merge fields.
Menu design
The menu design will consist of restricted normal functions, by using password protection. The users can only use the following functions provided:
Letter Templates
- Assistant Chef
- Master Chef
- Bar Staff
- Bouncer
- Washer-up
- Cleaner
- Waiter(ess)
- View Data
- Previous Record
- Go to Record
- Next Record
- Mail Merge and Print
Welcome to Enricé’s Tapas Bar
- Interview Letter
- Success Letter
- Rejection Letter
- Lifeline Letter
- Job Application Form
- Interview Notes
Enricé’s Tapas Bar Application Form
Enricé’s Tapas Bar Interview Notes
Macro design
Job Position macros:
- Assistant Chef
- Master Chef
- Bar Staff
- Bouncer
- Washer-up
- Cleaner
- Waiter(ess)
They all have the same function procedures. These macros must be able to mail merge individual records, even though more than one person as applied for the same job position. This is crucial if Enricé wants to send letters to individuals who applied for the same job position.
Automation macros, in each of the letter templates, job application form template and front end file:
- Interview letter
- Success Letter
- Rejection Letter
- Lifeline Letter
- ‘Welcome to Enricé’s Tapas Bar’
- Enricé’s Tapas Bar Application Form
This is because Enricé wants to restrict all functions except the ones provided. This means that the user cannot use other functions to alter and/or modify the files, except Enricé, as he has the authority to do so.
Open file macros in ‘Welcome to Enricé’s Tapas Bar’:
- Interview letter
- Success Letter
- Rejection Letter
- Lifeline Letter
- Application Form
These macros can open the letter templates quickly without going to ‘File’, clicking ‘Open’, selecting the appropriate folder and then the file. This means that the user can use the job application system more efficiently.
Mail merge macro:
This macro makes the system work more efficiently by reducing the procedures the user has to go through. This reduces the probability of making a mistake, which is time consuming to correct.
Query set macro
- Assistant Chef
- Master Chef
- Bar Staff
- Bouncer
- Washer-up
- Cleaner
- Waiter(ess)
This is because if there are limitations of ‘Job Position’ macro, this macro can limit the search of a job post to a number of applicants who have applied for that job. This is because the user can search the applicant more easily as the query narrows the search of an applicant.
Previous/Next record macro:
After the user has mail merged a record, the next record can be selected by using these macros. Aided with ‘Show Fields and Values’, the user can check if the current record has already mail merged already.
Show Fields and Values macro:
The user can view in advance the mail merged record before mail merging the record for real. This is because they can check if the current record has already been mail merged.
Print Form/Interview Notes macro:
The user can print application forms/interview notes quickly and conveniently when needed. This saves the users time and effort, reducing stress for them.
All designs are on separate sheets.
Test Plan
These headings above will be used because I need to know the expected and actual outcome of the tests I conduct, so I can correct anything, if necessary. I will know how many tests I did before I solve any problems I will come across, know what factor I am currently testing and know how I conducted each of the tests. I will also know if I have met the user requirements.
Example
The following features will be tested:
- In letter, job application form and front end templates:
- Query set (Selecting Job Position):
- Assistant Chef
- Master Chef
- Bar Staff
- Bouncer
- Washer-up
- Cleaner
- Waiter(ess)
- Mail merge
- Previous record
- Next record
- Show Fields and Values
- In Enricé’s Tapas Bar - Application Form
- In Enricé’s Tapas Bar – Interview Notes
- In Welcome to Enricé’s Tapas Bar (Front End)
- Interview Letter
- Absolute Success Letter
- Absolute Rejection Letter
- Lifeline Letter
- Job Application Form
- Interview Notes
- In Enricé’s Tapas Bar Database
- Autoexec
- Data Source
- Enricé’s Tapas Bar Parameter Query
- Enricé’s Tapas Bar Timetable
- Enricé’s Tapas Bar Total Wage
- Data Source Form
- Save Record (In Data Source Form only)
- Validation of the following fields:
- Title
- Date of Birth
- Age
- Post Applied
- Date of Application
- Interview
- Date of Interview
- Offer Job
- Start Date
- Working Hours per Week
- Hourly Wage
- Format of the following fields:
- Post Code
- Phone Number Home
- Phone Number Work or Mobile
- Date of Birth
- Age
- National Insurance Number
- Date of Application
- Date of Interview
- Start Date
- Start Time
- Working Hours per Week
- Hourly Wage
- Input mask for the following fields:
- Post Code
- Phone Number Home
- Phone Number Work or Mobile
- Date of Birth
- National Insurance Number
- Date of Application
- Date of Interview
- Start Date
- Start Time
- Password protection in the following files:
- Enricé’s Tapas Bar - Interview letter
- Enricé’s Tapas Bar - Success Letter
- Enricé’s Tapas Bar - Rejection Letter
- Enricé’s Tapas Bar - Lifeline Letter
- Enricé’s Tapas Bar - Application Form
- Enricé’s Tapas Bar – Interview Notes
- ‘Welcome to Enricé’s Tapas Bar’ (Front End)
The macros will be tested, by creating the macro then executing them. Then record the result of the outcome and compare them to the expected outcome. If necessary, correct the macros by changing the codes.
The validations will be tested, by typing up the validation rule then type in test data to see if the validation rule works. The result will be recorded and compared to the expected outcome. If necessary, correct the validation rules by changing the symbols and characters.
The formats will be tested, by typing up the format code then type in test data to see if it works. The result will be recorded and compared to the expected outcome. If necessary, correct the format codes by changing the characters.
The input masks will be tested, by typing up the input mask then type in test data to see if it works. The result will be recorded and compared to the expected outcome. If necessary, correct the input mask by changing the characters.
Password protection features will be tested, by typing in a password to protect the file. Then the results will be recorded and compared to the expected outcome. If necessary, change the method of password protection for certain files, which certain macros may not work if the file is fully protected.
Implementation
I have included the following features in the system in order to make it robust as possible:
- Validation for the following fields in data source and form:
- Title
- Date of Birth
- Age
- Post Applied
- Date of Application
- Interview
- Date of Interview
- Offer Job
- Start Date
- Working Hours per Week
- Hourly Wage
- Format of the following fields:
- Post Code
- Phone Number Home
- Phone Number Work or Mobile
- Date of Birth
- Age
- National Insurance Number
- Date of Application
- Date of Interview
- Start Date
- Start Time
- Working Hours per Week
- Hourly Wage
- Input mask for the following fields:
- Post Code
- Phone Number Home
- Phone Number Work or Mobile
- Date of Birth
- National Insurance Number
- Date of Application
- Date of Interview
- Start Date
- Start Time
- Password protection in the following files:
- Enricé’s Tapas Bar - Interview letter
- Enricé’s Tapas Bar - Success Letter
- Enricé’s Tapas Bar - Rejection Letter
- Enricé’s Tapas Bar - Lifeline Letter
- Enricé’s Tapas Bar - Application Form
- Enricé’s Tapas Bar – Interview Notes
- ‘Welcome to Enricé’s Tapas Bar’ (Front End)
- In letter, job application form and front end templates:
- Query set (Selecting Job Position):
- Assistant Chef
- Master Chef
- Bar Staff
- Bouncer
- Washer-up
- Cleaner
- Waiter(ess)
- Mail merge
- Previous record
- Next record
- Show Fields and Values
- Print Form/Interview Notes (In Enricé’s Tapas Bar - Application Form and Interview Notes Only)
- Open Files:
- Interview Letter
- Absolute Success Letter
- Absolute Rejection Letter
- Lifeline Letter
- Job Application Form
- Interview Notes
- In Enricé’s Tapas Bar Database
- Autoexec
- Data Source
- Enricé’s Tapas Bar Parameter Query
- Enricé’s Tapas Bar Timetable
- Enricé’s Tapas Bar Total Wage
- Data Source Form
- Save Record (In Data Source Form only)
- Calculating total wage in MS Access in Total field (only in Enricé's Tapas Bar Total Wage query)
These features are included because in order for the system to be robust, users cannot delete, alter or modify data, letter templates and job application form templates, as these are password protected. Only Enricé has the authority to perform any changes of the system. They cannot input the wrong type of data into the wrong fields in the data source and the input masks help the users to enter the right type of data into the right field. They can only use the functions I have provided above, from query set to print form macros. The macros above will perform tasks quickly and conveniently.
The details of how these advanced features are on separate sheets.
Test Plan
User Guide
The details of explaining how to use the system created are on a separate booklet. The user guide contains the following:
- Introduction
- System Requirements
- How to:
- Install the software
- Link the letter templates to the database
- Open files from the front end
- Input and delete data
- Find records of applicant’s by job position
- Mail merge applicant’s details onto letters
- Print details and application forms
- Output of data in the following ways:
This is because to state what actions the software performs. If the user wants help about a certain problem, they can solve it by following instructions under troubleshooting, and find the information of performing certain actions clear, as instructions of individual actions are on separate pages. Find out if their computer has sufficient performance in order to run the program under system requirements. It provides information about on which pages each of the actions are on, to help users find the page of an action. Finally, it has a front cover to make the user guide look professional.
Evaluation
About the User Requirements
The following user requirements have been achieved:
- Created dropdown menu for job posts by typing creating a field ‘Post Applied’, selected ‘Combo Box’ and then selected ‘Value List’. Then typed "Master Chef";"Assistant Chef";"Bar Staff";"Cleaner";"Waiter(ess)";"Washer-up";"Bouncer". When the dropdown menu is selected, the list of job posts appears.
- Enter applicant’s details onto a database via a form. Then the data is transferred to the table after a record is saved.
- Validate and verify data entered onto the database by typing in formulas and messages. The formulas ensure that correct data is accepted or otherwise a message appears, informing that irreverent data has been entered. The message also states the correct type of data needed to be entered.
- Typed up a letter, one for successful applicants and one for unsuccessful applicants, thanking them for their interest. Another two letters are needed, one for unsuccessful applicants after the interview, thanking them for their interest, and one unsuccessful applicants after the interview but they will be contacted at a later date for further information. Each of the letters for each role has been created by typing the outcome of Enricé’s decision in an appropriate language. Fields have been included in each of the letters to make it personal and for convenience, so the user does not have to type in applicants’ details again and again.
- Mail merge applicant’s details onto the letter (start dates, etc). The mail merged letter has to be saved automatically. This action is done by creating macros (see Appendix 4 - Mail Merge Macro, Appendix 5 - Previous Record Macro, Appendix 6 - Next Record Macro and Appendix 7 - Query Set Macro), which can aid the user to select certain applicant and mail merge their details. Then the mail merged letter is printed automatically. The message box will appear, informing the user that the letter will be printed.
- The database can calculate weekly wages from the number of hours applicants like to work and the hourly rate of pay automatically. This problem has been solved by typing ‘Total: [Data Source].[Working Hours per Week]*[Hourly Wage]’ in the new column in the design part of ‘Enrice's Tapas Bar Total Wage’. Then the box, which represents ‘Show’, has been ticked in order for the total amount of wage for each employee to show on the screen and on paper when it is printed.
- Provide interview times, with notes. Interview times are included, once the applicant has confirmed the time they are available for an interview. Interview notes are printed pressing a button in the Enricé’s Tapas Bar Interview Notes file. The notes contain any extra information the applicant needs.
- Use macros to save letters to an archive folder. This requirement is solved by including a save procedure in the Mail Merge Macro (see Appendix 4 - Mail Merge Macro). After the letter has been printed, ‘Save As’ box appears, so the user can save the mail merged letter into an archive folder. Once the letter has been saved, the message box appears, confirming that the mail merged letter has been saved.
- Modify tool bars. This has been achieved by creating toolbars with provided functions so the user can use these functions for convenience while the files are password protected
- The system needs to be robust by password protection, save back up copies onto a stand-alone computer and functions restricted by macros. The system is robust because the files are password protected fully, except letter templates as they do not work (see Test 52 to 54 - Mail Merge Macro in Protection Mode), automation macros restrict functions by closing unneeded toolbars automatically and actual copies of the system are saved onto CD-RW and onto a ISP server as attachments for maximum protection, in case of a virus outbreak as it could corrupt important files.
The only user requirement not achieved is creating an online application form because of the complexity of setting up a website, linking it to the server and linking the server to a specific folder on the computer.
Accuracy of the system
The system created can mail merge applicant’s details onto letters, depending on situations. The mail-merged letter is saved automatically, but the user can type in the ‘File Name’ box the applicant’s name and then save the letter as the applicant’s name.
Individual records of applicant’s can be selected by setting the query to a certain job position and then using the Previous/Next record and Show Fields and Values macros ensures the right applicant is selected.
The toolbars close automatically when the files open and the message box appears confirming that the file has opened, but the toolbar with provided functions open to let the user to carry out the tasks quickly and conveniently. The tasks are mail merge applicants’ details letters print mail-merged letters, view details before mail merging, selecting a job position, printing application forms and interview notes and opening files from the front end.
In the data source in MS Access, message box appears confirming that the database has opened and then the toolbars open. The form opens automatically and the message box appears confirming that the form has opened. The toolbars contains the following tasks; opening data tables and queries in read-only mode. Input data via a form, searching records and saving records. When a file in MS Access opens, a message box opens confirming that the file has opened.
The toolbars open automatically when the file closes, for example, Enricé’s Tapas Bar – Interview Letter. Then the toolbar with functions provided closes. Before the file closes, message box appears, saying ‘Goodbye’ to the user. In MS Access, when the database closes, the toolbars with provided functions closes.
Executing the Print Application Form/Interview Notes macro prints application forms/interview notes.
Individual files can be opened from the front end by executing Open File macros.
Application Form, Interview Notes and the front-end files are fully protected by passwords.
The database can calculate the total wage of each employee by multiplying the number of hours the employee works and the amount of pay the employee receives each hour.
Certain data can be viewed by opening queries or using parameter query to search particular records, depending on the information requested. All of the data of each applicant can be viewed by opening the data source table. Applicant’s details can be inputted onto an electronic form in MS Access. When the Query macro is executed, the search dialogue box appears, so the user can type in a particular job position required.
The letter templates cannot be protected fully because the mail merge function and the macros do not work. The database also cannot be protected fully because of the complexity of MS Access functions. Instead, the files in the database are hidden to provide protection of records against modification and/or alteration.
The website for the application form cannot be created due to the complexity of HTML and connecting the website to a server.
Changes to the original system
The design of the system was changed in the following ways; the toolbars in the database has been added because of security reasons. This prevents the user from opening the tables and queries to modify and/or alter data. The functions on the toolbar which open tables and queries in read-only mode for complete protection of the data.
A form was created because of security reasons above, provide an easy interface for the user to input data into the database and to ensure the only way to input data into the database is by entering applicant’s details onto the form.
In order to select an individual applicant, the design of original Job Position macro had to be abandoned. Instead, designs Previous and Next macros and Query macros were taken into account and have been created to solve the problem. This is because an individual applicant’s details can be mail-merged onto the letter instead of mail merging all of the applicants’ details from the database onto multiple letters.
The user requirements stated that interview notes had to be printed out for interview purposes. The file has been created and a print macro has been created to solve the problem encountered.
The user requirements also stated that the timetable was needed to track which interviews were already conducted and to see if there are any future interviews. Creating the query table for the purpose in MS Access has solved the problem.
Users Comment and Results about the System
The pie chart on the right enforces the fact that the system is very easy to use. This is because of clear symbols to inform them which file is which, message boxes to inform the user the file is opened or closed and to inform them that they have executed a particular action.
The user comments below suggest this:
User A – ‘Clear symbols and easy to understand.’
User B – ‘Clear symbols, not much that you can press to go wrong.’
User C – ‘Clear information and very detailed.’
User D – ‘Clear and easy to understand.’
The users’ comments above and the pie chart on the right suggest that the user guide about the system is easy to understand and use. This is because the user guide contains comprehensive information about how to use the function of the system, what the outputs should look like on screen and paper and trouble shooting information in case the system goes wrong for the user.
30% of the users of the system suggested an improvement to the system; toolbars should stay closed when a file has closed. This is because the letter templates could be vulnerable to alteration or modification. This problem could be solved by modifying Auto Close macros in the following files, to ensure that the toolbars stay closed in the front end; Absolute Rejection Letter, Absolute Success Letter, Interview Letter, Lifeline Letter, Job Application Form and Interview Notes.
Improvements to the System
The system could be improved by protecting the letter templates and the database completely by using VBA and password protecting the PC, since the system is designed to be used on a stand alone PC.
The limitations of the system are the system can be altered by a determined hacker, no matter how well the system is protected in any way.
AS Coursework (ICT3) - Word Processing Project Martin Yau - Candidate Number: 6079
