Intermediate GNVQ Sample Database Assignment - Bolton Tools Database

---

Description of user’s needs

The manager wants a system which will do the following tasks:

• The system must keep track of every tool for hire. He must be able to add new tools, delete ones that are no longer used (e.g. broken) and change the details of any particular tool.
• The manager must be able to record when a particular tool is hired out to a customer and record when it is returned to stock.
• When a customer comes into the store and asks for a particular tool the manager must be able to say whether he has a suitable tool in stock and how much it would cost to rent for a specified period, e.g. one day or weekend.
• The system must be able to print out a price list with and without VAT. The price list gives the different hire rates for each time period e.g. day, weekend, week.
• The system must be able to print out a list of every tool sorted by category. Within each category the tools will be sorted in alphabetical order. The 1-day hire price with and without VAT must be shown for each tool.
• The system must be able to print a summary report showing the quantity of each tool in stock for stocktaking and to see what tools are in stock.

Information to be processed

The information to be processed is:

• The list of tools showing tool category, description, hire rates.
• The prices charged for each different hire rate, shown in the Price List.
• Whether or not a tool is out on hire

Processing required

The following processing is required:

• There must be a query to look up the availability of tools of a particular type and the hire price.
• The information about the tools must be sorted by category and by tool description and printed in a report.
• VAT must be calculated on each price and a price list produced showing prices with and without VAT.
• There must be a query to count the quantity of each tool in stock. This will be used as the basis for the stock report.

---

Design of the database

The database will have two tables named tblPriceCode and tblEquipment.

tblPrice

tblEquipment

The two tables are linked in a one-to-many relationship.

---

Creation of the table structures

The two tables tblPrice and tblEquipment were created in Access. Validations were added to each of the fields as specified in the Design.

tblPrice

tblEquipment

An input mask was added to EquipmentCode so that the user has to enter 3 letters followed by 2 numbers.

---

Creating relationships

I created the one-to-many relationship between tblPrice and tblEquipment.

By ticking Enforce Referential Integrity I ensured that the user cannot enter a Price code for a tool unless that price code exists on the Price table. This satisfies the validation rule specified in the Design for this field.

The Relationships are shown in the Relationships window:

---

Entering data

Data was entered into the Price table by importing it into the database from a spreadsheet which was made available to me.

This is the spreadsheet

and this is the data imported into tblPrice

Because the data was imported I knew it was accurate.

---

I could not import the Equipment data in the same way as it was not in the right format, so I typed it in. I tested the validations (see next page).

I made some errors in data entry which I corrected.

Here is the data I typed in:

I set some of the fields for InStock to “No” (by clicking in the field to set it to a blank) so that I could test some of the queries.

I designed a form which the manager can use to enter new pieces of equipment.

---

Validation tests

I carried out some tests to make sure that my validation checks worked.

Test 1: Enter a record for a new Price code, with £3.99 for the day hire rate

Expected result: The record will not be accepted because the rate is outside the valid range

Actual result: An error message was displayed and I had to enter a number within the range £4 to £100, as specified in the validation.

Test 2: Enter a record with Price Code 26

Expected result: The record will not be accepted because the Price code is not on the Price table.

Actual result: An error message was displayed and the record was not accepted. This is because I specified ‘Enforce Referential Integrity’ when creating the relationship between the two tables.

Queries

I designed a query called qryEquipment which does 3 things:

1. It combines the Price fields from the Price table with the fields from the Equipment table
2. It sorts the tools by category and within category, by Description
3. It lets the user enter criteria. In the screenshot below the query will find all tools with a description starting with “pai” or “wal”, which are currently in stock.

I carried out tests to make sure the query gave the expected result with different criteria.

Test 3: Enter criteria to find all paint strippers or wallpaper strippers in stock. The query uses complex criteria:

(Description = “wal*” OR “pai*”) AND InStock = “Yes”

Expected result: The query should find 3 records for DEC01, DEC02 and DEC03.

Actual result: The correct records are found, and are sorted in alphabetical order.

---

The second query I created was designed to combine fields from the Equipment table and Price table, and also to calculate the VAT and the Price with VAT on each tool. The results will be printed by category and Description.

Test 4: Test the query (qryEquipmentList)

These are the results when the query was run:

Expected result: I calculated the VAT manually on the first record.

VAT at 17.5% on £5.00 = £0.875.

Total Price= £5 + £0.875 = £5.88 rounded to the nearest penny.

Actual result: The query gave the correct result.

The third query qryPriceList used only one table. It works out the VAT and total price for every hire period for all the records in the Price List. It will be used as the source of the Price List report.

Test 5: Run the query to make sure all the VAT calculations are performed correctly

The query results are shown below:

Expected result: I calculated the VAT and total price manually for each hire rate for the first record.

Vat at 17.5% on £4 = 0.7                  Total price for 1 day = £4.70

Vat at 17.5% on £5.50 = 0.9625          Total price for weekend = £6.46

Vat at 17.5% on £7.00 = 1.225          Total price for 2 days = £8.23

Vat at 17.5% on £8.00 = 1.225          Total price for 3 days = £9.40

Vat at 17.5% on £7.00 = 1.575          Total price for week days = £10.58

I made a mistake in the VAT calculation for the 1-week rate but after seeing that the answer did not match with my manual calculation I corrected this error.

Actual result: After correction the query gave the right results.

---

The fourth query qryStockTake combines fields from the two tables tblEquipment and tblPrice. It uses the COUNT function to count up all the tools with the same description in a new field named Quantity.

This is a screenshot of the query:

Test 6: Run the query to ensure that total number of each tool is correctly calculated.

The query results are shown below:

Expected result: I checked three different items. From the original data entered there should be 4 belt sanders and 1 post hole borer (3 entered but 2 on hire).  There should be 2 artex brushes as there are 4 altogether and 2 of them are out on hire (see page 7.)

Actual result: All the query results were correct.

---

Reports

I created three reports. The first one is called rptEquipment, and the source of the report is qryEquipmentList.  I used the wizard to create it by double-clicking Create Report by using Wizard. In the Report Wizard window I selected qryEquipmentList as the source of the report and selected fields for the report.

The wizard produced this report:

---

I formatted the report so that it had a better heading and the columns were spaced out better, with all the Description field showing. I did this by changing to Design view and then making changes.

The final report looked like this:

---

The second report was the Price List. I used the report wizard to create this report. The source of the report was qryPriceList.

After formatting the report it looked like this:

(Note: You should include a copy of the printed reports.)

The third report was the Stock List. I used the report wizard to create this report. The source of the report was qryStockTake which uses fields from two tables and uses the COUNT function. The report looks like this:

 

---

Testing

I carried out several tests to make sure that all my results were accurate. The tests and results are described in the sections on Entering Data, Validation and Queries.

Evaluation

The database that I created for Bolton Tools fulfills all the requirements specified in the description of the user’s needs. An on-screen form was created to enable the user to add new tools, delete ones that are no longer used and change details of existing tools. There is a checkbox on the form to show whether the tool is in stock, and when a tool is hired out, the manager can click this box to uncheck it. He clicks it again when the tool is returned to show that it is in stock again.

The validations and input mask help to ensure that the data is entered without error. However I have only allowed for 99 tools in each category and if I did the database again I would make the Equipment code 3 letters followed by 3 numbers, not 2.

It could be difficult for him to find the correct record when the tool is returned. He can however do this by using the ‘Find’ button on the toolbar and typing in part of the tool description in the Description field.

The system does not record who the tool was hired out to, or for how long. This could be added at a later date to improve the system, but it was not one of the requirements.

The manager can make a query to see whether a particular tool is in stock but he would have to be reasonably expert at Access, as the query is made by loading up qryEquipment in Design View, entering the correct criteria and then running the query. In a more customised system it would be possible for this process to be more automated.

The three reports, (Equipment List, Price List and Stock List) were all created and improvements made so that they are clear and easy to read.

---

Backups

I kept backups of all my work as I developed the database.

I used a floppy disk to keep a copy of the database, and I made a new backup copy after each session working on it.

I could not back up the documentation onto a floppy disk as it was over 2Mb. I kept a copy in another folder on the hard disk.

---

Planning sheet