European Computer Driving Licence

Back

 

Module 5 Sample Test 1 

(syllabus version 4) 

 

(click here for Test 2)

You are managing a database of customer accounts for a company that has several branches. In the test you will work with the Accounts database and will add and edit data in the database. You will then use queries and reports to extract information from the database. Remember that all your work should be saved on the candidate disk.

You will need to download some files to your candidate disk before you start the test. 

  1. Right click here and save the file M5answerfile1.doc to your candidate disk.
  2. Access 2000 users - Right click here and save the file accounts.mdb to your candidate disk
  3. Access 97 users - Right click here and save the file accounts97.mdb to your candidate disk.

    

Tasks 

1. Open the M5answerfile on your Candidate Disk and answer Questions 1,2,3 and 4. Save and close the Answer file.

 (4 Marks)

2. Open the Accounts database from your Candidate Disk. Create a new table with 3 fields and their properties as shown below.

Field Name

 Data Type

 Field Size or Format

Branch ID

 Numeric

 Integer

Branch Title

 Text

 20

Manager

 Text

 30
(2 Marks)

3. Using your knowledge of databases, select the most appropriate field for the primary key and create a primary key.

 (1 Mark)

4. Save the table as Branches.

 (1 Mark)

5. Add the following records to the Branches table. 

Branch ID

 Branch Title

 Manager

301

 Stockport

 B Melia

401

 Cheltenham

 A Azari

Close the table.

 (1 Mark)

6. Open the Accounts table and add a new field with the following details so that it becomes the last field in the table.

Field Name

  Data Type  

Live Account?

 Yes/No
(1 Mark)

7. In the Accounts table modify the field size of the Account Type field to 20 characters. Save and close the table.

 (1 Mark)

8. Open the Customers table. Find the record for J MacAllister and change the spelling of the surname to McAllister

 (1 Mark)

9. Delete the record for A Anderson.

 (1 Mark)

10. Again in the Customers table, create an index on Last Name. There may be duplicate names in the table.

 (1 Mark)

11. Again in the Customers table, delete the DateofBirth field. Save and close the table.

 (1 Mark)

12. Customers may have more than one account at the same branch or in different branches. Use the Customer Number field in the Customers and the Accounts tables to build a one-to-many relationship between the Customers and Accounts tables. 

 (1 Mark)

13. Apply referential integrity to the relationship.

 (1 Mark)

14. Open the Customers table and sort the table by the Last Name field in ascending order.

 (1 Mark)

15. Create a simple form using all the fields from the Accounts table. Save the form as Account Input Form.

 (1 Mark)

16. Use the form you have just created to print one copy of the first record in the table.

 (1 Mark)

17. Use the Customers and Accounts tables to create a query that will show the Customer Number, Initials and Last Name of all Customers who have an ISA account. Save the query as ISA

 (2 Marks)

18. Using both Customers and Accounts tables, create a new query using all the fields from the Customers table which will show those customers who have an Account Balance greater than €2000.00. Save the query as Balance

 (2 Marks)

19. Open the Branch List query and edit it so that only customers with an account at the Branch with ID 301 will be displayed.

 (1 Mark)

20. Print one copy of the result of the Branch List query.

 (1 Mark)

21. Delete the Account List query.

 (1 Mark)

22. Create a report using all the fields from the Customers table. Save the report as Customer Report

 (1 Mark)

23. Create a report using all the fields from the Accounts table grouped by the Branch ID field. Save the report as Branch Accounts

 (1 Mark)

24. Change the Branch Accounts report orientation to landscape.

 (1 Mark)

25. Open the Account Customers report and modify the header Accounts to read Account Balances.

 (1 Mark)

25. Copy the header Account Balances so that it will appear at the bottom of every page of the Accounts report. Do not obscure any other objects in the report. Close the database application. 

 (1 Mark)

Total Marks

 (32 Marks)

 

Module 5 Sample Test 2 (syllabus version 4) 

You are managing a database of customer accounts for a car dealership. In the test you will work with the Autos database and will add and edit data in the database and create forms. You will then use queries and reports to extract information from the database. Remember that all your work should be saved on the candidate disk.

You will need to download a number of files to your candidate disk before you start the test. 

  1. Right click here and save the file M5answerfile2.doc to your candidate disk.
  2. Access 2000 users - Right click here and save the file autos.mdb to your candidate disk.
  3. Access 97 users - Right click here and save the file autos97.mdb to your candidate disk.

 

Tasks 

1. Open the M5answerfile on your Candidate Disk and answer Questions 1,2,3 and 4. Save and close the Answer file.

 (4 Marks)

2. Open the Autos database from your Candidate Disk. Open the Sales Reps table and add the following fields after the Sales Rep Name field:

Field Name

 Data Type

 Field Size or Format

Telephone Number

 Text

 10

Date Started

 Date
(2 Marks)

3. Using your knowledge of databases, select the most appropriate field for the primary key and create a primary key for the table. Save the Sales Rep table with your changes.

 (1 Mark)

4. Add the following data to the correct records in the Sales Rep table. 

Sales Rep ID

 Telephone Number

 Date Started

3

 04638590

 12/01/1999

5

 03264784

 23/11/1996

Close the table.

 (1 Mark)

5. Open the Car Sales table. Find the record for the BMW Z3 and change the purchase price to £14,999

 (1 Mark)

6. The customer has decided not to buy the car. Delete the record for Nissan Micra.

 (1 Mark)

7. In the Car Sales table modify the Purchase Price field so it has no decimal places. Save and close the table.

 (1 Mark)

8. Again in the Car Sales table, create an index on Make and Model. Allow for duplicate names in the index.

 (1 Mark)

9. Again in the Car Sales table, delete the Part Exchange field. Save and close the table.

 (1 Mark)

10. Open the Car Sales table and sort the table by the Purchase Price field in descending order.

 (1 Mark)

11. Delete the Accounts table.

 (1 Mark)

12. Customers may buy more than one car. Use the Customer Number field in the Customers and the Car Sales tables to build a one-to-many relationship between the Customers and Car Sales tables. 

 (1 Mark)
13. Apply referential integrity to the relationship. (1 Mark)

14. Create a simple form using all the fields from the Car Sales table. Save the form as Car Sales Input Form

 (1 Mark)

15. Use the form you have just created to add the following record:

Sale Reference

Date of sale

Customer Number

Sales Rep ID

 Type

Make and Model

Reg Number

 Year

Purchase Price

03/130

3/9/2003

40

5

Jaguar XK

12999
(1 Mark)

16. Use the Customers and Car Sales tables to create a query that will show the Customer Number, Initials and Last Name of all Customers who have bought a Hatch car. Save the query as Hatch

 (2 Marks)

17. Using both tables again, create another query using all the fields from the Customers table to display those customers who have bought a car with a Purchase Price greater than £10,000.00. Save the query as More than 10,000

 (2 Marks)

18. Open the Sales Rep Grade query and edit it so that only sales reps on Grade 2 will be displayed. Save the query.

 (1 Mark)

19. Print one copy of the result of the Sales Rep Grade query.

 (1 Mark)

20. Delete the Query 1 query and the Reps List report.

 (1 Mark)

21. Create a report using all the fields from the Customers table. Save the report as Customer Report.

 (1 Mark)

22. Create a report using all the fields from the Car Sales table grouped by the Sales Rep ID field and showing the total sales for each Rep. Save the report as Grouped Car Sales.

 (2 Marks)

23. Open the Grouped Car Sales report and modify the header to read Car Sales by Rep.

 (1 Mark)

24. Change the margins of the Grouped Car Sales report to 2 cms all round.

 (1 Mark)

25. Move the date field from the footer of the Grouped Car Sales report so it prints at the top of the report only. Do not obscure any other objects in the report. Close the database application. 

 (1 Mark)

Total Marks

 (32 Marks)

Back