Report design of “invoice”…
Method of data entry, including validation
I intend to enter data through the forms created from the tables and that validation will take place on each appropriate field.
Data will be entered through means of input devices such as a mouse and keyboard; a keyboard is a computer input device derived from the typewriter, where keys pressed communicate discrete commands to the computer. Although most often used to input the letters of the alphabet, most keyboards contain special character keys programmed by the user or the software to perform specialized tasks. Keyboards are quick, simple and a cheap method of entering data into the system and so is very efficient. Again the mouse is also advantageous and enforces quick data entry and is normally less error prone.
Record structure, file organisation and processing
The database is split up into multiple sections, starting off with the three major tables and then forms, and finally branching off at the report (invoice) and the mail merged letter.
Evidence of the tables and validation used
Card table…
Validation used for “cardtbl”…
Other fields, which have been validated in the card table, are company name and comment. For company name, the maximum field size enabled here is 25 and 50 for the comment field. Number of new tyres fitted has also been validated which restricts users to enter a value greater than 5, if a greater value is entered, then an error message is produced.
Company details table…
Validation used for “companydetailstbl”…
Other fields, which have been validated in the company details table, are company name, contact name, street address, town/city, telephone number and discount offered.
The maximum field size for company name is 25; 25 for contact name, 50 for street address, 50 for town/city, 11 for telephone number and for discount offered, validation involved here only allows the values 0 or 0.05 (validation rule).
Price list table…
Validation used for pricelisttbl…
Other fields, which have been validated in the price list table, are type of tyres.
The maximum field size for type of tyres is 9.
Calculations/formulas used in access
For
For example, to calculate total price, firstly go to properties on the total price text box in the design of the report. Then click control source and enter the formula “=[Price before discount]+[Customer's price (incl VAT)]”.
This means that whatever value is in the price before discount text box is added to the value in customer’s price (incl VAT).
Below is a screenshot indicating the points described…
The price before discount and price of VAT are both done in the exact method as total price.
To calculate the price before discount, the formula “=[number of new tyres fitted] * [customer’s price ex VAT]” is entered in the expression builder of the design view of the report. The value produced is the result of multiplying the value in number of tyres fitted by the value in customer’s price ex VAT.
To calculate the price of VAT, the formula “=[discount offered] * [customer’s price ex VAT]” is entered in the expression builder of the design view of the report.
Query used to determine those whose cars have not been fitted with new tyres for over 12 months
I have named this particular query overdue and it basically calculates which customer has not had any tyres fitted for over a year since their last fitting.
The formula used here is entered under the criteria for the field date.
“<Date()-365”
Design of mail-merged document – a letter is sent to people who have not had their tyres checked on their vehicle for a period of over twelve months…
Create and print form letters
-
On the Tools menu, point to Letters and Mailings, and then click Mail Merge Wizard.
-
Under Select document type, click Letters.
The active document becomes the main document.
- Click Next: Starting document.
- Click Use the current document.
You can then either type the letter in the document window, or wait until the wizard prompts you to do so in a later step.
- Under Select recipients, click Use an existing list.
- Click Browse.
- In the Select Data Source dialog box, locate and click the data source you want.
By default, Microsoft Word opens the My Data Sources folder.
- Click Open.
- If you have not already done so, in the main document, type the text that you want to appear in every form letter.
- Insert merge fields where you want to merge names, addresses, and other data from the data source.
System design
Entity relationship diagram…
Security and integrity of data
Data integrity refers to the correctness of the data; the data held in a computer system may become incorrect, corrupt or of poor quality.
For example:
- Errors on input
- Errors in operating procedure
- Program errors
- Viruses
- Transmission errors
To protect against input and operating procedure errors…
- Data entry must be limited to authorised personnel only.
- All output should be inspected for reasonableness and any inconsistencies investigated.
Security of data
Computer-based information systems are vulnerable to crime and abuse, natural disaster and human error. To overcome this problem, I have password protected my database. The password is “tod2004”.
Implementation/Testing
Test plan
Testing
Evidence
Test 1 - To check the system only allows Car Registration numbers of the post-2001 format.
Test 2 - To check invoice is produced with all specified information.
Test 3 - To check standard reminder letter is correctly produced.
Test 5 – check security (Type correct password).
Test 6 – check security (Type incorrect password).
Test 7 – check security (Leave password blank).
Test 8 – test formula (Check discount is subtracted correctly).
Test 9 – test formula (Check VAT is added correctly).
Test 10 – test formula (Check query “overdue” calculates customers who have not had new tyres replaced over 12 months).
Test 11 – test validation (Check number of tyres fitted does not exceed 5).
Test 12 – test validation (Check if input mask used for post code is correct).
Test 13 – test validation (Check discount only allows either 0% or 5%).
Test 14 – test validation (Check field size for company name is 25).
Corrective action: