Microsoft Access XP Beginners: Exercises

Lessons 1 8 will be used while designing a database on paper Exercise 9. Creating a new database

Create a new database called my new database on the G: drive

Exercise 10. Closing a Database

Close the my new database file

Exercise 11. Opening a Database

1. Open the database file called Orders from the G: drive 2. Open the My new database file (Note that the Orders database will close!)

Exercise 12. Creating a table

Create a new table and add the following fields

Exercise 13. Changing the field size

1) Set the fields in the table you created so that they have the field sizes shown below Field Name Product Code Description Maker_ID Field Size 5 75 Long Integer

Exercise 14. Setting the Primary key in a table

1) Set the Maker_ID as the Primary Key. 2) Remove Maker_ID from being the Primary Key. 3) Set Maker_ID and Product Code as the Primary key 4) Remove Maker_ID and Product Code from being the Primary key 5) Set Product_ID as the Primary Key.

Exercise 15. Saving the table design

6) Close and save the table you have created as Product

Microsoft Access XP Beginners- Exercises

7) Create a table in design view with the following fields:

8) Set Supplier_ID as the Primary key 9) Close and save the table as Supplier 10) Create a table in design view with the following fields

11) Set Supplier_ID and Product_ID as the Primary Key 12) Close and save the table as Product_Supplier 13) Create a table in design view with the following fields

14) Set Maker_ID as the Primary Key 15) Set the field size of Maker Name as 40 16) Close and save the table as Maker

Exercise 16. Table view

1) Open the Product table in Design view 2) Change to the Datasheet view without closing the table 3) Close the table 4) Open the Maker table in Datasheet view 5) Change to the Design view without closing the table 6) Close the table

Exercise 17. Changing the table design

1) Add the following fields to the Supplier table

1. Change the order of the fields in the Supplier table to that shown below:

Page 2 of 13

Microsoft Access XP Beginners- Exercises

2. Delete the Web_site field. 3. Close the table and save the design changes 4. Open the Maker table in design view 5. Add the following fields

6. Change the field size of Contact name to 25 7. Change the order of the fields to:

Exercise 18. Lookup Wizard

1. Open the database Lesson 18 from the G: drive 2. Open the Product table in datasheet view note that the Maker_ID is not very informative! You would need to look up the Makers name in the Maker table to know which Maker produces each product! 3. Change to the design view. 4. Change the field type for Maker_ID to Lookup Wizard and tab to the next field 5. Follow the Lookup Wizard, looking up values from the Maker table and including Maker_ID and Maker name. 6. Change to the Datasheet view and test the Maker_ID field to see the change. 7. Close the table 8. Open the table Product_Supplier in datasheet view. Again, the table has codes only. Not very helpful if you dont remember what the codes represent! 9. Change the data type of the Supplier_ID to Lookup Wizard and tab to the next field 10. Use the Lookup Wizard to set the field to lookup values from the Supplier table (use the Supplier_ID and the Supplier Name) 11. Change to the Datasheet view and test the field. 12. Change back to the Design view 13. Change the data type of the Product_ID to Lookup Wizard and tab to the next field

Page 3 of 13

Microsoft Access XP Beginners- Exercises

14. Use the Lookup Wizard to set the field to lookup values from the Product table (use the Product_ID, Description and Product Code in that order!!) 15. Change to the Datasheet view and test the field. 16. Close the table 17. Close the database

Exercise 19. Relationships

1. Open the My new database file 2. Open the Relationships window and add all the tables 3. Set up a proper relationship between the Maker_ID and the Product_ID Can you? What is the reason? 4. Set up relationships as shown below:

Exercise 20. Changing the layout of the Relationships window

1. Open the Lesson 18 database 2. Open the Relationships window and note the relationships which were created when you used the Lookup Wizard 3. Remove the relationship between Supplier and Product_Supplier. 4. Edit the relationship between Product_Supplier and Product so that the Integrity is enforced. 5. Add a relationship between Supplier and Product_Supplier, ensuring that Integrity is enforced. 6. Add the Maker table (if it is not already shown!) 7. Edit the relationship between Product and Maker so that the Integrity is enforced. 8. Make the Supplier and Product tables tall and wide enough to show all the fields in the table

Exercise 21. Adding data to a table

1. Open the Lesson 21 database 2. Add the following to the Maker table and close it. (Note that the Maker_ID is created automatically)

Page 4 of 13

Microsoft Access XP Beginners- Exercises

3. Add the following data to the Product table, using CTRL ' whenever possible, and close the table. (Note that the Product_ID is created automatically)

Exercise 22. Editing and deleting data in a table

1. Open the Product Table 2. Edit the Description of product PC342 to read A4 Paper and undo the change. 3. Edit the Product Code of product PC342 to read P34 and the Description to read Stapler and undo the changes. 4. Edit the Product Code of product PC342 to read P34 and the Description to read Stapler and move to the next record 5. Undo the changes made to the previous record 6. Delete the product with the Description Rubbish

Exercise 23. Formatting data in a table

1. Open the Maker table 2. Make the data in Contact_Name show in magenta 3. Make the data in Maker_Name show in upper case 4. Close and save the Maker table 5. Open the Product table 6. Make the data in RRP show a sign instead of the 7. Close and save the Product table

Exercise 24. Controlling Input to a table using an Input Mask

1. Open the Product table in design view 2. Set the Product_Code field so that the user can only enter data in the form of two initial letters of the alphabet and then three numbers. The user must put all five characters in 3. Change to the datasheet view and enter a new record with the Product_Code FHT231. Can you? 4. Enter the Product_Code FD23 - can you? 5. Enter the Product_Code FD231 - can you? 6. Close and save the Product table

Page 5 of 13

Microsoft Access XP Beginners- Exercises Exercise 25. Controlling Input to a table using Validation
1. Using the database Lesson 21 database: 2. Open the Order table in design view 3. Set validation so that the Order_date must be between 1/1/2003 and todays date (inclusive) 4. Change to the datasheet view and try to enter tomorrows date 5. Try to enter 31/12/02 note the error message 6. Change back to the design view and add validation text explaining what dates may be added. 7. Change back to the datasheet view and enter tomorrows date note the error message! 8. Enter todays date 9. Add another record leaving the date blank - can you? 10. Change the validation for the Order_date so that it can be left blank. 11. Add another record leaving the date blank 12. Close and save the table

Exercise 26. Setting other Field Properties in a table

1. Open the Product table in design view and create an index for Description which does not allow duplicates 2. Change to the datasheet view and add two records as shown below

(Remember to use Ctrl ) Can you? 3. Change the index for Description to allow duplicates and add the second record again. 4. Close and save the table 5. Set the default value for Quantity in the Order_line table to 1 6. Add a new record 7. Close and save the table 8. Set the Supplier Name in Supplier as a required field 9. Try to add a record in Supplier without a Supplier_Name 10. Close and save the table

Exercise 27. Create a new database at this stage!

1. Open the Orders table from the G: drive 2. Look at the relationships Window and note the set up of the database 3. Create a new database called OrdersFrontEnd

Page 6 of 13

Microsoft Access XP Beginners- Exercises Exercise 28. Linking tables

1. Link all the tables from the Orders table 2. Use the Linked table Manager to view the links

Exercise 29. Creating a Form

1. Create a columnar form based on the Maker table including all fields 2. Close and save the form as Maker 3. Open the Maker form and add a record 4. Create a columnar form based on the Supplier table including all fields 5. Close and save the form as Supplier 6. Use the Supplier form to add a record 7. Create a columnar form based on Order 8. Close and save the form

Exercise 30. Form views

1. Open the Maker form in Design view 2. Change to Form view without closing the form 3. Change to the datasheet view 4. Close the form 5. Open the Supplier table in Form view 6. Change to Design view without closing the form 7. Close the form

Exercise 31. Changing header, footer and detail area size for Forms
1. Open the Supplier table and increase the detail area size 2. Open the Footer area 3. Open the Header area 4. Close the Header and Footer areas

Exercise 32. Bound/Unbound Controls and labels

1. Change the label for Maker_name in the Maker table to Maker Name 2. Delete the words Maker_Name in the control 3. Change to the form view and note that you cannot now see the Maker Name data (the control is unbound) 4. Change back to the design view and type Maker Name into the control 5. Change to the form view and note the error message! (there is no such field as Maker Name) 6. Type Maker_Name into the control and check that the data is shown correctly. Page 7 of 13

Microsoft Access XP Beginners- Exercises Exercise 33. Adding simple Controls in Forms
1. In the OrderFrontEnd database open the Order form in Design view. 2. Add another bound control for the Supplier field. 3. Add another bound control for the Date field. 4. Change the text in the label for the new Date control to Date order placed 5. Add an unbound field with the label Type of Order. 6. Change to the form view and test the controls you have added. Move from record to record and note that what you type in the bound control is specific to the record in which you type it, while data you type in the unbound control stays there regardless of the record you are viewing 7. Close and save the form

Exercise 34. Creating Single Option Box/ Check boxes in Forms

1. Open the Order table (note that the table is in the Order database!) 2. Add a new field called Order completed? with type Yes/No 3. Return to the OrderFrontEnd database, open the Order form and add a bound Yes/No control for Order completed? 4. Change to the form view and test the new control 5. Change the default value for Order Complete? To yes 6. Change to the form view and add a new record to test the default

Exercise 35. Changing controls in Forms

1. In the Order form remove the Type of Order control and the duplicate Date control. 2. Delete both Supplier controls 3. Close and save the form 4. Open the Maker form and change it so that it looks as below (Note labels)

5. Close and save the form 6. Open the Supplier form and make it look as shown right

Page 8 of 13

Microsoft Access XP Beginners- Exercises

7. Open the Order form and make it look as shown right: (Note the size of fields and that Order completed? Is in the Form header area)

Exercise 36. Creating List boxes and Combo boxes in Forms

1. Open the Order form. 2. Add a combo box bound to the Supplier_ID field. 3. Add a list box bound to the Supplier_ID field. 4. Change to the Form View and test and compare the combo box and list box 5. Change back to Design view 6. Delete the list box 7. Close and save the form

Exercise 37. Editing Tab Order in Forms

1. Open the Supplier form and ensure the tab order is correct 2. Open the Maker form and ensure the tab order is correct 3. Open the Order form and ensure the tab order is correct

Exercise 38. Sub forms

Creating a form/subform for viewing Suppliers and the Products they supply. 1. Create a tabular form based on the Product_Supplier table. 2. Remove the Supplier_ID control and label, since this the field that is common between the Supplier form and the Product_Supplier form. The form should look as shown below:

3. Close and save the form as Product_Supplier 4. Open the Supplier form in design view and drag the Product_Supplier form onto it. 5. Change to form view to test the form. (Note that Supplier_ID is not visible anywhere on either form, but exists and is used to connect the relevant information from the two tables.) 6. Close and save the Supplier form Creating a form/subform for viewing Orders and the order lines, that is the details of multiple products being ordered on each order. 7. Create a tabular form based on the Order line table.

Page 9 of 13

Microsoft Access XP Beginners- Exercises

8. Remove the Order No control and label since this is the common field between the Order and the Order line tables. The form should look as follows:

9.

Close and save the form as Order line

10. Open the Order form in design view and drag the Order line form onto it. 11. Change to form view to test the form. 12. Close and save the Order form 13. Creating a form/subform for viewing Makers and the Products they make. 14. Create a tabular form based on the Product table. 15. Remove the Maker_ID field (this is common between the Maker and Product tables) and the Product_ID field (used by Access, of no interest to us!). The form should look as shown right 16. Close and save the form as Product 17. Open the Maker form in design view and drag the Product form onto it. 18. Change to form view to test the form. 19. Close and save the Maker form

Exercise 39. Creating a simple select Query

1. Use the database OrderFrontEnd 2. We are aiming to create a query to show all products and their suppliers - create a new query and add the Product and Supplier tables. Is that all we need? Which other table is required and why???? 3. Add the fields Product Code, Description, Supplier Name and Town.

Exercise 40. Saving and changing views

1. Change to the datasheet view (without closing the query!) and see the data. 2. Close and save the query as Qry_Product_Suppliers 3. Create a query using the Maker, Product, Order and Order_line tables. Include the fields Maker_Name, Product_Code and Description. 4. Change to the datasheet view and see the data. 5. Change back to the design view and add the RRP 6. View the data. 7. Close and save the query as Qry_Products

Page 10 of 13

Microsoft Access XP Beginners- Exercises Exercise 41. Deleting tables from Queries (and why you might need to)
1. Open the Products table and note the Crystal Ballpen and Matt Photo Paper which exist in the table. 2. Open the Qry_Products Query and note that you cannot see any Crystal Ballpen and Matt Photo Paper. This Query is not giving a complete list of products! 3. Delete the Order and Order line tables from the Query (no fields from these have been used anyway!) 4. View the result of the query and note that Crystal Ballpen and Matt Photo Paper are now included in the list! Note that because we had the Order and Order_line tables included in the query we were asking for data which existed in all tables. Since no Crystal Ballpens or Matt Photo Paper have been ordered they do not exist in those tables and are therefore not included in the query. Now that you have removed the Order tables you will list all products which exist in the Product table. 5. Remove all criteria 6. Close and save the Query

Exercise 42. Adding Criteria to a Query

1. Use Qry_Product_Suppliers to find Products supplied by Mail Room Supplies (check the results there are 13) 2. Add criteria which find all paper supplied by Mail Room Supplies (check the results -3) 3. Find only products with paper at the beginning of the description (2) 4. Add criteria which find all cases supplied by Goose and Hen Stationery and all photo products supplied by All Types of Paper. Remember the dangers of AND and ALL (5) 5. Close and save the query 6. Create a query to find out which Makers the Supplier Krazee Papers deals with. (2!!!) 7. Close and save the query as Supplier_Maker.

Exercise 43. Editing a query

1. Open Qry_Product_Suppliers 2. Add the table Maker 3. Insert the field Maker Name to the left of Description. 4. Move Product Code after Description 5. Add the Supplier County 6. Delete Town 7. View the query 8. Remove all criteria 9. Save the query

Page 11 of 13

Microsoft Access XP Beginners- Exercises Exercise 44. Sorting Fields in a query and Showing fields
1. Sort the data in Qry_Product_Suppliers by Maker Name 2. View the datasheet 3. Sort the data by Supplier_Name 4. View the datasheet 5. Sort the data by Supplier_Name and then by Maker name, but keep the fields showing in their existing order 6. Add a criterion which makes the query only deal with suppliers in Notts 7. View the results (17) 8. Hide the County field 9. View the results 10. Remove all criteria and show the County field again 11. Sort by Supplier and then by Maker 12. Add the Supplier_ID field 13. Close and save the query

Exercise 45. Creating calculated fields in a query

1. Create a query which shows orders sent to Mudway Business Supplies 2. Include fields for Order_No, Supplier name, Date, Quantity and Price 3. Create a field called Cost which calculates the total price for each orderline (i.e. Quantity times Price) 4. Create a field called inc VAT which adds 17.5% VAT onto the Cost (i.e. Cost * 1.175) 5. Change the query to show all orders 6. Save the Query as Qry_Orders

Exercise 46. Formatting fields in a query

1. Format the inc VAT field to show the sign and two decimal places 2. Format the inc VAT field to show the currency as Kwacha 3. Format the date field to show the month in full 4. Save and close the Query

Exercise 47. Creating a report

1. Create a Report based on Qry_Product_Suppliers 2. Change to design view 3. Change back to preview the report

Page 12 of 13

Microsoft Access XP Beginners- Exercises Exercise 48. Saving, Changing Views and Printing Reports
1. Close and save the report as Rpt_Product_Suppliers 2. Create a report based on the Products query. 3. Close it and save it as Rpt_Products

Exercise 49. Changing a Report

1. Open the Rpt_Product_Suppliers 2. Remove the Supplier_ID field from the report 3. Make the Page Header area deeper 4. Change the Product_Code label in the Page Header area so that Product appears on the first line and Code beneath it and there is no between.. (repeat for Maker_Name and Supplier_Name) i.e. 5. Move the controls in the detail area to match the labels 6. Preview the report 7. Move the page number from the Page footer to the Page header, above the labels 8. Preview the Report

Exercise 50. Sorting and Grouping in Reports

1. Open the Rpt_Product_Suppliers Report 2. Move all controls out of the group header into the detail area 3. Change the Report so that it is grouped by Maker Name 4. Move the fields into the Maker Name header as appropriate 5. View the Report

Exercise 51. Creating Calculated fields and joining text fields

1. Create a Report based on Qry_Orders 2. Make sure it is grouped by Order_No 3. Make sure there is an Order_No footer 4. Add a control in the Order_No footer with the label Order Total which sums the Cost for each order 5. Add a control which counts the number of items in each order . Give it the label No of Items 6. Alter the Supplier_name control so that it shows the Supplier Name followed by a comma followed by the County

Exercise 52. Creating mailing labels

1. Create mailing labels for the suppliers using the Supplier table Page 13 of 13