exercise 76 - replacing text - extended web viewbeep make computer beep. cancelevent ... this...

Open Learning Guide

Microsoft® Access 2010

Advanced

Note: Microsoft, Access and Windows are registered trademarks of the Microsoft Corporation

Release OL344v1

Access Advanced Open Learning

Published by:

CiA Training LtdBusiness & Innovation CentreSunderland Enterprise ParkSunderlandSR5 2TAUnited Kingdom

Tel: +44 (0) 191 549 5002Fax: +44 (0) 191 549 9005

E-mail: [email protected]: www.ciatraining.co.uk

ISBN: 978-1-86005-885-1

Important NoteThis guide was written for Microsoft Office 2010 running on Windows 7. If using earlier versions of Windows some dialog boxes may look and function slightly differently to that described.

A screen resolution of 1024x768 is assumed. Working at a different resolution (or with an application window which is not maximised) may change the look of the dynamic Office 2010 Ribbon, which changes to fit the space available.

For example, the Editing Group on a full Ribbon will contain several buttons, but if space is restricted it may be replaced by an Editing Button (which, when clicked, will display the full Editing Group).

First published 2011

Copyright © 2011 CiA Training Ltd

All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording or otherwise) without the prior written permission of CiA Training Limited.

Microsoft is a registered trademark and Windows is a trademark of the Microsoft Corporation. Screen images reproduced by permission of the Microsoft Corporation. All other trademarks in this book are acknowledged as the property of their respective owners.

Access 2010 2 © CiA Training Ltd 2011

Open Learning Access Advanced

CiA Training's Open Learning guides are a collection of structured exercises building into a complete open learning package, to teach how to use a particular software application. They are designed to take the user through the features to enhance, fulfil and instil confidence in the product.

ACCESS ADVANCED - The third guide in the Microsoft Access series contains exercises covering the following topics:

Macros AutoExec Macros

Conditional Macros Exporting

Formatting Forms Inserting Pictures and Charts

Adding Startup Controls Relationships and Joins

Attachments and Links Database Utilities

Database Passwords Database Analysis

Database Tools and

Operations

Control Panels

This Open Learning Guide is suitable for:

Any individual wishing to further their knowledge of Microsoft Access following the Introductory and Intermediate guides or equivalent. The user works through the guide from start to finish.

Tutor led groups as reinforcement material.

Aims and Objectives

To further the user's knowledge and techniques for the successful creation and use of complicated database models using Access 2010.

After completing the guide the user will be able to:

Create advanced macros

Add form backgrounds, pictures and charts

Apply conditional formatting

Create data access (web) pages using wizards

Send database objects as e-mail attachments

Repair, compact, backup and restore a database

Create a switchboard and control panel

Set database passwords

Analyse database performance

© CiA Training Ltd 2011 3 Access 2010


Downloading the Data FilesThe data files associated with this guide must be downloaded from our website. To do this, go to www.ciatraining.co.uk/data and follow the simple on-screen instructions.

Your FastCode for this guide’s data is: OL344

By default, the data files will be downloaded to the DATA FILES \ Open Learning \ Access 2010 Advanced folder in the Documents library/folder (or My Documents in Windows XP).

If you prefer, the data can be supplied on CD at an additional cost. Contact the Sales team at [email protected].

IntroductionThis guide assumes that the program has been correctly and fully installed on your personal computer, that the computer is already switched on, and that a printer and mouse are attached. The guide was created using Access 2010.

Important Notes For All Users

The accompanying downloaded data contains files, enabling the user to practise new techniques without the need for data entry.

Notation Used Throughout This Guide

Key presses are included within <> e.g. <Enter>

The guide is split into individual exercises. Each exercise consists of a written explanation of the feature, followed by a stepped exercise. Read the Guidelines and then follow the Actions with reference to the Guidelines, if necessary.

Recommendations

It is suggested that the user add their name, the date and exercise number after completing each exercise that requires a printed copy.

Read the whole of each exercise before starting to work through it. This ensures understanding of the topic and prevents any unnecessary mistakes.

Measurements used throughout this guide are metric.

Some fonts used in this guide may not be available on all computers. If this is the case, select an alternative.



ContentsSECTION 1 MACROS................................................................................................................................. 7

1 - MACRO ACTIONS..................................................................................................................................... 82 - CREATE A NEW MACRO......................................................................................................................... 103 - ATTACHING A MACRO TO A CONTROL...................................................................................................124 - WHERE CONDITION MACRO................................................................................................................... 145 - CREATING NEW MACROS FROM CONTROLS............................................................................................156 - MULTIPLE ACTION MACROS.................................................................................................................. 177 - SET VALUES.......................................................................................................................................... 188 - UPDATE FIELDS AUTOMATICALLY.......................................................................................................... 209 - MESSAGE BOXES................................................................................................................................... 2210 - AUTOEXEC MACRO............................................................................................................................. 2311 - REVISION: MACROS............................................................................................................................. 24

SECTION 2 FORMATTING FORMS.......................................................................................................25

12 - ADDING BACKGROUNDS...................................................................................................................... 2613 - INSERTING A PICTURE.......................................................................................................................... 2814 - INSERTING A CHART............................................................................................................................ 3015 - PAGE HEADERS AND FOOTERS............................................................................................................. 3216 - CONDITIONAL FORMATTING................................................................................................................. 3317 - INSERT DATE AND TIME....................................................................................................................... 3418 - REVISION: FORMATTING FORMS........................................................................................................... 35

SECTION 3 ATTACHMENTS AND LINKS............................................................................................36

19 - ATTACHMENTS.................................................................................................................................... 3720 - ATTACHMENTS IN FORMS..................................................................................................................... 3921 - MULTIPLE ATTACHMENTS.................................................................................................................... 4022 - HYPERLINK FIELDS.............................................................................................................................. 4123 - HYPERLINKS ON FORMS....................................................................................................................... 4324 - REVISION: ATTACHMENTS.................................................................................................................... 45

SECTION 4 EXPORTING DATA............................................................................................................. 46

25 - SEND TO............................................................................................................................................. 4726 - SEND USING A MACRO......................................................................................................................... 4927 - EXPORTING OBJECTS........................................................................................................................... 5128 - REVISION: EXPORTING DATA............................................................................................................... 53

SECTION 5 DATABASE TOOLS............................................................................................................. 54

29 - COMPACT AND REPAIR A DATABASE....................................................................................................5530 - LINKED TABLES................................................................................................................................... 5731 - BACKUP/RESTORE A DATABASE........................................................................................................... 5932 - DATABASE SPLITTER............................................................................................................................ 6033 - SET DATABASE PASSWORDS................................................................................................................. 6234 - REMOVE A PASSWORD......................................................................................................................... 6335 - REVISION: DATABASE TOOLS............................................................................................................... 64



SECTION 6 DATABASE ANALYSIS.......................................................................................................65

36 - TABLE ANALYZER............................................................................................................................... 6637 - PERFORMANCE ANALYZER................................................................................................................... 6938 - DOCUMENTER...................................................................................................................................... 7039 - DEPENDENCIES.................................................................................................................................... 7140 - REVISION: DATABASE ANALYSIS.......................................................................................................... 72

SECTION 7 DATABASE OPTIONS AND UTILITIES............................................................................73

41 - OPTIONS.............................................................................................................................................. 7442 - CURRENT DATABASE OPTIONS............................................................................................................. 7643 - KEY PRESS CONTROLS......................................................................................................................... 7844 - PAGE NUMBERS FOR REPORTS............................................................................................................. 7945 – NAVIGATION FORM............................................................................................................................. 8046 - REVISION: DATABASE OPTIONS............................................................................................................ 82

SECTION 8 CONTROL PANEL............................................................................................................... 84

47 - CONTROL PANEL................................................................................................................................. 8548 - MAIN CONTROL PANEL........................................................................................................................ 8749 - SUB CONTROL PANELS........................................................................................................................ 8850 - APPLY MACROS TO A CONTROL PANEL................................................................................................8951 - APPLY MACROS TO SUB CONTROL PANELS..........................................................................................9052 - CREATING AN OPTION GROUP.............................................................................................................. 9153 - REVISION: CONTROL PANEL................................................................................................................. 94

SECTION 9 RELATIONSHIPS AND JOINS...........................................................................................95

54 - ONE-TO-MANY................................................................................................................................... 9655 - MANY-TO-MANY................................................................................................................................ 9856 - ONE-TO-ONE..................................................................................................................................... 10057 - JOINS................................................................................................................................................. 10158 - REVISION: RELATIONSHIPS................................................................................................................. 104

ANSWERS................................................................................................................................................. 105

GLOSSARY............................................................................................................................................... 107

INDEX........................................................................................................................................................ 108

OTHER PRODUCTS FROM CIA TRAINING........................................................................................110



Section 1

Macros

By the end of this Section you should be able to:

Create and Attach MacrosUse Where Conditions

Create Multiple Action MacrosSet Values with Macros

Update Fields AutomaticallyUse Message Boxes

Create AutoExec Macros



Exercise 1 - Macro ActionsGuidelines:

A macro is an object in Access which is intended to automate database processes. It can be made up of a single action or multiple actions, which are activated every time the macro is run. Some actions have Action Arguments, which set the controls for the action, e.g. the Action Arguments for the OpenReport action will contain details of which report is to be opened, what view will be used, or if a filter/query is to be used to restrict the records for the report. Once created a macro can be run manually or more commonly, attached to a command button. They can also be activated automatically by an event such as a form opening or a field being updated.

Outlined below is a list of some the actions which do not require granting trusted status to the database. These are the actions that will be listed by default when creating a macro. Other actions can be used but these will have security implications when writing databases for other users.

Action Explanation

AddMenu Add a menu to a menu bar.

ApplyFilter Applies a Filter/Query to specified data.

Beep Make computer beep.

CancelEvent Cancels event that macro is attached to.

CloseWindow Close an object.

EMailDatabaseObject Create a message with the object as an attachment

FindNextRecord Find next record that meets specified criteria.

FindRecord Find a specific record.

GoToControl Move the cursor to a control.

GoToPage Move to a specific page.

GoToRecord Move to a specific record.

Hourglass Show the hourglass.

LockNavigationPane Prevents objects being deleted from the Navigation Pane

MaximizeWindow Maximise the current window.

MessageBox Show a message box.

MinimizeWindow Minimise the current window.

MoveAndSizeWindow Moves/resizes active window.

Navigate To Go to a specific Navigation Pane group or category.

On Error Defines error handling process.

continued over



Exercise 1 - ContinuedAction Explanation

OpenForm Open a form.

OpenQuery Open a query.

OpenReport Open a report.

OpenTable Open a table.

QuitAccess Quit Access.

RemoveAllTempVars Remove all temporary variables.

RemoveTempVar Remove a single temporary variables.

RepaintObject Update screen activity.

Requery Requery an object.

RestoreWindow Makes the active window the original size.

RunCode Run a Visual Basic Module.

RunMacro Run a macro.

RunMenuCommand Carry out a menu command.

SearchForRecord Searches an object for a specific record.

SelectObject Select an object.

SetDisplayedCategories Specifies categories to be shown in Navigation Pane.

SetMenuItem Set status of customised menus.

SetProperty Set a control property.

SetTempVar Set a temporary variable value.

ShowAllRecords Display all records.

Single Step Pause macro, start single step mode.

StopAllMacros Stop all currently running macros.

StopMacro Stop the currently running macro.



Exercise 2 - Create a New MacroGuidelines:

Macros are easily created and this usually involves selecting the required Action or Actions and specifying Action Arguments to control the operation of each action. For example, an Open Form macro can be created to open a specified form whenever it is run. Action Arguments will have to be set up to state which form is to be opened and whether the form is to be viewed in Form, Design or Datasheet View.

Other Action Arguments can be used to set controls for the form, such as whether data can be added, edited or whether the form is to be read - only.

Different Actions will have a different set of Action Arguments available.

Actions:

1. Open the Transport database. Some macro actions require that the content is enabled. If the Security Warning banner is displayed now, click Enable Content. The message will disappear.

2. Display the Create tab and click the Macro button to create a new macro. The Macro window appears.

3. Click the drop down arrow in the empty Action box to display a list of available actions.

4. Scroll down the list of options and select OpenForm. An expanded Action box is displayed for the OpenForm action, containing all the arguments that apply for this particular action (different actions will have a different selection of action arguments).

Note: Actions can also be added by locating and double clicking the required action in the Action Catalog panel on the right of the window. If this panel is not shown, click the Action Catalog from the Show/Hide group on the Macro Tools Design tab.

continued over



Exercise 2 - Continued

5. Click the drop down arrow in Form Name and choose the name of the form that is to be opened – Servicing. Leave the View argument as Form. This defines the view that will be used when the form is opened.

6. Click the drop down arrow in Data Mode and choose Read Only from the list. When the Servicing form is opened using this macro, it will be for enquiry only, it will not be possible to amend any data.

7. Click Collapse All on the Macro Tools Design tab.

Note: Further actions could be added to the macro at this point by using the Add New Action box.

8. Right click on the Macro1 tab and select Close. Click Yes when prompted to save the macro. Enter the name Read Service Records.

9. Click OK to save the macro, then leave the database open.

Note: Alternatively use Save Object As from the File tab to save the macro first, then it can be closed without the save prompt.



Exercise 3 - Attaching a Macro to a ControlGuidelines:

Once a macro has been created, it can be attached to various controls on a form, such as a data field, image, command button or an option group. It can also be attached to the whole form. When it has been attached, an Event needs to be set up in the properties of the control. This will control when the macro will run, for example, on a single or double click of the mouse, or when <Enter> is pressed, or when the form closes.

Actions:

1. In the Transport database, open the Buses form in Design View. A button will be placed on this form to open the Servicing form.

2. Display the Design tab and ensure the Use Control Wizards option (in

the Controls group) is turned off, .

3. Click on Button, in the Controls group of the Design tab and click once on the left side of the Form Footer area to create a button.

4. With the Command Button selected, display the Event tab of the Property Sheet.

5. Click in On Click, i.e. the event will be triggered when this button is clicked.

6. To define what the event will be, click the drop down arrow - a list of all the available macros in this database is shown.

7. Select the Read Service Records macro.

8. Select the Format tab and in Caption, enter Servicing as the name on the button.

9. Save the form then switch to Form View and click the Servicing button. The macro runs and the Servicing form opens. No data can be amended on the form because the macro has opened it in Read Only mode.

continued over



Exercise 3 - ContinuedNote: Notice that the Buses form is still open, and the Servicing form is not

necessarily showing the same vehicle that is selected in the Buses form. This will be dealt with later.

10. Close the Servicing form.

11. Macros can be attached to any existing control on the form. View the Buses form in Design View, and click on the image in the centre of the page. The Property Sheet should still be displayed.

12. Select the Event tab and click On Dbl Click from the list, i.e. a double click on this object will be needed to start the event.

13. Click the drop down arrow and choose the Read Service Records macro. Save the form.

14. Switch to Form View and double click the central image. The macro runs and the Servicing form opens.

15. Close the Servicing form.

16. Macros can be attached to the form as a whole. View the Buses form in Design View and click on the Form Selection button at the top left.

17. The Property Sheet shows properties for the whole form. Notice there are many more possible events now shown on the Event tab.

18. Click On Close, i.e. the event will be triggered when the form is closed.

19. Click the drop down arrow and choose the Read Service Records macro. Save the form.

20. Switch to Form View then close the form. The macro runs and the Servicing form opens.

21. Close the Servicing form then open the Buses form in Design View.

22. Make sure the Property Sheet for the whole form is displayed and delete the Read Service Records macro from the On Close event (so that the Servicing form does not open every time the Buses form is closed). Save the form.

23. Close all open forms but leave the Transport database open.



Exercise 4 - Where Condition MacroGuidelines:

In the previous exercise, the Servicing form was always opened at the first record, with all records available. Often when opening one form from another, it is desirable to specify which record (or records) will be seen in the second form. A common scenario is that the second form will show records related to the record currently displayed in the first form. This can be done using the Where Condition argument when defining the macro actions.

Actions:

1. From the Transport database, open the Read Service Records macro in Design View. This is the macro attached to the Open Servicing Form button on the Buses form. At present all records are available when the Servicing form is opened.

2. Make sure the OpenForm action is selected and expanded, and enter the following expression in the Where Condition box:

[Registration]=[Forms]![Buses]![Registration]

Note: This Where Condition causes the macro to open the Servicing form only for those Servicing Records where the Registration field is the same as that for the current record on the Buses form.

3. Save the macro, close it, and open the Buses form in Form View.

4. With the first bus record displayed (B478 MTK), click the Servicing button.

5. The Servicing form opens, displaying the Servicing record for bus B478 MTK. Notice that according to the navigation buttons only one record is available.

6. Close the Servicing form and use the navigation buttons to find the record on the Buses form for registration number M09 TWS (record 18).

7. Click the Servicing button.

8. A servicing record for the appropriate vehicle is displayed but as there are 2 servicing records for this registration the navigation buttons indicate that this is record 1 of 2. Display the second record.

9. Close both the Servicing and the Buses forms.


Registration field in the Servicing Form

Current Registration field on the Buses


Exercise 5 - Creating New Macros from ControlsGuidelines:

Instead of creating the macro then attaching it to the control, it is possible to create the macro as the control itself is being added to the form (or report). As before, the control can be a button, a field or the form itself. The created macro is embedded with the form.

The Find macro, which is created in this exercise, uses the Find and Replace function to search for records using the field specified in the Action Argument for the command.

Actions:

1. From the Transport database, open the Buses form in Design View.

2. Ensure the Use Control Wizards option is turned off. Create a Control Button (Command Button) at the right of the Form Footer area and make sure the Property Sheet is displayed.

3. Select the Event tab and select On Click, i.e. the event will be triggered when the button is clicked.

4. Instead of selecting an existing macro, click on the Build button, . The Choose Builder dialog box appears.

5. Select Macro Builder and click OK. A new macro is opened.

6. Add an Action of GoToControl and enter Registration as the Control Name. This is the field that will be searched during Find and Replace.

7. Macros can contain more than one action. On the next Add New Action line, select RunMenuCommand, and click in Command to see a list of available commands. Select Find.

8. Close the Macro window. A prompt will appear.

continued over



Exercise 5 - Continued9. Select Yes and the macro will be stored as an embedded object within

the form definition. This is shown in the Property Sheet.

10. On the Format tab, change the Caption to Find. This will appear on the command button.

11. Close the Property Sheet, save the form and switch to Form View.

12. Click on the Find button. The Access command Find is run and the Find and Replace dialog box appears.

13. Enter L126 in Find What, and set the Match option to Any Part of Field.

14. Click on Find Next. The record for L126 BWE appears.

15. Close the Find and Replace dialog box.

16. Close the Buses form but leave the database open.



Exercise 6 - Multiple Action MacrosGuidelines:

The macro created in the previous exercise contained two actions. It is an example of a multiple action macro. Macros can be built containing many actions in order to perform complex operations.

It is also possible to insert additional actions into an existing macro. For example, in the recently created macro to open a form, a prior action can be added to close down the current form.

Actions:

1. With the Transport database open, right click on the Read Service Records macro and open it in Design View. Another Action is to be added to close the Buses form before opening Servicing.

2. Click in the first Add New Action box and select the action CloseWindow from the drop down list.

3. The Object Type is Form and from Object Name choose Buses (the drop down lists for each box may be used to avoid typing). Leave the Save option as Prompt so that changes cannot be lost when closing the form.

4. The order of the actions in a macro can be amended at anytime. With the

CloseWindow action still selected, click the Move up button, . The action is moved above the OpenForm action.

5. Click the Move down button, to return the action to its original position.

6. Close the macro and click Yes when prompted to save.

7. Open the Buses form in Form View.

8. Click on the Servicing button. This time when the Servicing form is opened, the Buses form closes.

Note: In a practical database project there would be a button on the Servicing form to close it and re-open the Buses form.

9. Close the Servicing form and close the database.



Exercise 7 - Set ValuesGuidelines:

A useful macro action is the SetValue action which makes it possible to create a macro that will allow the value of a field to be set. The value set by the macro can be a specific value, e.g. 99, or a calculation, e.g. Price + 10%. By making the calculation Original Value + 1 (or Original Value - 1), a macro can be created to increase (or decrease) a quantity, every time a button is clicked.

SetValue is not one of the default actions.

Actions:

1. Open the Household Plants database. If the Security Warning is displayed, select Enable content.

2. Open the Customer Orders form in Design View. It is decided that no less than 10 plants can be supplied on an order.

3. With the Use Control Wizards option switched off, draw a Command button to the right of the Quantity field.

4. View the Property Sheet for the new button. Select the Event tab, then On Click and click the Build button.

5. Select the Macro Builder to create a new macro. Click Show All Actions from the Design tab so that the SetValue action will be available.

6. Select SetValue in the first Action box.

7. Set the Item as [Quantity] (remember square brackets around a field name) and the Expression as 10.

8. Close the macro and click Yes when prompted to save. In the Property Sheet for the button, enter a Caption of Minimum Quantity.

9. Close the Property Sheet, switch to Form View and view any record.

10. Click the Minimum Quantity button. 10 appears in the Quantity field.

11. Save and close the form, then open the Plant Details form in Design View.

12. Insert a Command button above the flower graphic. View the Property Sheet for the new button and enter a Caption of Increase Quantity.



continued over



Exercise 7 - Continued13. Select the Event tab, then On Click and click the Build button.

14. Select the Macro Builder. Make sure Show All Actions is selected then select SetValue in the first Action box.

15. Set the Item as [Stock] and the Expression as [Stock]+1 (this increases the amount in the field by one).

16. Close the macro and click Yes when prompted to save. Switch to Form View and view the record for Bellflower.

17. Click on the Increase Quantity button. The number in the Stock field will increase by one.

18. Return to Design View. Create another Command button below the flower graphic and add a caption of Decrease Quantity.

19. Select the Event tab, then On Click. Click the Build button and select Macro Builder.

20. This macro will reduce the value by one. To prevent the number from decreasing to less than zero and becoming negative, a condition must be set. Click Action Catalog on the Design tab to display the Action Catalog pane.

21. Double click If in the Program Flow section of the Action Catalog pane.

22. In the If box enter [Stock]>0. The macro will now only run if the conditions are true, i.e. when the current value of Stock is greater than zero.

23. Select SetValue as the New Action, the Item is [Stock] and the Expression is [Stock] -1.


25. Return to Form View and search for the Friendship Plant record (31), with a stock level of 4. Click the Decrease Quantity button until the stock value reaches 0. Click the button again. Due to the If condition, it will not function after the stock value reaches 0.

26. Save and close the form and close the database.



Exercise 8 - Update Fields AutomaticallyGuidelines:

SetValue can also be used to enter dates into field. A macro can be created to enter the current date automatically. This exercise will create a macro to test the value of a Yes/No field. Selecting Yes will enter today's date into a Date field; selecting No will leave it blank. The IIF function performs this test.

This is also an example of how a macro can be activated by an event other than clicking a button. This macro will run whenever a certain field is updated.

Actions:

1. Open the Transport database and make sure the content is enabled.

2. Open the Servicing form in Design View. A macro is to be created that will automatically enter the current date into the Date field once the Yes option has been checked in the Logged field.

3. Select the Logged option group (to do this, select the frame around the entire group).

4. View the Property Sheet. If the whole group is selected, Option Group will be displayed as the Selection type.

5. Select the Event tab and click on the After Update property. It is after this field has been updated that the macro is to be run.

6. Click the Build button, select Macro Builder and click OK.

7. Select the SetValue Action and enter the Item as [Date] as this is the field that is to have its value set.

continued over



Exercise 8 - Continued8. In the Expression property enter the following, taking care with the

brackets:

IIF([Logged],Date(),Null)

9. Make sure you understand this expression, which can be analysed as follows. If the Logged field is true, i.e. set to Yes to indicate completed, then the current date (the Date() function) will be entered into the Date field, otherwise the Date field will be left blank (null).


11. Save the form and switch to Form View.

12. Start a new record, then click on Yes in the Logged option group.

13. The option group has been updated so the macro will apply the current date to the Date field. Click on No from Logged to set the Date field to blank.

Note: Content will have to be enabled for the SetValue action to work.

14. Close the form without saving and close the database.



Exercise 9 - Message BoxesGuidelines:

It can often be useful to display messages when using a database. These can be general information messages displayed when a certain object is opened for example, or specific messages displayed when a specific event occurs such as an unusual entry in a critical field. Messages can be displayed using macros.

Actions:

1. Open the Household Plants database and then the Unpaid Invoices report in Design View. View the Property Sheet and click on the Report Selection button to see the properties for the whole report.

2. On the Event tab, click the On Open field and then the Build button. Select the Macro Builder, click OK.

3. Select MessageBox as the first Action.

4. Enter the following text as the Message: Make sure that today’s payments have been entered before printing this report.

5. Select Yes as the Beep argument, Warning! as the Type, and Important as the Title.

6. Close the macro and click Yes when prompted to save. The Embedded Macro now appears in the Property Sheet.

7. Close the Unpaid Invoices report, saving when prompted.

8. Double click on the Unpaid Invoices report to open it again. As it opens, the message will be displayed.

9. Click OK to answer the message. The report will be displayed.

10. Close the report and the Household Plants database.



Exercise 10 - AutoExec MacroGuidelines:

A macro that is saved with the name AutoExec will automatically run its actions as soon as the database is opened. This is often used to open a specific form when a database is opened.

Actions:

1. Open the Transport database, select the Create tab and click Macro.

2. In the macro window set out the following actions, which will open a new record in the Buses form as soon as the database is opened:

Action Argument Comment

RunMenuCommand WindowHide Hide the Navigation Pane so no objects can be amended

OpenForm Servicing Open Servicing form

RunMenuCommand RecordsGoToNew Displays a new blank record

3. Save the macro and call it Autoexec.

4. Close the macro. Close the database.

5. Open the database again, the Autoexec macro will automatically run and display the Servicing form ready to accept a new record.

6. The Navigation Pane is not displayed so only objects which are available using controls on the form can be accessed. To display the Navigation Pane again press <F11>.

Note: An AutoExec macro will run every time the database is opened. To stop it from running, hold down <Shift> when opening the database.

7. To remove the macro, right click on the Autoexec macro in the Navigation Pane, and select Delete. Click Yes in the confirmation message.

8. Close the Transport database.



Exercise 11 - Revision: Macros1. Open the Household Plants database and the Customer Orders form

in Design View.

2. Create a Command Button on the left of the form footer area (click and drag to expand the form footer area if necessary).

3. Create a macro for the On Click property of the button.

4. Add a single action macro which will open the Plant Details form. What is the action used?

5. Set a Where condition so that the Plant Details form is opened for the plant shown on the currently open Customer Order record. That is when the Common Name field from the Plant Details form equals the Plant name on the Customer Orders form. What is the Where expression?

6. Save the macro to embed it in the form. Add a caption of Check Stock Level to the button.

7. View record 7 on the Customer Orders form, an order for 10 Leopard Lilies. Click the Check Stock Level button to see if there are enough in stock to meet the order.

8. Add a macro to the Paid option group so that when the group is updated, the value of the Paid field is examined. If it is true (= Yes) then the current date is to be added to the Date Paid field.

9. Select Paid = Yes for the first two records to set the Date Paid.

10. Save and close all forms and close the database.

11. Open the Wine database and enable the content.

12. Open the Products Form in Design View and create a command button in the left of the Form Footer area with a caption of Discount.

13. Use Build to create a macro for the On Click property of the button.

14. Add an action to the macro to reduce the Price field by 20%. What action is used and what expression will perform the necessary calculation. Save the macro as Discount.

15. Switch to Form View for the Products form and view record 14. Click the Discount button to reduce the price. What is the new price?

16. Print a copy of the form for this record then close the form without saving and close the database.

Note: The answers to this exercise can be found in the Answers Section at the end of the guide.



Section 2

Formatting Forms


Insert Pictures as BackgroundsInsert a Chart

Use Page Headers and FootersUnderstand Conditional Formatting

Use Date and Time Fields



Exercise 12 - Adding BackgroundsGuidelines:

Backgrounds images can be added to forms or reports. The background needs to be in a picture format, e.g .jpg, .bmp, .ico, .dib, .wmf or .emf file, which can be embedded or linked. An embedded background is stored as part of each database object in which it appears. A linked background is stored once, so utilising storage space more efficiently. Another advantage of linking is that if the original linked background image is altered, e.g. a corporate logo change, this would automatically be seen in all database objects using this background.

Actions:

1. Open the Wine database and the Products Form in Design View.

2. Double click the Form Selector to open the Property Sheet for the form.

3. From the Format tab in the Property Sheet, scroll down and select the Picture property. Click the Build button.

4. From the Insert Picture dialog box, locate the Grapepicking file (this is a .jpg file supplied with the data for this guide). Select the file and click OK.

5. From the Property Sheet, choose a Picture Type of Embedded and a Picture Size Mode of Stretch. Switch to Form View.

6. The image fills the available space. Reduce the size of the Access window. The image changes to match the size of the window.

continued over


Form Selector Button


Exercise 12 - Continued7. Notice that by default the data labels are transparent (the background can

be seen through them) but the data fields are not.

Note: Sometimes a background image can mask the displayed data and it may be advisable to use only light images for this purpose.

8. Maximise the window and switch back to Design View. Change the Form properties to Picture Size Mode = Clip, Picture Alignment = Top Left and Picture Tiling = Yes.

9. Switch to Form View to see the changed effect, then close the form without saving.

10. Picture backgrounds can also be added to reports. Open the Products report in Design View and click on the Report Selector. A warning tag may be shown.

11. If the tag is shown, click it and select Remove Extra Report Space.

12. Display the Property Sheet and select a Picture Size Mode of Stretch and a Picture Type of Linked.

13. Click the Build button in the Picture property, locate the Champagne file from the supplied data folder and click Open.

Note: A linked image does not become part of the object, but the original source image file must be always be available.

14. Switch to Print Preview to see the effect. Notice the background image is seen on every report page.

15. Save the report and close it.

16. The background image is linked, so a change to the original picture will be seen in the form. Start the Windows Paint application. This can be found by clicking the Start button, selecting All Programs and opening the Accessories folder.

Note: Any image processing application can be used.

17. Open the Champagne image and change the fill colour to pale yellow. Save the image, close it and close Paint.

18. In the Access window open the Products report. The background image will show the changed image.

19. Close the report without saving but leave the database open.



Exercise 13 - Inserting a PictureGuidelines:

Objects such as pictures can also be added to the foreground of forms or reports either embedded or as linked objects. Objects can be pictures, images, media clips, charts or files from other applications, e.g. Excel. If an object is embedded, it will not change after insertion. If it is linked, it will change automatically as the source object changes, such as in a chart (covered later in this section).

Actions:

1. From the Wine database, open the Orders Form in Design View. If the Security Warning is displayed, select Enable content.

2. Display the Property Sheet for the Add Record button.

3. From the Format tab, click in the Picture property and select the Build button. The Picture Builder dialog box appears.

4. There are a number of icons available with Access that can be added to a button. Select some of the available pictures and observe the effect in the Sample area.

5. Alternatively, pictures can be selected from any source. Click on Browse to display the Select Picture dialog box. Locate the folder containing the supplied data files and select the Carafe picture.

6. Click Open.

7. Click OK on the Picture Builder dialog box. The image is added to the button. Extend the height of the button to see more of the image.

8. Save the form and switch to Form View. The picture does not affect the operation of the button. Click on the button to go to a new record form. Content will have to be enabled for this action to work.

continued over



Exercise 13 - Continued9. Close the Orders Form and open the Products Form in Design View.

10. Pictures can be inserted directly on to a form and not just on to a button. Increase the height of the Detail area, click Insert Image on the Design tab then select Browse.

11. The Insert Picture dialog box is displayed, but by default only shows Web-Ready Image Files. Use the drop down arrow to change this to All Files. Make sure the contents of the supplied data folder are displayed and select the Bottle picture file. Click OK.

12. Draw 4cm square with the picture frame cursor, . The image will be inserted automatically.

Note: To insert a clip art image, locate the folder containing the clips, e.g. Program Files | Microsoft Office | ClipArt | PUB60COR, and select an image from the list.

13. Make sure the Property Sheet for the image is displayed and set the Size Mode to Stretch.

Note: If a picture is to be linked and not embedded, the Picture Type property would be changed to Linked.

14. Click and drag a corner of the image frame to make it larger.

15. Save the form then switch to Form View.

16. Switch to Design View, click on the picture to select it and press <Delete>. The picture is removed.

17. Close the form without saving and close the database.



Exercise 14 - Inserting a ChartGuidelines:

Data can be displayed in a chart within a form or report. The chart wizard is usually used to create a chart.

Actions:

1. Open the Egypt database and open the Summary form in Design View. Make sure the Design tab is displayed.

2. In the Controls group, make sure the Use Control Wizards button is on,

then click the Chart button from the same group, .

3. Click in the form Detail area below the Adults field. The Chart Wizard appears. In the first screen, select the Bookings table and click Next.

4. Move the Adults and Airport fields from the Available Fields box into the Fields for Chart box. Click Next.

5. Click on the 3-D Column Chart to select it and click Next.

Note: The sample chart may not be displayed.

6. This screen shows how the data will be used in the report. Click Next.

7. This screen allows the chart to be linked to the individual record displayed on the form, but for this exercise delete both field names so that the chart contains data from all records. Click Next.

8. Enter a title of All Data, click the option No, don’t display a legend, then click Finish. A chart is displayed with sample (not actual) data.

continued over



Exercise 14 - Continued9. Drag the borders of the chart to make it 15cm wide by 6cm high. Increase

the size of the Detail area if necessary.

10. Switch to Form View. The sample data will be replaced with the actual data. Switch back to Design View.

11. Double click on the chart to open it in edit mode. This mode is indicated by a diagonally shaded border around the chart.

Note: If the Datasheet box appears when you double click, close it.

12. Right click on a blank (white) area of the chart and select the option Format Chart Area from the shortcut menu.

Note: If Format Chart Area is not an option, you have probably clicked in the wrong area. Try again.

13. The Format Chart Area dialog box is displayed. Change the Font to Arial, Italic and size 10pt. Click OK. All text on the chart is changed.

14. Right click on the chart title and select Format Chart Title. Change the font to Arial, Bold, 14pt and click OK.

15. Right click on the (grey) background to the chart and select Format Walls. Apply the palest green colour to the background area.

16. There is another way to format chart features. Double click on one of the data columns to display the Format Data Series dialog box.

17. Change the colour to red and click OK. All columns for this data series are changed.

18. Click anywhere away from the chart to deselect it then switch to Form View. If the chart is not shown, click in the white area where it should be.

19. Use the Navigation buttons to scroll through the records on the form. The chart will not change.

20. Close the form, saving the changes, then close the database.



Exercise 15 - Page Headers and FootersGuidelines:

In forms, Page Headers and Footers are similar to Form Headers and Footers - they can display titles, dates, etc. on every page, but they only appear when the form is printed. They cannot be seen in Form View.

Actions:

1. From the Wine database, open the Orders Form in Design View. The form has a Form Header (empty) and a Form Footer.

2. Expand the Form Header area, place a Label in the centre and enter the text Form header goes here. Increase the font size to 12pt.

3. Right click on the Form Header bar and move the cursor over Fill/Back Color from the shortcut menu. Select a pale blue colour.

4. Right click on the Form Header bar again and select Page Header/Footer, from the shortcut menu. The form now has two new areas, a Page Header area above the Detail and a Page Footer area below the Detail.

5. Place a Label in the Page Header, and enter the text Page header goes here. Increase the font size to 12pt.

6. Right click on the Page Header bar and move the cursor over Fill/Back Color from the shortcut menu. Select a yellow colour.

7. Save the form and switch to Form View. Only the blue Form Header is shown. Move through the records, the header is on every form.

8. Click the File tab, select Print and click Print Preview from the options. On the first page the blue Form Header and yellow Page Header are shown. On all other pages, only the yellow Page Header is shown.

9. Close Print Preview and close the form, but leave the database open.



Exercise 16 - Conditional FormattingGuidelines:

If a control (text box, command button, check box) which is placed on a form or report contains a value that needs to be monitored, Conditional Formatting can be applied. This will make the value easier to spot if a certain condition applies, e.g. a sales target dropping below a certain figure. Controls can be given different coloured backgrounds, or text can be set up to disable the control when it meets or doesn’t meet certain criteria.

Actions:

1. From the Wine database, open Products Form in Design View. Display the Form Design Tools - Format tab.

2. Click on the Stock field then select the Conditional Formatting button. Click New Rule to display the New Formatting Rule dialog box.

3. Change the rule description to read Field Value Is less than or equal to 200.

4. To define the formatting when the condition is met, click the Font Color button and select red, then click the Background color button and select yellow. Finally click the Bold button. The formatting effect is previewed.

5. Click OK then OK again. Save the form and switch to Form View.

6. Scroll through the records and notice that any stock quantity less than or equal to 200, will be immediately conspicuous.

7. Move to the record 4 for Soave. Increase the quantity to 300. Click in a different field. The conditional formatting no longer applies.

8. Leave the form open for the next exercise.



Exercise 17 - Insert Date and TimeGuidelines:

The current date and time can be placed on a form or report as a text box. By default, Access will place the text box into the header section but it can then be moved or entered manually. The format of the date and time can be varied.

Actions:

1. The Products Form should be open. Switch to Design View.

2. Click the Date and Time button, , in the Header/Footer group of the Design tab. The Date and Time dialog box appears.

3. Click on the first date format option and uncheck the Include Time box to deselect the time option.

4. Click OK. The date field appears in the Form Header.

5. Use the Text Box button to add a text box to the right of the image in the Detail area (widen the area if necessary). Enter a field value of =Date() and a label of Current Date. Display the Property Sheet and set the Format to Long Date.

6. Add a field below this with a value of =Time() a label of Current Time. On the Property Sheet set the Format to Long Time. Save the form and switch to Form View. The time and date will appear for every record.

7. Use the navigation buttons to move through the records. Note that each time the form is redisplayed, the time is updated.

8. Close the form without saving but leave the database open.



Exercise 18 - Revision: Formatting Forms1. Use the Wine database.

2. Add an Embedded background to the Orders Form, using the Champagne picture supplied on the data disk.

3. Apply a Picture Size Mode of Stretch.

4. Add a Page Footer area to the form and insert a label with the text Orders.

5. Save the form, then print preview it.

6. Apply Conditional Formatting to the Quantity field, so that if 24 or more items are ordered, the field background becomes blue.

7. Insert the Date and Time and move them to the bottom of the Page Footer on the form.

8. View the form in Form View, checking the Quantity field conditional formatting works.

9. Are the Date and Time fields visible in Form View?

10. Print Preview the form. Are the Date and Time fields visible now?

11. Explain the difference in views.

12. Save and close the form, then close the database.

Note: The answers to this exercise can be found in the Answers Section at the end of the guide.



Section 3

Attachments and Links


Use Attachment Fields on RecordsView Attachments on FormsUse Multiple Attachments

Use Hyperlink Fields on RecordsUse Hyperlink Fields on Forms



Exercise 19 - AttachmentsGuidelines:

Access 2010 includes a new type of data type called Attachment that can be used in tables. This data type allows different files such as documents or image files to be attached to each record in a table. Defining a file as an attachment includes a copy of that file within the database. That file can be amended from the database without altering the original. Similarly the original file can be changed or deleted without altering the copy in the database.

Actions:

1. Open the Pet Shop database, and open the Pets table in Design View.

2. Add a new field at the end of the list with a Field Name of Photo.

3. Select a Data Type of Attachment and in the Field Properties enter a Caption of Photograph.

4. Switch to Datasheet View. You will be prompted to save the table. Click Yes. The new field has been added to table.

5. Double click in the Photograph field for the first record (African Grey Parrot). The Attachments dialog box is displayed.

6. Click the Add button to display the Choose File dialog box. Locate the supplied data folder, Access 2010 Advanced, select the file AGP1.jpg and click Open.

continued over



Exercise 19 - Continued7. Click OK. The field now shows that there is one attachment for this

record.

Note: Unlike other fields, field names are not used as column headings for Attachment fields. Captions must be defined if column headings are required.

8. Repeat the process to add the file BG1.jpg as an attachment to the second record.

9. Attachments can be viewed at any time. Double click in the Photograph field for the first record to display the Attachments dialog box again.

10. Double click on the AGP1 file in the Attachments dialog box. The file is opened in an appropriate application, e.g. Windows Photo Viewer.

11. The image can be edited here. Right click on the image and select Rotate clockwise. The image is inverted. Close the image application.

12. Click OK in the Attachments dialog box. A message is displayed.

13. Click Yes to save the changes to the file in the database.

Note: The original file AGP1 has not been affected by this, only the copy in the database.

14. Close the Pets table but leave the database open for the next exercise.



Exercise 20 - Attachments in FormsGuidelines:

Probably the most useful place to see attachments, particularly image files is on forms. An image attachment on a form will display a version of the image before it is selected.

Actions:

1. In the Pet Shop database open the Add/Edit Pets form in Design View and make sure the Design tab is displayed.

2. Click Add Existing Fields from the Tools group to display the Field List panel.

3. Click on the Photo field to select the entire four line entry and drag it to the Detail area. Close the Field List panel.

4. Move the Photo field to the right of the form, resize it to 4cms square and delete the label.

5. Switch to Form View. The AGP1 image is shown on the form for the first record. Move to the second record. The image for that record is shown.

6. Double click on the image to display the Attachments dialog box. The image can be opened and amended as before.

7. Click Cancel to close the Attachments dialog box.

8. Save the changes to the form and close it, then close the database.



Exercise 21 - Multiple AttachmentsGuidelines:

More than one file can be attached to a record using the same Attachment field.

Actions:

1. Open the Wine database and open the Products table in Design View.

2. Add a new field with a Field Name of Reviews with a Data Type of Attachment. Enter Reviews as the Caption property.

3. Save the table and switch to Datasheet View.

4. Double click in the Reviews field for the first record to display the Attachments dialog box.

5. Click the Add button to display the Choose File dialog box.

6. Locate the supplied data folder and select the files Review176 and Review293. Click Open.

7. Click OK then close the Products table.

8. Open the Products Form in Design View and add the Reviews field to the right side of the form. Switch to Form View.

9. Double click on the Reviews icon, then double click on Review176. The document is opened in Word.

10. Close Word then double click Review293 to open this document in Word.

11. Close Word and click OK to close the Attachments dialog box.

Note: Different types of file can be attached in one field.

12. Close the form, saving the changes, and leave the database open.



Exercise 22 - Hyperlink FieldsGuidelines:

As well as storing various types of data in tables, it is also possible to store Hyperlinks to objects such as documents, spreadsheets, images which are held elsewhere. Unlike Attachments, there is no copy of the file stored within the database, which keeps the size of the database to a minimum. Hyperlinked data cannot be amended from within the database and must be maintained separately. However, this also means that the most current version of the original data is always available.

Links can be activated whenever the link field is shown on the screen, in Table Datasheet view for example or when the record is displayed on a form.

Note: Later exercises in this guide will cover the different topic of linked tables, where the whole table is held elsewhere but can be accessed from within the database.

Actions:

1. In the Wine database, open the Suppliers table in Design View.

2. Add a new field with a Field Name of Links with a Data Type of Hyperlink. Enter Links as the Caption property.

3. Save the table and switch to Datasheet View.

4. Right click in the Links column for the first record, select Hyperlink then Edit Hyperlink from the menus to display the Insert Hyperlink dialog box.

5. Make sure that Existing File or Web Page and Current Folder are both selected and Look in shows the location of the supplied data folder.

continued over



Exercise 22 - Continued6. Scroll down and select Grapepicking from the list of files. Enter

Vineyard in the Text to display box and click OK.

7. Right click in the Links field for the second record, select Hyperlink then Edit Hyperlink from the menu.

8. Type www.ciatraining.co.uk in the Address box (Access adds http://) then type Sponsors in the Text to display box and click OK.

9. Right click in the Links field for the third record, select Hyperlink then Edit Hyperlink from the menu.

10. Select E-mail Address in the Link to panel then type [email protected] in the E-mail address box (Access adds mailto:). Type Contact in the Text to display box and Query in the Subject box. Click OK.

11. Close the table and open the Suppliers Form in Design View.

12. Add the field Links to the form, below the existing fields.

13. Switch to Form View. Click in the Links field for record 1.

14. Select Yes if a security message is displayed. The grape picking photo is opened in a browser window.

15. Close the browser window, move to record 2 and click the Links field.

16. If you have an active internet link, the CiA Training web site will be displayed in the browser.

17. Close the browser window, move to record 3 and click the Links field.

18. Your default e-mail application will open with a blank message already addressed to bigplanetsupport, ready to be filled in and sent.

Note: These links are used to demonstrate the possibilities only. It would be more normal in a table, for all the links in one column to go to photos only (or web sites only, or e-mail addresses only).

19. Close the e-mail application without saving.

20. Save the form, close it and close the database.



Exercise 23 - Hyperlinks on FormsGuidelines:

Hyperlinks can also be added directly to form design. They can link to files, web sites or e-mail addresses as before but they will not vary as different records are displayed.

Actions:

1. Open the Egypt database and open the Booking Form in Design View.

2. Select the picture of the Sphinx and display the Property Sheet.

Note: Hyperlinks can be added to any object on the form.

3. Select the Format tab in the Property Sheet and click the Build button for Hyperlink Address property.

4. The Insert Hyperlink dialog box is displayed. Select Existing File or Web Page and Current Folder and select Horus.docx from the list.

5. Click the ScreenTip button and enter Read more about the Company in the dialog box.

continued over



Exercise 23 - Continued6. This is the text that will appear when the pointer is held over the

hyperlink. Click OK to accept it, and OK again to add the link.

7. To create a link to an e-mail address, add a Label to the Form Footer with the text Contact us for further details.

8. Click away from the label then select it again. Insert a hyperlink using the Property Sheet. Select E-mail Address in the Link to panel then type [email protected] in the E-mail address box. Click OK.

9. A new hyperlink field can be added to a form. Click the Insert Hyperlink button from the Controls group on the Design tab. The Insert Hyperlink dialog box is displayed.

10. Select Existing File or Web Page in the Link to panel then type www.ciatraining.co.uk in the Address box. Type Visit our web site in the Text to Display box and click OK.

11. A new Label field is added to the currently selected area of the form. Drag it down below the existing link in the Footer area.

12. Switch to Form View.

13. Move the cursor over the Sphinx image. The cursor changes to a pointing hand, indicating that there is a hyperlink here.

14. Leave the cursor for a second and the ScreenTip will appear.

15. Click the mouse button and the document Horus.docx will open in Word.

Note: Depending on the security settings, there may be a prompt screen displayed before the document is opened. Select Yes.

16. Close Word and look at the Form Footer area.

17. Click on the Contact us link to display a blank e-mail addressed to ciasupport. Close the message application without saving.

18. Click on the Visit link. If you have an active internet link, the CiA Training web site will be displayed in the browser.

19. Close the browser window.

20. Move to a different record on the form and try all the links again. They will all be exactly the same.

21. Click the File tab, select Save Object As, and save the form as Bookings2.

22. Close the form and the database.



Exercise 24 - Revision: Attachments1. Open the Transport database and open the Buses table in Design

View.

2. Add a new Attachment field to the table with a name of Photo.

3. Add the image files Bus1, Bus2, Bus3 to the Photo field for the first three records on the Buses table.

4. Open the Buses form in Design View.

5. Delete the central image from the form and replace it with the Photo field from the Buses table.

6. Delete the label for the Photo field and resize the field to 4cm by 3cm.

7. Increase the height of the Detail area by about 4cm.

8. In the left side of the new space, insert an E-mail Address Hyperlink with the following settings:

Text to display Click here to send a Service RequestE-mail address [email protected] Service Request

9. In the right side of the new space, insert the image file logo.gif.

10. Use the Property Sheet to add a hyperlink address to this object which links to the document Smalltown.docx. This file can be found in the supplied data folder.

11. Add the ScreenTip, Read the Regulations.

12. Switch to Form View. Navigate through the records. What happens to the central image at record 4?

13. Test the E-mail link. What message text is inserted by default?

14. Test the Image hyperlink. What is the content of the document?

Note: A sample of this web page can be found in the Answers section at the end of this guide.

15. Save the form as Revision and close it.

16. Close the database.



Section 4

Exporting Data


Send a Database Object as an E-mail AttachmentSend a Database Object using a Macro

Export Objects



Exercise 25 - Send ToGuidelines:

Objects can be exported from databases in a variety of ways. For example, any database object can be sent as an e-mail attachment in several different formats. They can be e-mailed internally via an Intranet, or externally via the Internet.

This feature requires that you have a suitable e-mail application (such as Microsoft Outlook) installed, and an active Internet connection if you wish to send the message.

Actions:

1. Open the Household Plants database. Use the Query Design button on the Create tab to create a new Query based on the Plant Index table.

2. Include the fields Common Name, Latin Name and Stock. Select records with Stock less than 15. Run the query.

3. Display the External Data tab and select E-mail from the Export group. The Send Object As dialog box is displayed.

4. Look at the options available. The results of the query can be sent as a spreadsheet, an html file (web page) or a text file (rtf or txt) depending on the intended use. For example if the recipient needs to manipulate numbers on the file it should be sent as a spreadsheet.

5. Select Excel Workbook (*.xlsx) and click OK.

continued over



Exercise 25 - Continued6. Your message handling application will start, with a blank message and

the file Query1.xlsx already present as an attachment.

Note: The version of the message window that appears will depend on the application that is currently in use to send e-mails. The application shown in this exercise is Microsoft Outlook.

7. Double click on the Attached file name and click Open if there is a security message. The results of the query are opened as a spreadsheet in Microsoft Excel. Close Excel.

8. Enter the recipient’s e-mail address in the To box and enter Test message in the Subject box.

9. Click in the message area and type in a brief message.

10. All of the features of the messaging application may be used.

11. Click the Send button to send the message with its attachment to the entered address, or if you do not wish to send it now, close the message and application without saving.

12. Close the query without saving but leave the database open for the next exercise.



Exercise 26 - Send using a MacroGuidelines:

As well as manually sending a database object as an attachment, this can also be achieved by using a macro. The macro could be attached to a button with an option to send the e-mail immediately, with no further intervention.

Actions:

1. In the Household Plants database, open the Plant Details form in Design View.

2. Ensure the Use Control Wizards option is turned off and create a Command Button at the bottom of the Detail area.

3. Display the Property Sheet, select the Event tab and select On Click.

4. Click on the Build button, , select Macro Builder and click OK.

5. Select EMailDatabaseObject as the first Action.

6. Select Table from the Object Type drop down list and select Plant Index from the Object Name.

Note: Even though this macro is run from the Plant Details form, it does not have to send that form as the object. It can send any object in the database such as a table, form, query or report.

7. For this exercise, the Plant Index table will be sent as a web page so that the recipient can then put the page on their Intranet for general viewing. In Output Format select HTML as the required format for the attachment.

8. Enter the required e-mail destination address (for example [email protected]) in the To field.

continued over



Exercise 26 - Continued9. In Subject, enter Test Send, and in Message Text, enter Here is a list

of our current stock.

10. In Edit Message, select Yes. This means the message window will be displayed for possible amendment before sending. If No was selected, the e-mail and its attachment would be sent immediately after clicking the command button, the process would not require any further intervention.

11. Close the macro and select Yes when prompted about save and update. The macro now appears in the Property Sheet.

12. In the Property Sheet, enter a Caption of Send List then switch to Form View.

13. Click the Send List button. Because the Edit Message argument has been set to Yes, your messaging application will open (Outlook 2010 is assumed here) with the message and attachments already entered.

14. Close the messaging application without sending or saving.

15. Save and close the Plant Details form and close the Household Plants database.



Exercise 27 - Exporting ObjectsGuidelines:

Database object can be exported in a variety of formats without having to be sent as attachments.

Actions:

1. Open the Transport database and open the Buses table in Datasheet View.

2. Display the External Data tab and click the Excel button in the Export group. The Export dialog box is displayed.

3. By default, the file will be saved in the Documents folder with a name of Buses and a format of Excel workbook (*.xlsx). Use the Browse button if you want to change the location of the exported file, otherwise leave the settings unchanged.

4. Select the options as shown below.

5. Click OK. The Buses.xlsx file is created and opened in Excel. This can be used like any other spreadsheet, but remember, it is now a separate file, not linked to the database. Any changes to the table will not be shown in the spreadsheet, and any changes made to the spreadsheet will not be applied back to the database table.

Note: Although it could be imported back into the database as a separate process.

6. Close the Excel window and close the Export dialog box without saving the export steps.

7. Objects can be exported in other formats. Close the Buses table and select the Routes report in the Navigation Pane (there is no need to open it).

8. Select the External Data tab and click the More button in the Export group.

continued over



Exercise 27 - Continued

9. Select from the list of options.

10. By default, the HTML file will be saved in the Documents folder with a name of Routes. Use the Browse button if you want to change the location of the exported file, otherwise leave it unchanged.

11. Select the option to Open the destination file after the export operation is complete. Click OK.

12. An HTML Output Options dialog box is displayed. Leave Default encoding selected and click OK.

13. The Routes report is displayed in the web browser. It can now be viewed on-line, on the Internet or on an Intranet.

Note: This process only produces a static view of the report (or other object). It is possible to publish a database so that it can be accessed and amended online, but this requires the use of the Windows Share Point Services application and is beyond the scope of this guide.

14. Close the browser window and close the Export dialog box without saving the export steps.

15. Close the Transport database.



Exercise 28 - Revision: Exporting Data1. Open the College database and the Courses table in Datasheet View.

2. You are the curriculum manager of a local college who has just received notification of some new courses to be provided this year. Send the table as an e-mail attachment in the form of an Excel spreadsheet.

3. The message is to be sent to a friend who runs a centre for the unemployed. Enter any e-mail address in the To box.

4. Enter the subject as New Courses and enter the following message in the message area:

I thought you might be interested in some of the following courses, which we are now able to offer. They are free for unemployed people. Please reply as soon as possible with provisional numbers, as places are limited.

Your name.

5. The message is urgent. Change the priority accordingly.

6. Send the message.

7. Create a Macro called Send Courses.

8. Include an action to send a copy of the Courses table to the same address as before. Send the data as HTML format and add a Subject and Message Text of your choice. Select the option to Edit the message before it is sent.

9. Save the macro and then run it. When the message window opens, right click on the attachment and open it. What application does the attachment open in?

10. Close the attachment and its application, then close the message window without sending.

11. Close the Courses table.

12. Export the Students table as an Excel spreadsheet. Save the spreadsheet as Students.xlsx in the supplied data folder.

13. Close the College database.



Section 5

Database Tools


Compact and Repair a DatabaseUse Linked Tables

Backup/Restore the DatabaseSplit Databases

Set/Remove Database Passwords



Exercise 29 - Compact and Repair a DatabaseGuidelines:

Whenever a database is in use, information and objects are regularly created, updated and deleted. This can drastically increase the amount of space taken up by the database and decrease its efficiency. To ensure a database is running efficiently, it is advisable to compact it once in a while, thereby improving the performance and reducing the size of the database.

Occasionally databases may become damaged. This may be because of a system crash during use, a power cut or hardware failure during use or simply because the database was not closed down properly. The database may be so damaged that it cannot be opened. If so, then it is still possible to repair it.

In Access the same option both repairs any damage to a database and compacts it to a more efficient form.

Actions:

1. Open the Documents window from the Start menu and locate the supplied data folder. Select Details View and note the size of the Egypt and Wine databases.

2. Ensure Access is active but no databases are open.

3. Display the Database Tools tab and select Compact and Repair Database.

4. Locate the supplied data folder in the dialog box and select the Wine database.

continued over



Exercise 29 - Continued5. Click Compact. The Compact Database Into dialog box appears.

6. Select the Wine database again and click Save.

Note: The database can be compacted using the original name, or given a new name and location. Compacting under a new name is a way of backing up a database.

.

7. Select Yes at the prompt. Compacting… appears in the Status Bar for a few seconds. The Wine database is now repaired and compacted.

8. In the Documents window, check the new size of the Wine database.

9. It is easier to Compact the current database. Open the Egypt database but make sure that no objects are open.

10. Display the File tab, and make sure Info is selected from the list on the left. Click Compact and Repair Database from the options.

11. By default the current database is the one processed and stored again under its own name. There are no prompts. After a short delay the database will be displayed again.

12. It is possible to automatically compact a database each time it is closed. With the Egypt database open, click the File tab, and select Options from the list on the left.

13. Select Current Database from the left of the Access Options dialog box and check the option Compact on Close. This database will now be compacted every time it is closed. Click OK.

14. Click OK at the information message that appears.

15. Close the Egypt database and check the new size in Windows Explorer again.



Exercise 30 - Linked TablesGuidelines:

Data from a different application can be imported into an Access database by means of a Linked Table. This is a table which is part of an Access database, but based on an external object such as a spreadsheet or a table from another database.

The table is available for use within the database (in a query for example) but the design of a Linked Table cannot be altered in Access. If alterations to the design are required, they will have to be made in the source object and saved. The alterations should then automatically be updated in the linked table.

The following exercise will look at importing an Excel spreadsheet into Access as a linked table.

When a table is linked to an Excel spreadsheet, the data cannot be amended in the database, only in Excel.

Actions:

1. Open the Transport database and select the External Data tab.

2. Look in the Import & Link group and select the Excel button. A dialog box is displayed so that the source, destination and type of the imported file can be defined.

3. In the Get External Data dialog box, use the Browse button to locate the supplied data file folder and select the file Wages.xlsx. Click Open to return to the dialog box.

Note: To import Excel data, the spreadsheet should be a List type with the data arranged in columns. This is the type of spreadsheet used here.

4. There are options here to create a new table, add records to an existing table, or create a link to the data. Select the option to link to the source data by creating a linked table.

5. Click OK. The Link Spreadsheet Wizard appears. Check that Show Worksheets is the selected option and Company Payroll is the selected worksheet.

continued over



Exercise 30 - Continued6. Click Next. Ensure First Row Contains Column Headings is checked

and click Next.

7. Accept the Linked Table Name of Company Payroll. Click Finish.

8. Click OK at the Finished linking table message.

9. A new Company Payroll table appears in the Navigation Pane with the symbol indicating that it is linked to an Excel spreadsheet.

10. Open the Company Payroll table in Datasheet View and view all the records. Try to change any data in the table, this will not be allowed.

11. Switch to Design View. A warning message will be displayed. Click Yes to open the view anyway and see the design. Properties can be changed on the screen but it is not possible to save them. Close the table without making any changes but leave the database open.

12. Start Excel and open the Wages spreadsheet from the supplied data files. The Hours Worked for the first employee is 100. Change this figure to 75 in the spreadsheet then delete the last five records. Save and close the spreadsheet and close Excel.

13. In Access, open the Company Payroll table in Datasheet View. All the amendments have automatically been updated. Close the table.

14. In the Transport database, display the External Data tab and select the Access button from the Import & Link group.

15. Click Browse and open the Pet Shop database. Select the option to link to the data source and click OK.

16. Select the Customers table and click OK. Customers appears as a linked table in the Transport database.

17. Open the Customers table and change one of the telephone numbers. Data amendment is allowed for a linked Access table.

Note: Design changes are still not allowed.

18. Close the Customers table, right click on it in the Navigation Pane and select Delete. Click Yes at the prompt. The link is deleted (but not the original table). Leave the database open for the next exercise.



Exercise 31 - Backup/Restore a DatabaseGuidelines:

It is important to backup a database to prevent the loss of data in the event of a catastrophe. It is also good practice to backup a working database before applying any updates, in case these cause unforeseen problems.

Access 2010 has a Backup function which allows copies to be easily made. The Backup process copies the database file (extension .accdb). All database information is stored in this single file, i.e. tables, queries, data, etc.

Note: Backups can also be made indirectly, either by using the Compact and Repair feature as described in a previous exercise, or by using Save Database As from the File tab.

Actions:1. With the Transport database open, display the File tab and select Save

& Publish.

2. From the options on the right of the window, select Back Up Database and click Save As. The Save As dialog box is displayed.

3. The folder for the backup defaults to the original database folder, and the File name for the backup file defaults to the original name plus the date of the backup. Both of these can be changed, but for this exercise accept the defaults and click Save. The backup copy is saved.

4. Leave the Transport database open.

Note: There is no specific Restore process other than to rename the backup database file back to the original name. The backup itself is a completely standard working database.



Exercise 32 - Database SplitterGuidelines:

The Database Splitter function is usually used when a network is in place and more than one user needs access to the database. It splits a database into a new Back-end database, containing only data tables and the original (Front-end) database which contains everything but the data tables. Instead of the data, the Front-end database contains Linked tables linked back to the single copy of the data on the Back-end database. Copies of the original database can then be given to a number of users, but only a single copy of the actual data is ever maintained.

Users of the original database can then create their own queries, forms, reports etc. and can add edit or delete data in the tables held in the Back-end database. They cannot, however, amend the design of the original tables.

Typically a database administrator will control the Back-end database which contains the single copy of the actual data. If alterations to the table’s design are required, for example, the addition of a new field, the database administrator will make the necessary changes in the Back-end database, which will change the table design in all copies of the original database.

Note: It is wise to make a backup copy of a database before splitting it.

Actions:

1. With the Transport database open, select the Database Tools tab and select Access Database from the Move Data group. The wizard starts.

continued over



Exercise 32 - Continued2. Click on the Split Database button.

3. The Create Back-end Database dialog box appears. The folder location should default to the supplied data folder, if not locate it now.

4. Leave the file name as Transport_be, as suggested by Access and click Split. After a few seconds, the following message appears:

5. Click OK. The current database is now the Front-end database containing only linked tables. Notice how all tables in the Database Window now have arrows next to them to show that they are linked.

6. Open the Buses table in Datasheet View. Data can be amended as before. Change the Capacity on the first record from 55 to 44.

7. Switch to Design View.

8. Because the tables are linked, design modifications are not allowed. Click No to cancel the process.

9. Close the table and the Transport database. Open the Back-end database Transport_be. Notice that this database only contains the data tables. Open the Buses table in Design View. There are no warning messages and the design could now be amended.

10. Switch to Datasheet View. The data amendments made previously to the Front-end database are seen here in the actual tables.

11. Close the table without saving and close the Transport_be database.



Exercise 33 - Set Database PasswordsGuidelines:

If desired, a password can be set up on a database to prevent unauthorised access. Once set, any user attempting to open the database from any location, must enter the password.

Actions:

1. With all databases closed, select Open from the File tab.

2. Locate the supplied data folder Access 2010 Advanced and select (but do not open) the College database.

3. Click the drop down arrow at the right of the Open button and select Open Exclusive from the list.

Note: Open Exclusive prevents any other users accessing the database whilst you have it open. If your database is on a system where it can be accessed by other users it is necessary to use Open Exclusive before setting or removing passwords.

4. Display the File tab, make sure Info is selected and click Encrypt with Password.

5. Enter Protected as the Password, then repeat it in the Verify box.

Note: Remember the password exactly. Passwords are case sensitive.

6. Click OK to set the password, OK again at the message, then close the database.

7. Open the College database again. The following dialog box appears:

8. Enter the correct password and click OK to open the database.

9. Close the College database.



Exercise 34 - Remove a PasswordGuidelines:

Once a password has been set up on a database, it can be removed when required. The removal process prompts for the password before it will function, so it follows that it is necessary to know the password in order to remove it.

Actions:

1. Open the College database using the Open Exclusive process as described in the previous exercise.

Note: Open Exclusive is necessary if your database is on a system where it can be accessed by other users.

2. Enter the password in the Password Required dialog box and click OK.

3. Display the File tab, make sure Info is selected and click Decrypt Database.

4. Enter the password in the box provided and click OK.

5. The database is no longer password protected. Close it.

6. Reopen the database to ensure no password prompt appears. Close the database.



Exercise 35 - Revision: Database Tools1. Make a backup copy of the Wine database. Save the copy in the supplied

data folder and name the backup Wine_Backup.

2. Close the Wine database and open Wine_Backup.

3. Create a linked table in the database which is linked to the Students table in the College database.

4. Rename the linked table as Customers and open it.

5. Change the address for Dave Prescott to 4 Steeply Drive.

6. Close the table and close the database.

7. Open the College database and open the Students table. What is the address for Dave Prescott?

8. Close the table and close the database.

9. Without opening it, compact and repair the Pet Shop database, compacting it into a new database called Creatures.

10. Split the Creatures database, creating a new Back-end database, Creatures_be.

11. In which of the two databases Creatures or Creatures_be would you be able to add another field to the Pets table?

12. In which of the two databases Creatures or Creatures_be would you be able to change the layout of the Add/Edit Pets form?

13. Apply a password of pass to the Creatures_be database. What must be done to the database before a password can be applied?

14. Does the password apply when using the Creatures database?

15. Remove the password from the Creatures_be database and close it.



Section 6

Database Analysis


Analyse a TableUse Performance AnalyserUnderstand Documenter

Use Object Dependencies



Exercise 36 - Table AnalyzerGuidelines:

The Table Analyzer will analyse a table and make changes to improve its efficiency. In particular, it will even split an inefficient table into simpler related tables, if appropriate. This is sometimes known as Normalisation. It will not change the original table.

Actions:

1. Open the Chemicals database and view the information in the Elements table. There are 30 elements listed but many of the Classification and Colour combinations are repeated e.g. Gas - Colourless and Metal - Silvery. Close the table.

2. Select the Database Tools tab and select Analyze Table from the Analyze group.

3. Read the information and click on the first Show me an example button to see an example of how space is wasted.

4. Close the information box and click the second Show me an example button to see an example of how mistakes can arise.

5. Close the information box and click Next.

6. Read the information on this screen, including the two examples, then click Next.

7. Select the Elements table as the table to be analysed and click Next.

continued over



Exercise 36 - Continued8. Select Yes, let the wizard decide so that the table will be split

automatically. Click Next.

9. The Wizard will then make a suggestion for the split of fields between the two new tables.

10. Make sure the split is as shown above. Enlarge the field lists if necessary to see all the entries. Fields could be moved from one table to the other by clicking and dragging if required.

11. Select Table1 and click the Rename Table button, . Call the table Symbols.

12. Click OK, then select Table2 and rename that as Classifications.

13. Click Next, read the text, then click Next again.

14. This screen highlights possible errors in the new tables, where similar values are possibly misspellings of the same value. Click Next without making any corrections. Click Yes at the warning message.

15. Read the text, select Yes, create the query and click on Finish. If a help screen appears, close it.

continued over



Exercise 36 - ContinuedNote: The Analyser has split the original table into 2 smaller linked tables. It

also automatically creates a Query based on these two tables, which exactly reproduces the original table. The query is given the same name as the original table and replaces it in any existing forms, reports or pages. The original table is retained but given a new name.

16. The new query (Elements) is open. Close it and look at the Navigation Pane. There are three tables. Elements_OLD is a copy of the original table and can be deleted when the operation of the new structure has been checked. Any existing forms or reports which referenced this table, now reference the new Elements query.

17. Open the two new tables, Symbols and Classifications to see the new structure. There are only 15 different Classification/Colour values for the 30 Symbols records so the new format is now more efficient.

18. The Symbols table is a subdatasheet of the Classifications table. Expand the subdatasheet for the first classification to see all Colourless Gas elements.

19. Close the Symbols table. In the Classifications table enter a new record with Classification of Solid and Colour of Yellow. Close the table.

20. Open the Symbols table and enter a new record. Enter 16 as the Atomic No, Sulphur for the Element and S as the Symbol. Enter the Atomic Mass as 32, the Melting Point as 119 and the Boiling Point as 445.

21. In the Lookup to Classification field, a drop down arrow is displayed. Click the arrow to show a list of the available Classifications. Scroll down and click Solid - Yellow. The information is automatically retrieved.

22. Close the table but leave the database open.



Exercise 37 - Performance AnalyzerGuidelines:

The Performance Analyzer tool will analyse the performance of every object in the database, making suggestions on how to improve the performance, if possible.

Actions:

1. With the Chemicals database open, display the Database Tools tab and select Analyze Performance. Select the All Object Types tab in the dialog box.

2. All the objects in the database are listed. Click Select All then OK. After a moment the Analysis Results are displayed.

3. Read the results carefully. Notice different levels of importance can be assigned to the results. Click Close. Leave the database open for the next exercise.



Exercise 38 - DocumenterGuidelines:

The Documenter facility produces a printout of the design characteristics of selected objects within the database for reference purposes.

Actions:

1. With the Chemicals database open, select Database Documenter from the Database Tools tab.

2. Make sure the Tables tab is selected in the Documenter dialog box.

3. Click Select All to include all tables in the analysis.

4. Select the Current Database tab and then select Relationships.

5. Click OK. After a while, the report appears in Print Preview mode.

6. Use the features of Print Preview to view the different pages. There should be several pages of information on the 3 tables within the database and a page on the Relationships between the tables. Print the report, if required.

7. Close the report (it is not saved) but leave the database open.



Exercise 39 - DependenciesGuidelines:

The Object Dependencies feature displays quickly which objects in a database are dependent on which other objects. This is particularly useful in a large complex database. For example, before the design of a table is altered, a list can be seen of every other object that will be affected.

Actions:

1. With the Chemicals database open, select, but do not open, the Elements query.

2. Select Object Dependencies from the Database Tools tab.

Note: If a prompt appears to enable an AutoCorrect option, click OK. All objects must be closed for this operation to run.

3. An Object Dependencies task pane appears on the right. By default it shows which other objects in the database obtain some or all of their data from the selected object.

4. Click Objects that I depend on in the task pane. The display changes to show those objects that supply data to the selected object.

5. Select the Properties form in the Navigation Pane and click Refresh in the task pane.

6. The display now shows that the only source of data to the Properties form is the Elements query.

7. Close the Object Dependencies task pane and close the database.



Exercise 40 - Revision: Database Analysis1. If the Wine_Backup database was not created previously, backup up the

Wine database now with a name of Wine_Backup.

2. Open the database Wine_Backup and analyse the Products table using the following steps.

3. Let the wizard decide which fields go in which tables.

4. Leave the fields in Table1 and Table2 as suggested by the wizard.

5. Rename Table1, Wines and Table2, Origins.

6. Ignore the typographical errors.

7. Create a query. Close Help, if it opens.

8. Add the following record to the query:

Bouches du Rhone, Red, Smooth, Lookup to France Caves de Chouan, price £3.29, stock 400.

9. Close the query.

10. Analyse the performance of the Wine database, selecting All Object Types.

11. Click on each of the Ideas in the Analysis Results to see a description in the Analysis Notes.

12. Close the Performance Analyser.

13. Display the Documenter and from the Forms tab click Select All items.

14. Read some of the report, but don't print it.

15. Close the report and the database.



Section 7

Database Options and Utilities


Use Option SettingsAdd Startup Controls

Create Key Press ControlsApply Report Page Numbers

Use Navigation Form



Exercise 41 - OptionsGuidelines:

Many settings which control how Access operates can be set by using the Options dialog box. Most of the Option settings apply to the operation of Access in general, although some are specific to the currently selected database. If no database is open, these settings will not be available.

Actions:

1. Open the Wine database, display the File tab and select Options from the list on the left. The Access Options dialog box is displayed.

2. The options available are divided into headings which are listed down the left of the dialog box. Select General.

3. Look at the options that are available in the main part of the screen. The Color Scheme setting changes the appearance of the Access screen surround but does not alter its operation in any way. All screen shots in this guide are taken with this option set to Silver.

4. Select Current Database on the left. These settings here apply to the currently open database only. They are described in the next exercise.

continued over



Exercise 41 - Continued5. Select Datasheet on the left. The settings here control the default

appearance of the datasheets in all databases. Datasheet format can be changed for an individual datasheet by using commands on the Ribbon.

6. Under Default font, change the Size to 12pt and click the Italic option. Click OK.

7. Open the Orders and Products tables in Datasheet View to see the revised table format. All tables that have not been individually formatted will look like this. Close both tables.

8. Display Access Options again and make sure Datasheet is selected.

9. Change the Size to 11pt and deselect the Italic option. Click OK.

10. Open the Orders and Products tables in Datasheet View to see that tables have now resumed their original appearance. Close both tables.

11. Display Access Options again and select Object Designers. Look at the options. They control settings such as the default table design values and query settings.

12. Change the Default text field size to 60. The next time a new text field is defined in a table it would have a default length of 60 characters.

13. Select Proofing on the left of the Access Options dialog box. Settings here control the automatic correction feature in Access.

14. Select Client Settings on the left of the Access Options dialog box. There are many options here grouped under various headings. Scroll down and read them all to see what settings are available.

15. Other options on the left of the dialog box include:

Customize Ribbon, which allows the Ribbon to be customised with new groups and/or commands,

Quick Access Toolbar which allows the buttons on the Quick Access Toolbar to be customised

Trust Center which contains security statements and options.

16. Leave the Access Options dialog box open.



Exercise 42 - Current Database OptionsGuidelines:

The Current Database settings in the Access Options dialog box are the only ones which apply to a single database. There are options to control how the current database is opened and closed. In particular it can be set up to open a form automatically when a database first opens. It is similar in this way to an AutoExec macro, although both features can do more than just open a form. If both features are present in a database, the AutoExec macro runs after the Startup controls have been applied.

Actions:

1. The Wine database should be open and the Access Options dialog box displayed. Select Current Database from the left of the dialog box.

2. Type your name in the Application Title, and click on the down arrow in the Display Form box and select Orders Form.

3. Further down, remove the check from Display Status Bar.

4. The Document Window Options control how multiple objects are displayed. All screen shots in this guide are taken with this option set to Tabbed Documents.

continued over



Exercise 42 - Continued5. In the Navigation section, remove the check from Display Navigation

Pane.

6. Click OK. There will be a message. Click OK to remove it then close the Wine database.

7. Open the Wine database. Notice your name appears in the Application Title Bar.

8. The Orders Form is displayed but the Navigation Pane is not, so the only way any other objects can be opened is via buttons on this form.

Note: Often the form which is displayed in this manner is a Navigation Form or Control Panel Form which acts as a menu to access specific objects. The database designer then controls which parts of the database a user can access. Navigation Forms and Control Panels are covered in a later exercise.

9. Display the Access Options dialog box, Current Database page, and under Document Windows Options select Overlapping Windows.

10. Select the Display Navigation Pane option and click OK. Click OK at the message.

11. Close the Wine database and reopen it. Open the Suppliers Form as well as the Orders Form. Notice the different manner in which multiple objects are displayed.

12. Display the Access Options dialog box, Current Database page. Select (none) as the Display Form option, check the Display Status Bar option, and under Document Windows Options select Tabbed Documents.

13. Click OK and OK at the message, close the Wine database and reopen it. The database opens in the original layout, with no form open and the Navigation Pane displayed.




Exercise 43 - Key Press ControlsGuidelines:

Quick key presses can be assigned to forms, buttons, etc., instead of clicking buttons or menu commands. To assign a key press, switch to Design View and in the properties of the form or button, place the ampersand character, &, before the letter which is to be used for the key press.

Actions:

1. Using the Transport database, open the Buses form in Design View. Key presses are to be added to the three command buttons.

2. Select the Find button and view its Property Sheet. Select the Format tab and in the Caption property, insert an ampersand character before the F of Find.

3. Select the Print Report button and in the same way, place the ampersand character before the P of Print.

4. The Key Press does not have to be the first character of the caption. Select the Servicing button and view its Property Sheet. Select the Format tab and change the Caption to Open Servicing Form, then insert an ampersand character, &, before the S of Servicing.

5. Close the Property Sheet and save the form.

6. Switch to Form View. Notice that the buttons now have a letter underlined.

Note: Ensure the buttons are large enough for the underline to be viewed.

7. Press <Alt P> to print the entire Routes report.

8. Press <Alt F> to find a record. Cancel the dialog box.

9. Press <Alt S> to open the Servicing form.

10. Close the form and the database.



Exercise 44 - Page Numbers for ReportsGuidelines:

Page numbers can be added to reports. They are only visible in Print Preview and the printed report.

Actions:

1. Using the Household Plants database, ensure that the Index Report is open in Design View.

2. Make sure the Report Design Tools - Design tab is displayed and select the Page Numbers button from the Header/Footer group.

3. Choose Page N and Position at Bottom of Page [Footer], Alignment Center. Click OK.

4. Change to Print Preview. Zoom into the bottom of the report page to view the page number. View page 2 to ensure the number has changed.

5. Return to Design View and select the page number box in the footer, then press <Delete> to remove it.

6. To create a more advanced page number setting, display the Page Numbers dialog box again and choose the Page N of M from Format.

7. Click OK and view the results.

8. Save the report and close it.




Exercise 45 - Navigation FormGuidelines:

A Navigation Form is a special type of form containing links which open various objects within the database. Thus it acts as a menu and so helps the user to navigate between forms and reports in a database. It can also help control which features in a database are available to the user.

This feature is particularly useful if databases are to be published as web sites, as the Navigation Pane will not be available. A Navigation Form will then act as a Home Page to the system, and can include information, images and hyperlinks.

A Navigation Form can open other Navigation Forms, so for large complex databases a structure of menus can be created. For example, a Navigation Form could be created to include all data entry forms in a database and another could include all reports. A main form could then include only links to the forms and reports navigation forms.

Some database templates provided in Microsoft Access contain examples of Navigation Forms.

Actions:

1. Open the Wine database and display the Create tab.

2. Click Navigation from the Forms group. Different options for arranging the links (tabs) are listed.

3. Select Vertical Tabs, Left. A blank Navigation Form is opened in Layout View. If the Field List is displayed on the right, close it now.

4. Click and drag Orders Form from the Navigation Bar to the [Add New] box on the Navigation Form.

continued over



Exercise 45 - Continued5. Repeat the process for the Products Form and the Supplier form.

6. Click in the Navigation Form title on the form (not the tab) to select it then click again to start editing the text.

7. Delete the existing text and type Data Entry.

8. Save the form as Data Entry and close it.

9. A new form can be created to act as a starting form (Home page). Display the Create tab and select Blank Form from the Forms group.

10. Switch to Design View and click in the Detail area.

11. Display the Form Design Tools - Format tab. Click Shape Fill from the Control Formatting group and apply a dark red colour as background.

12. Display the Form Design Tools - Design tab and add a label (6cm by 2cm) near the top of the Detail area.

13. In the label, enter the text Waldridge Wines. Format it as 26pt, White.

14. Below this, draw a second label the same size and enter the text Data Entry Menu. Format it as 20pt, White.

15. Below this, draw a third label the same size and enter the text Select an option from the left. Format it as 18pt, white.

16. Save the form as Data Entry Home and close it.

17. Now the new form can be added to the Data Entry form. Open the Data Entry form and switch to Layout View.

18. Click and drag Data Entry Home from the Navigation Bar to the [Add New] box on the Data Entry form.

continued over



Exercise 45 - Continued19. Drag Data Entry Home up to the top of buttons on the Data Entry form.

20. Save the Data Entry form and close it.

21. The Data Entry form can be set to display when the database opens. Click the File tab, select Options then Current Database.

22. Set Data Entry form as the Display Form, to be displayed as the database opens.

23. Click OK then OK again at the message.

24. Close the database and reopen it. The Data Entry form is displayed in Form View.

25. Click Orders Form to display that form.

26. Click Products Form to display that form.

27. Click Data Entry Home to return to the starting menu.

28. To cancel the automatic display of the Data Entry form, click the File tab, select Options then Current Database.

29. Set (none) as the Display Form and click OK


Note: Remember, the Navigation Pane can be hidden when using Navigation Forms to control access to the database features.



Exercise 46 - Revision: Database Options1. Open the Household Plants database.

2. Use the Options feature to change the default Datasheet properties.

3. Change the Default cell effect to Sunken and change the Font Size to 12pt and apply an Italic effect.

4. Open the Orders table and make sure all data is fully displayed. Widen columns where necessary.

5. Print a copy in Landscape orientation.

Note: An example of the printed table is included in the Answers section at the back of the guide.

6. Reset the Datasheet defaults to Flat cells, 11pt and not Italic.

7. Close the table and the database.

8. Open the Egypt database.

9. Open the Airport report in Design View and insert page numbers in the form Page N of M in the centre of the Page Footer.

10. Insert a field to show the current date (not time). Use cut and paste to move the field to the left of the Page Footer. Print Preview the report and save it.

11. Add a command button to the Form Footer of the Booking Form. Give the button a caption of Airport and an action to open the Airport report in Print Preview mode.

12. Create a Key Press control of <Alt A> for the button.

13. Create a Navigation Form named Horus Tours for the database. It should include buttons to open the forms Booking Form, and Excursions.

14. Use the Options feature to the database so that the Horus Tours is always displayed when the database opens.

15. Save the form, close the database and re-open it.

16. Check that the Horus Tours form is automatically opened then use a button to open the Booking Form.

17. Use a key press to preview the Airport report. Check that page numbering has been inserted correctly.

18. Close the report and the form. Remove the feature that opens the Horus Tours form automatically, then close the database.



Section 8

Control Panel


Create a Control Panel FormCreate Sub Control Panel Forms

Create Navigational MacrosApply Macros to the Command Buttons

Create an Option Group including Macros



Exercise 47 - Control PanelGuidelines:

Navigation Forms (as described earlier) are a convenient way of building a simple control structure into a database, but they are limited in the options that they can provide. It is often desirable to create a more versatile user-friendly interface, which will make a database into an application. One way is to use Control Panel forms which will allow users to move from one part of the database to another, without the need for a detailed understanding of Access. The control panel must be easy to use. All the user should need to know is which button to press. Buttons can activate macros which can provide a wide range of possible actions.

A typical database application can be organised into 3 levels:

Main Control Panel Contains buttons which open further sub control panels and a button to close the database.

Sub Control Panel Contains buttons which open specific forms and return to the main control panel. It may contain option groups which print or preview specific reports.

Forms Contains a button to return to the main control panel.

The following diagram shows a very basic form design, representing the first 2 construction levels of a fully integrated database.

continued over



Exercise 47 - ContinuedEach individual control panel then functions as follows, down to the Form level, for example:

The following exercises will demonstrate, using examples, how to create the main control panel, then each sub control panel. Macros will then be added to each button in the control panel, which will open one form and close another. Option groups will be added to each sub control panel, which will give options of what reports can be printed or viewed. A Return to Sub Control Panel button will be added to existing forms to complete the structure.

Note: A similar effect can be obtained by using a series Navigation Forms but these can typically only open forms or reports without any options.

Note: Often Control Panels are not created as separate objects but are incorporated into existing objects. For example, in a common scenario, the main form, where most of the database work is processed, is opened when the database opens. This form then has links (buttons) to other subsidiary forms, which will all have options to return to the main form.



Exercise 48 - Main Control PanelGuidelines:

This exercise will create a main Control Panel, automating the main tasks to be performed by the user.

Actions:

1. Open the Pet Shop database and create a blank form but do not select a table or query to base it on.

2. Draw a label at the top of the form. Type Main Control Panel in the label and increase the font size to 24. Centre the text.

3. On the Format tab of the Property Sheet for the label, set a Special Effect of Raised. Add a background colour to the form, and a different background colour to the label. Resize/reposition the label as appropriate.

4. Ensure the Control Wizard is switched off. Draw a button below the label, at the left of the form.

5. Create a second button to the right side of the form.

6. Create a third button below these two (see the picture below).

7. Add the following captions to the buttons Pets, Customers, and Quit respectively, until the form looks like the following picture:

8. Save the form as Main Control then close the form.

Note: Macros will be added to the buttons in a later exercise. An exit macro will also be set up to close the database completely.



Exercise 49 - Sub Control PanelsGuidelines:

When the main control panel form has been created, a sub form for each option on the main control panel can be created. This will contain buttons which when selected, will open further forms. Option groups may be added to give choices such as which reports are available, etc. Each sub control panel should contain a Return to Main Control Panel button.

Actions:

1. With the Pet Shop database open, create a new blank form but do not select a table or query to base it on.

2. Draw a label at the top of the form. Type Pets Menu in the label and increase the font size to 24. Centre the text.

3. Apply a Special Effect of Raised, add a background colour to the form and a different background colour to the label to match the previous form. Resize/reposition the label as appropriate.

4. Ensure the Control Wizard is switched off. Draw a button below the label, at the left of the form.

5. Create two more buttons (see the picture below). Add the captions Add/Edit Pets, Delete Pets and Return to Main Control respectively.

6. Save the form as Pets Menu.

7. Change the three caption references to Pets (label and two buttons) to Customers. Use the File tab and the Save Object As option to save the new form as Customer Menu, then close it.



Exercise 50 - Apply Macros to a Control PanelGuidelines:

To automate a database, all control panels and forms should contain controls which when selected or clicked on, take the user to another specific form or control panel and back again. This is achieved by having macros attached to the various controls. The macros could be created separately and assigned to the buttons, but for this exercise they will be embedded.

Actions:

1. If the security banner is displayed for this database, click Enable Content. Open the Main Control form in Design View.

2. Ensure the Use Control Wizards option is turned off, select the Pets button and display the Property Sheet.

3. Select the Event tab and select On Click, Click on the Build button, , select Macro Builder and click OK. A new macro is opened.

4. Add an Action of CloseWindow. Enter an Object Type of Form, an Object Name of Main Control and a Save option of No.

5. Add another Action of OpenForm. Enter a Form Name of Pets Menu, a View of Form and a Window Mode option of Normal.

6. Save the macro and close it.

7. Select the Customers button and build a similar macro for the On Click event, except that the Form Name in the OpenForm action will be Customer Menu. Save the macro and close it.

8. Select the Quit button and build a macro for On Click, with an action of QuitAccess and options of Save All. Save the macro and close it.

9. Save and close the form.



Exercise 51 - Apply Macros to Sub Control PanelsGuidelines:

Similar macros can be created for sub control panels to open the individual forms.

Actions:

1. Open the Pets Menu form in Design View.

2. Select the Add/Edit Pets button and display the Property Sheet.

3. Select the Event tab and select On Click, Click on the Build button, , select Macro Builder and click OK. A new macro is opened.

4. Add an action of CloseWindow. Do not enter any parameters, the default is to close the current window.

5. Add another Action of OpenForm. Enter a Form Name of Add/Edit Pets, a View of Form and a Window Mode option of Normal.


7. Select the Delete Pets button and build a similar macro for the On Click event, except that the Form Name in the OpenForm action will be Delete Pets. Save the macro and close it.

8. Select the Return to Main Control button and build a similar macro for the On Click event, except that the Form Name in the OpenForm action will be Main Control. Save the macro and close it.

9. Save and close the form.

Note: If you wish, use the information in this exercise to add similar macros to the Customer Menu form. Consider it a revision exercise. All the necessary Customer forms are available in the database.



Exercise 52 - Creating an Option GroupGuidelines:

Adding an option group to a form is a way of displaying information where choices are available, such as a choice of reports to print. Macros can be combined with an option group, so that the option group can select a target, e.g. a specific report, and the macro can determine what action will be performed e.g. printing or previewing.

Actions:

1. Open the Pets Menu in Design View. In the Controls group of the Design tab, turn on the Use Control Wizards option.

2. Draw an Option Group to the right side of the form.

3. Enter the Label Names: Pets List and Customer List. Click Next.

4. Select Pets List as the default choice and click Next and Next again.

5. Select Option buttons and the style Sunken.

6. Click Next.

7. Change the caption to read Reports and click Finish.

8. From the Controls group, turn the Use Control Wizards option off, select the Button control and draw two buttons in the right of the option group. You may need to enlarge the option group first.

9. Enter the captions as View and Print respectively.

continued over



Exercise 52 - Continued10. Resize/reposition the buttons and option group as appropriate until the

form looks like the following diagram:

11. Select the Option Group and view its properties.

12. From the All tab select the Name property and name the option group Report Choice. The Default Value property should be set to 1.

13. Select the View button and view its properties. Select the On Click property and click the build button. Select Macro Builder.

14. Select an action of If. Enter [Report Choice]=1 as the condition, to the right of the If command.

15. Select an action of OpenReport and the options as shown below. This opens the Pets List report in Print Preview mode if the Pets List option (=1) was selected in the Option Group.



16. Click Add Else If from below the first action definition. This time the condition will be [Report Choice]=2 and the OpenReport action will print preview the Customer List.


18. Select the Print button and view its properties. Select the On Click property and click the build button. Select Macro Builder.

19. This will be identical to the macro for the View button, except each OpenReport action will have the View option of Print.


21. Save the form.

22. Open the Main Control Panel.

23. Click the Pets button to display the Pets Menu.

24. Make sure Pets List is selected in the Reports group and click View. The Pets List report is opened in Print Preview.

25. Close the Pets List report, select Customer List in the Reports group and click the Print button. The Customer List is printed on the default printer.

26. End by closing all forms and then close and the Pet Shop database.



Exercise 53 - Revision: Control Panel1. Open the Wine database. This exercise will manually create a control

panel form to access the system.

2. Before that, a new Suppliers report will be needed. Select but do not open, the Suppliers table. Click Report from the Create tab.

3. Close the report, select Yes when prompted to save and accept the name of Suppliers by clicking OK.

4. From the Wine database create a main control form with options to open any of the 3 forms Orders, Products or Suppliers. It should also contain an exit database button. Save the form as Control. Note that this is a simple system that does not involve any Sub Control Panels.

5. Create a Return to Control Panel button to the bottom left of the 3 forms mentioned above.

6. Add an option group to the control panel form. The option group is to be called Lists, the option choices will be Print or Preview.

7. Add two buttons to the option group, to be called Products and Suppliers. Select the Products button and build a macro for this button that will either preview or print the Products report depending on the setting of the option group buttons.

Note: A listing of the macro is included in the Answer section at the back of this guide. Notice that this is the other way around to the example in exercise 52 where the control buttons decided between Print or Preview and the option group buttons set which report to run.

8. Repeat for the Suppliers button so it that will either preview or print the Suppliers report depending on the setting of the option group buttons.

9. Change the Options for this database so that the Control form is opened every time the database is opened.

10. Save all objects and close the database.

11. Open the Wine database to check the operation of the Control form.




Section 9

Relationships and Joins


Use One-To-Many RelationshipsUse Many-To-Many Relationships

Use One-To-One RelationshipsUnderstand Join Types



Exercise 54 - One-To-ManyGuidelines:

Applying relationships allows a database to store its data on many smaller tables which are linked together to form a complete system, improving its overall efficiency. The most common relationship type used in databases is One-To-Many.

For example a table of Orders may contain information on the product ordered. There is no need to have all the product details on every order record, a single field can link to a Product table where all the information for each product is held once on a single record. One record on the Product table can be linked to many records on the Orders table, so the relationship is One-To-Many.

Actions:

1. Open the Wine database, close any open forms, and display the Database Tools tab. Select the Relationships button. Relationships have already been set up between the Orders, Products and Suppliers tables.

Note: There may be extra fields listed which were created in earlier exercises.

2. Notice the linked field Reference in the Products table is a Primary Key (indicated by the key icon) so it must be unique. There can be only one record with a specific Reference. On the Orders table, Product Ref is not unique; many records can contain the same value. The relationship shown between Orders and Products is therefore One-To-Many.

Note: A similar argument applies to the relationship between the Suppliers and Products tables.

3. Display the Create tab and select Query Design.

4. In the Show Table dialog box, select the Orders table, click Add then select the Products table and click Add. Click Close. The two tables are added to the query with the link shown.



continued over



Exercise 54 - Continued5. Insert Date, Surname and Quantity from the Orders table into the query

grid.

6. Instead of using Product Ref, insert Name and Supplier from the Products table.

7. Sort the query by Date (Ascending).

8. Run the query. The name of the product and the supplier name are shown on the query although these are not included on the Orders table. The same product name, e.g. Macon Villages, can appear many times in the query, but it is only stored once on a single record on the Products table.

9. Chains of One-To-Many relationships can be built. Switch to Design View and select Show Table from the Query Setup group.

10. In the Show Table dialog box, select the Suppliers table, click Add then Close.

11. The Suppliers table is displayed already linked to the Orders table because a relationship exists. Data from the Suppliers table is now available in the query. Add the Address and Telephone Number fields to the grid.

12. Run the query. Once again the same supplier details may appear many times but each is held only once on the Suppliers table.

13. Save the query as Linked and close it.

14. Close the Wine database.



Exercise 55 - Many-To-ManyGuidelines:

Sometimes a relationship exists between tables, which is not One-To-Many. For example a database in a college may have a table for part time Courses and a table for Students. Any course may have many students enrolled, but any student may be on more than one course. The relationship is therefore Many-To-Many.

This type of relationship does not actually exist in Access, but has to be built from two One-To-Many relationships, using an intermediate table, sometimes called a Link table. In this case the Link table need only contain two fields to define which students are on which course, in the form of Student ID and Course Ref.

Actions:

1. Open the College database. Open the Courses table to see a list of available courses and open the Students table to see a list of students.

2. Open the Register table to see a list of which students are on which courses. This table will be the Link table in creating a Many-To-Many relationship between the Courses and Students tables.

Note: When creating new Many-To-Many relationships, it may be necessary to build the Link table manually.

3. Switch to Design View. The register table has two fields which together make up the Primary Key. The same Student ID or Course Ref may appear several times but any specific combination is unique.

4. Close all tables.

5. Display the Database Tools tab and select the Relationships button. Use Show Table to add the tables Courses, Register and Students.

6. Drag the Ref field from Courses to the Course Ref field in Register.

7. In the Edit Relationships dialog box select Enforce Referential Integrity and click Create.

8. The Ref field is unique in the Courses table (it is the Primary Key), but is not unique in the Register table (it is only part of the Primary Key). This means that the link created is One-To-Many.

continued over



Exercise 55 - Continued9. Drag the ID field from Students to the Student ID field in Register.

Select Enforce Referential Integrity as before and click Create. Once again the link created is One-To-Many.

10. Two One-To-Many links have been created and the overall effect is a Many-To-Many relationship between Courses and Students.

11. Close the Relationship object, saving when prompted.

12. Create a query in Design View. Add all three tables.

13. Include the fields Ref, Course and Qualification from the Courses table.

14. Include the fields ID, Surname and Address from the Students table.

15. Add the criteria E01EE to the Ref column.

16. Run the query to see all students on the Electrical Engineering course.

17. Switch back to Design View, remove the criteria from the Ref column and add the criteria Hunter to the Surname column.

18. Run the query to see all the courses that Ms Hunter has enrolled on.

19. Save the query as Linked2 and close it.

20. Leave the College database open.



Exercise 56 - One-To-OneGuidelines:

It is possible to have a One-To-One relationship between tables in which one record in one table is linked to one record in another table. This is not common as in effect it only splits one record over two tables, but occasionally it is used to keep parts of a record completely separate for organisational or security purposes.

Actions:

1. In the College database, create a new Table in Design View.

2. Add three fields; ID, a Number field; Nationality, a Text field; and Age, a Number field. Make the ID field the Primary Key.

3. Save the table as Info and close it.

4. Display the Database Tools tab, select the Relationships button and add the Students and Info tables.

5. Link the tables by dragging the ID field from Students to the ID field in Info (it must be this way around). Set Enforce Referential Integrity. Because both ends of the link are unique (Primary Keys) the relationship is One-To-One.

6. Right click on the link between the Students and Register tables and select Delete from the shortcut menu. Click Yes at the prompt to remove this link. Close the Relationship display, saving if prompted.

7. Open the Info table in Datasheet View. Add a record for an ID = 99, Nationality = British, Age = 32. Because this table is linked to the Students table this will not be allowed (there is no student ID 99).

8. Change the ID to 1. The record can now be added.

9. Close the Info table and open the Students table. Click the at the left of the first record. The linked record from the Info table is opened.

10. Close the table and the database.



Exercise 57 - JoinsGuidelines:

Joins describe the links between tables. They affect the way queries select records when related tables are involved.

The default join type, which is applied by Access automatically, is an inner join. With this type of join, a query will only display records where there is a corresponding entry in both tables. For example, a query containing fields from linked Vehicles and Repairs tables would only show records where data existed in both tables. Any vehicles without a repair job record (or repairs without a vehicle record) would not be displayed. This is an important point to remember. Sometimes a query based on linked tables will not display all the records you expect it to because of this.

Using the above example, if you wanted to display all vehicles in a query, whether or not they had any repair records, you would redefine the link as an outer join based on the Vehicle table.

A self join describes the situation where a table needs to be linked to itself. For example, in a Staff table, with Employee Number as the primary key, there may be a field showing who each employee's manager is, using the manager's employee number. This employee number will already be a record in the table (managers are also employees). To display the manager's name instead of their number in a query, a link to a copy of the Staff table will need to be set up, using Manager as the link field.

Actions:

1. Open the Transport database and open the Buses table. Note that there are 30 records then close the table.

2. Create a new query in Design View. This is a query to list buses and their recent servicing records.

3. Add the Buses and Servicing Records tables. If the tables are not linked by default, link them now by dragging the Registration field from one table to the other.

4. From the Buses table, place Registration, Capacity and Size on the grid.

5. From the Servicing Records table, place Date and Repair on the grid.

6. Run the query. 18 records for 16 buses are shown (some buses have 2 repairs). Only buses with associated repair records are shown. 14 buses are not shown at all.

continued over



Exercise 57 - ContinuedNote: Although the discrepancy is fairly obvious in this example there may be

times when it is not so obvious and mistakes can easily be made.

7. Switch back to Design View of the query, and double click the relationship line to display the Join Properties dialog box.

Note: Alternatively, right click on the relationship line and select Join Properties from the menu.

8. Read the text for the selected Join option 1. This is an Inner Join.

9. Look at the other Join options. What is required here is one of the Outer Joins, Option 2. Select this option and click OK.

Note: The link graphic changes to an arrow. The join type is changed for this query only.

10. Run the query again. Now all bus records are shown. Fields are left blank where there are no servicing details.

11. Save the query as Outer and close it. Close the database.

12. Open the College database and the Students table. There is an Introduced column, so that each student’s record can contain the ID of any other student who has introduced them.

13. Instead of just showing the ID of the introducing student, it is possible to create a query showing their name by linking back to the same table (a self join).

14. Create a new query in Design View. Add the Students table twice.

Note: The table itself is not duplicated, it is just referred to by two different names.

15. The second table is given a different name, Students_1. To change this, right click on the table title and select Properties from the shortcut menu.

continued over



Exercise 57 - Continued16. Change the Alias entry to Introduced By and press <Enter>. Close the

Property Sheet panel.

17. Drag the Introduced field from Students to the ID field in Introduced By to create the link. Because the link is between copies of the same table it is a Self Join.

18. From Students, add Forename, Surname and Address to the grid and from Introduced By, add Surname to the grid again.

19. To change the last field title so that it displays Sponsor instead of Surname, click anywhere in the last field in the query grid to select it, then click from the Show/Hide area of the Design tab.

20. Change the Caption property to Sponsor. Close the Property Sheet and run the query.

21. Notice the student name and not their ID appears in the Sponsor field. The query however only shows records that have an entry in this field. As well as being a Self Join the link is also an Inner Join.

22. Switch back to Design View and double click the relationship line to display the Join Properties dialog box.

23. Select Join option 2 and click OK and run the query again. Now all Student records are shown, with sponsor details where appropriate.

24. Save the query as Self and close it.

25. Close any open tables and close the database.



Exercise 58 - Revision: Relationships1. Open the Pet Shop database and open the Orders table. This shows the

orders received by the shop in one day. Note how many orders are present.

2. Close the table and open the Relationships window. Add the tables Credit, Customers, Orders and Pets.

3. Create a link between the Credit and Customers tables using the Customer Ref field. Enforce Referential Integrity. What is the Relationship Type?

4. Create a link between the Customers and Orders tables using the Customer Ref field. Do not enforce Referential Integrity. What is the Relationship Type?

5. Create a link between the Pets and Orders tables using the Animal Ref field. Do not enforce Referential Integrity. What is the Relationship Type?

6. Create a query to list Orders, also using data from the Customer and Pets tables. Add the tables Orders, Customers and Pets to the query.

7. Use the following fields: Order Number, Order Date, Animal Ref and Quantity from the Orders table: Animal from the Pets table: Name from the Customers table.

8. Run the query. Only six records are shown. Why is one missing?

9. Return to Design View and change the Join Type for the join between the Orders and Pets tables so that all records from the Order table are shown. What is the new type of join called?

10. Run the query to verify all orders are shown.

11. Save the query as Order Details and close it.


13. Close Access.



AnswersExercise 11

Step 4 OpenForm

Step 5 [Common Name]=[Forms]![Customer Orders]![Plant] or[Plant Index]![Common Name]=[Forms]![Customer Orders]![Plant]

Step 8 SetValue. Item=[Date Paid]. Expression= IIF([Paid],Date(),Null)

Step 14 SetValue, Expression= [Price]*0.8

Step 15 £4.40

Exercise 18Step 9 Command buttons can be seen, but not Date and Time

Step 10 Date and Time can be seen at the bottom of every page (the Command buttons are seen at the end of the last form)

Step 11 Page Footer, containing the Date and Time, only seen in Print Preview (or on printout)

Exercise 24Step 13 The space for the image is blank

Step 14 There is no default message

Step 15 This page is under construction

Exercise 28Step 9 The attachment should open in your web browser application, e.g.

Internet Explorer or Netscape Navigator.



Exercise 35Step 7 Because the table is linked to the one in the Wines_Backup

database, this address is also changed to 4 Steeply Drive Step 11 Table format can be amended in Creatures.be Step 12 Forms can be amended in Creatures Step 13 Database must be opened ExclusiveStep 14 The database can be opened without the password but no data can

be accessed

Exercise 46

Exercise 53

Exercise 58Step 3 One-To-One

Step 4 One-To-Many

Step 5 One-To-Many

Step 8 Because one Order record does not have a matching Pets record

Step 9 Outer Join (Type 3)



GlossaryAction Arguments Settings which control the operation of a macro action.

Attachment Any separate file which is transmitted as part of an email message. Also a Data Type in a table.

AutoNumber A data type that automatically enters a sequential number for each record that is added to a table.

Control Panel A form manually created to control navigation through a database system.

Control Wizard A feature that helps to automate the process of creating controls on a form or report. Can be switched off to allow manual control.

Event An operation that can trigger the running of a macro or other procedure. Clicking a button, updating a field, and opening a report are all examples of Events.

Hyperlink A link in a table or form that can be used to display the contents of another object such as an image, a document or a web site. Also a Data Type in a table.

Join Type Describes how records are selected when multiple linked tables are included in a query.

Linked Table A table where the data is not held in that database, but in some external location. This can be in another database or in a different application.

Navigation Form A form containing links to the other objects in the database.

Normalisation The structuring of a database so that it meets the theoretical rules defining an efficient relational database.

Option Group A group of option controls, only one of which can be selected at any time.

SendObject A macro action that can transmit any database object as an e-mail attachment.

SetValue A useful macro action that assigns specific values to a field.

Size Mode A property of an inserted image. Can be Clip, Stretch or Zoom.

Walls The vertical background area of a 3-D chart. Called Plot Area on a 2-D chart.



Index

ActionsClose 17

OpenForm 10

RunMenuCommand 15

EMailDatabaseObject 49

SetValue 18

Action Arguments 10

Analyser Performance 69

Table 66

AutoExec 23

Backgrounds 26

Backup/Restore Database 59

Charts 30

Conditional Formatting 33

Compact Database 55

Control Panel 86Apply Macros to 89, 90

Main Control Panel 87

Option Group 91

Overview 85

Revision 94

Sub Control Panels 88

Current Database 76

Embedded Image 26

Database Analysis 65Dependencies 71

Documenter 70

Performance Analyzer 69

Revision 72

Table Analyzer 66

Database Options and Utilities 73Key Press Controls 78

Options 74

Page Numbers for Reports 79

Revision 83

Database Tools 54Backup and Restore 59

Database Splitter 60

Linked Tables 57

Navigation Forms 80

Passwords 62, 63

Compact and Repair 55

Revision 64

Date and Time 34

Documenter 70

Events 12

Exporting Data 46Database Objects 51

Revision 53

Send Using Macro 49

Send To 47

Formatting Forms 25Backgrounds 26

Charts 30

Conditional Formatting 33

Date and Time 34

Headers and Footers 32

Pictures 28

Revision 35

Headers and Footers 32

Hyperlinks 41, 43

Images 28

Image Hyperlink 43

IF Macro Command 91

IIF Expression 20

InsertChart 30

Date and Time 34

Image 28


Open Learning Guide Access Advanced

Joins 101

Key Press Controls 78

Linked Tables 57

Macros Actions 8

Applying to Control Panel 89, 91

Attaching to Controls 12

AutoExec 23

Creating from Controls 15

Create New 10

Deleting 23

Key Press Controls 78

Multiple Action Macros 17

Revision 24

Open Form 10

Set Value 18

Update Fields Automatically 20

Where Condition 14

Multiple Action Macro 17

Navigation Forms 80

On Click Event 12

OpenForm Action 10

Option GroupCreating 91

Page NumbersAdding to Forms 32

Adding to Reports 79

PasswordsRemove 63

Set 62

Performance Analyzer 69

Relationships and JoinsJoins 101

Many to Many 98

One to Many 96

One to One 100

Revision 104

Repair Database 55

ReportsAdding Page Numbers 79

RevisionAttachments and Links 45

Control Panel 94

Database Analysis 72

Database Tools 64

Database Utilities 83

Export Data 53

Formatting Forms 35

Macros 24

Relationships 104

RunMenuCommand 15

Security 62, 63

Send To 47

Set Value 18

Split Database 60

Startup Control 76

Table Analyzer 66

Update Automatically 20

Where Condition 14



Other Products from CiA TrainingCiA Training is a leading publishing company which has consistently delivered the highest quality products since 1985. Our experienced in-house publishing team has developed a wide range of flexible and easy to use self-teach resources for individual learners and corporate clients all over the world.

At the time of publication, we currently offer materials for:

• New CLAIT, CLAIT Plus and CLAIT Advanced

• ITQ Level 1, Level 2 and Level 3

• ECDL Syllabus 5.0

• ECDL Advanced Syllabus 2.0

• Start IT

• Skill for Life in ICT

• Functional Skills ICT

• CiA Revision Series

• Open Learning Guides

• Trainers Packs with iCourse Professional

• And many more…

Previous syllabus versions are also available upon request.

We hope you have enjoyed using this guide and would love to hear your opinions about our materials. To let us know how we're doing and to get up to the minute information on our current range of products, please visit us at:

www.ciatraining.co.uk
