In the spreadsheet I will have to design to store all the data I will need the following field names:
Specification
When my marking system is completed it should be able to:
- Store details of every single pupil accurately and efficiently without errors or loss of data.
- Saving a table of 30 pupils, opening it the next day and seeing if any information has been lost.
- Seeing if someone else can read and understand my system only using the user guide provided.
- Be able to be changed easily in case of expulsion/or school change without difficulty.
- Wait until a pupil is expelled or changes school and then see if any problem arises in changing the pupil’s data.
- Be able to easily and quickly add new pupils or deleted old pupils without too much hassle.
- Wait until a new pupil arrives or a pupil leaves and record any problems or errors occur.
- Be simple and easy for the operators to use without any errors slowing him/her down.
- Ask the operator if he/she has encountered any problems that even the user guide cannot explain. If there are any the system will have to be improved.
- Be able to search for any pupil’s name, results, grades, age etc in the entire database without any errors.
- Again getting anyone to see if they can run the searches successfully using only the provided user guide.
Hardware/Software
To put all the data and information on to a database you would need:
Hardware
-
A normal 14” monitor. An Adi ProVista would be suitable.
- A processor linked to a network so that all the information can be shared with any other operators in the park.
- A standard mouse with pad (or maybe as the company advances a touch screen for easy, effective use).
-
A base unit (such as Firstec Computers, energy EPA pollution Preventer or any others with quite a large memory for any amount of members e.g.40GB) which has a basic floppy disk drive, a basic CD-ROM and a basic graphics card so that photos of the pupils may be displayed.
- A quality printer which would need to be joined to the network so that any information can be printed off at any time.
-
A standard keyboard (such as a Chicony KB-2971) which is quick and easy to use.
Software
To store information on the computer a suitable software package will be necessary:
- ‘Bramhall High’ will need some sort of system for sorting information quickly and easily, such as Microsoft Excel. As it can:
- Store information clearly and save without loss of data.
- Help you design and run searches with reliable results.
- Microsoft Windows NT: It can run both of these programs and run a network system, which is needed, in ‘Bramhall High’.
Data Capture
I have designed my data capture form in an eye catching, formal presentation that will impress the teacher filling it out. It has a neat border and has the school logo at the top of the page for a professional look. The important parts of the form are clearly underlined and in bold for easy accessibility. The sections where you write the information have plenty of space for the longest name or surname. In other parts where information is required I have put tick boxes ( ) next to several options where the teacher will have to tick either one. Most of the information needed for the form is required in the form of text but for such fields as exam marks and coursework marks require numeric data.
Validation
While the data capture form is being filled out and the spreadsheet is being set up mistakes are sometimes inevitable. Therefore validation is necessary to prevent important information being mistyped. In my data capture form you are, at first, advised to fill out the form in pencil to prevent mistakes that cannot be erased.
In the actual creating of the spreadsheet errors can be avoided if a validation rule is entered in the appropriate box whilst making a table. You can use the validation rule to specify requirements for data entered into a cell. When data is entered that violates the validation rule setting, you can use the validation text property to specify the message to be displayed to the user. Data that has been entered into the data capture form, which is invalid, can be corrected by my spreadsheet through a simple validation rule. This can be seen here:
Possible problems could be resolved when if the operator enters a validation rule saying that certain letters or numbers that refer to a specific field can be entered only. E.g. the “Sex” field, a simple “M” or “F” could be typed instead of “Male” or “Female”. This is entered because it saves time and prevents spelling errors. A validation rule would avoid such mistakes. The validation rule can be applied to any field and check any part of the information.
The following fields could be validated:
- This could be validated to avoid mistakes in the students grade and save time when recovering the information. Instead of a long-winded “Grade B” a simple “B” could be entered instead.
Here is an example of the “Final Grade” Validation Rule:
- It could be validated by only allowing certain words or codes into the cell e.g. “M” for Male and “F” for Female. This would help to avoid spelling errors and would provide a quicker and more effective data input.
Here is an example of the “Sex” Validation Rule:
The following fields could not be validated:
- This could not be validated because there must be 25 students that have different initials e.g. “Sam Whitaker” could be validated to “SW” but that would clash and cause errors in searches with “Sian Wilson”.
- This could not be validated because people have different DOB. On the odd chance 2 people have the same DOB (twins) this would be indicated in the spreadsheet but would not matter as other fields would be different.
- This could not be validated because people have different addresses. On the odd chance 2 people have the same address (relatives) this would be indicated in the database but would not matter as other fields would be different.
- This could not be validated because people have different Telephone numbers. On the odd chance 2 people have the same emergency telephone number (relatives) this would be indicated in the database but would not matter as other fields would be different.
As a part of my validation I have entered several different validation rules to alert the teacher of what should be entered in the selected cell. This obviously reduces the event of an error. However, I have discovered and applied a technique that when you enter a certain value in the “ Final Total Marks” column the spreadsheet will automatically present the relative grade for that mark. This is called “VLookup”. It is basically a formula that searches for a match in a point of reference/lookup table, e.g. simple grade boundaries table, and calculates the grade that applies to the mark. An example of the formula is shown here: =VLOOKUP( L3,$Q$2:$R$10, 2, TRUE).
Firstly, the syntax of the formula is as follows:
- =function name(parameter, parameter list)
In this case my function name is VLOOKUP. The first parameter is the cell that contains the mark. The second parameter is the range of cells that contains my lookup table. The third parameter is the column in my lookup table that contains the grade. The last parameter determines that I do not have to find an exact match for my mark; the next lowest number will approximate to a match.
I use “$” signs in my formula to indicate that the cells they refer to (my Lookup table) are to be recognised as an absolute reference as opposed to a relative reference. If it were a relative reference on my spreadsheet then when I tried to copy the formula to other cells the reference to the lookup table would move as well, causing errors on the spreadsheet.
Data Structures
Output Format
I have designed my spreadsheet so that it has a friendly and easy to use interface. The cells are bordered and formatted in a sensible size and style of font. The important headings of the spreadsheet are put in bold font so that they stand out. The headings that contain marks, have the appropriate range of data in brackets next to them e.g. “Final Total Marks (out of 100)”. The spreadsheet is also designed so that the name of the students are on the left so that you can easy follow their information across the page on the computer or on a printout. I have designed my spreadsheet in landscape format so that the information can be easily read on a printout.
For the convenience of the operator my spreadsheet can also be exported onto other operating systems in different formats. My spreadsheet can be made into a Microsoft Access Report or put into a Query where the search function may operate. This can easily be done using these instructions:
- Close the Microsoft Excel workbook that contains the data you want to use in Microsoft Access. If the Microsoft Excel worksheet contains column labels in the first row, Microsoft Access uses the labels as field names in the new table it creates to contain the imported data.
- On the File menu in Microsoft Access, point to Get External Data, and then click Import.
- In the Import dialog box, click Microsoft Excel in the Files of type box.
- In the Look in list, locate the file you want to import, and then double-click the file.
- Follow the directions in the Import Spreadsheet Wizard.
A sample of this output format can be found in a printout on the next page.
By using Microsoft Access I can also create a report by exporting my spreadsheet into Microsoft Access. This can be done by using the methods above. This way a handy professional report can be printed so that the teacher can use it as a formal printout. It could also be used to display averages or totals.
A sample of this output format can be found in a printout on the next page.
By using Microsoft Access I can also create query which would allow the operator to perform a search as this function is not available on Microsoft Excel. This has many advantages as the teacher can now produce certain statistics, such as:
- How many children got grades A-C
- How many children got grades D-U
- What grades people in a certain Form Group got
- Which sex got better overall grades
This function can be done using the above method. Except the operator should make a query and then use the following method to perform a search:
