Access Intermediate Notes 2007 by Sarah Mason

Intermediate Access Training Manual

Intermediate Database Using MicrosoftÂŽ Access 2007 Note: Microsoft is a registered trademark and Windows is a trademark of the Microsoft Corporation

~ Presented by Sarah Mason ~

ÂŠ Sarah Mason

Intermediate Access Training Manual TABLE OF CONTENTS Chapter 1 – Tables.................................................................................................................................. 5 1.1 ~ Deciding which data type ................................................................................... 5 1.2 ~ Specify a required field & default value in a table ............................................. 16 1.3 ~ Define a default field value in a table .................................................................. 17 1.4 ~ Alter the caption ...................................................................................................... 18 1.5 ~ Add a picture to a table ......................................................................................... 19 1.6 ~ Set a primary key for a table .................................................................................. 23 1.7 ~ Create a lookup field to a table ............................................................................ 25 1.8 ~ Creating a lookup from values you type in .......................................................... 31 1.9 ~ Define an input mask for a field ............................................................................. 34 1.10 ~ Filter by form............................................................................................................ 37 Chapter 2 – Relationships .................................................................................................................... 39 2.1 ~ Create and Modify a 1:Many relationship ........................................................... 40 2.2 ~ Display and use a subdatasheet ........................................................................... 44 Chapter 3 – Forms................................................................................................................................. 47 3.1 ~ Design a custom form.............................................................................................. 47 3.2 ~ The property sheet ................................................................................................... 49 3.3 ~ Add a header and footer to a form...................................................................... 51 3.4 ~ Filter by form .............................................................................................................. 54 3.5 ~ Select & move controls on forms and reports ...................................................... 55 3.6 ~ Change text line and colours of controls ............................................................. 56 3.7 ~ Use spacing and alignment controls..................................................................... 56 Chapter 4 – Queries ............................................................................................................................. 57 4.1 ~ Create a query to extract information from multiple tables.............................. 57 4.2 ~ Using logical operators in a query eg NOT AND OR ........................................... 58 4.3 ~ Create a query to generate summary information ............................................ 59 4.4 ~ Create a query to find unmatched records in a table ...................................... 61 4.5 ~ Create a query to find duplicate records in a table .......................................... 65 4.6 ~ Include a calculated field in a query using multiple tables ............................... 69 4.7 ~ Create a parameter query ..................................................................................... 73 4.8 ~ Create charts from a query .................................................................................... 74 4.9 ~ The Sections of a Chart ........................................................................................... 78 4.10 ~ Editing the values of a Chart ................................................................................ 79 4.11 ~ A Chart's Legend.................................................................................................... 79 4.12 ~ The Title of a Chart ................................................................................................. 80 4.13 ~ Chart Figures ........................................................................................................... 81 4.14 ~ Chart's Labels .......................................................................................................... 82 4.15 ~ The Chart's Background ........................................................................................ 83 Chapter 5 – Reports ............................................................................................................................. 84 5.1 ~ The Sections of a Report ......................................................................................... 84 5.2 ~ Creating a Report in Design View ......................................................................... 90 © Sarah Mason

Intermediate Access Training Manual 5.3 ~ Create a report of information in multiple tables ............................................... 92 5.4 ~ Add a calculated control to a report ................................................................... 93 5.5 ~ Address Labels .......................................................................................................... 98 Chapter 6 – Manipulating the database ........................................................................................ 101 6.1 ~ View object dependencies .................................................................................. 101 Chapter7 – Working with Other Applications ................................................................................. 102 7.1 ~ Export an access datasheet to Microsoft Office Excel .................................... 102 7.2 ~ Export an access datasheet to a text file .......................................................... 104 7.2 ~ Using access for a mailmerge .............................................................................. 106 Chapter 8 – Sharepoint Services( Data Access Pages) ................................................................ 109 8.1 ~ Sharepoint services ................................................................................................ 109 8.2 ~ View data as a pivot chart ................................................................................... 111

Intermediate Access Training Manual

Introduction These notes are designed to provide reinforcement material for tutor led training sessions in Microsoft Access. These revision exercises assume that the necessary program has been fully and correctly installed on your computer. However, in Access, some features are not installed initially and a prompt to insert the Office CD may appear when these features are accessed. Aim To provide the knowledge and techniques necessary to be competent at an advanced level. Objectives After completing the exercises the user will have experience in the following areas: Explore the new 2007 environment Tables – data types; required fields; pictures, default values; primary key; lookup fields, input mask wizard, filter by form Forms – custom forms, header and footers, filter by form, controls – move, resize, change colour, spacing and alignment controls Queries – summary information, duplicate records, calculated fields, unmatched records, parameter query, charts. Reports – calculated controls, labels Relationships – create one to many relationship, referential integrity, use a subdatasheet. Working with other applications – export access data to Excel, mail merge

Intermediate Access Training Manual Chapter 1 â&#x20AC;&#x201C; Tables 1.1 ~ Deciding which data type Procedure:

Determining which data type to use for a field is a very important step in designing a table. The available data types are Text, Memo, Number, Date/Time, Currency, AutoNumber, Yes/No, OLE Object, and Hyperlink. The following are some important considerations when deciding which data types to use. Storage requirements. For example, the Number data type can use 1, 2, 4, 8, or 16 bytes. The domain of the data. This refers to what can be stored in the column. For example, Text cannot be stored in a Number field. (nb a telephone number is text due to the spaces and brackets) Establishing relationships. Generally, it is more efficient to establish relationships between Number or Text columns. Sorting requirements. Sorting Text values may return unexpected results and OLE Objects cannot be sorted. Indexing requirements. Indexing Memo fields can create very large indexes and OLE Objects cannot be indexed. Calculation requirements. Certain calculations cannot be performed on fields defined as Text or OLE Objects. NOTE: OLE stands for object linking and embedding.

ÂŠ Sarah Mason

Intermediate Access Training Manual Exercise: Open Access 2007. Click on the Office Button displayed at the top left of your screen. Click on the Open command. This will display the Open dialog box. Open a database file called Contacts.accdb, contained within your sample files folder. Your screen will now look like this. Double click on tbl Contacts to open it. The table will be displayed in the main screen, as illustrated.

Click on the View button on the top to open the tbl Contacts in Design View.

ÂŠ Sarah Mason

Intermediate Access Training Manual Your screen will now look like this.

Create a new field called Person_Phone by entering this data into the last row of the column called Field Name (as illustrated below).

Select the desired data type from the Data Type drop down menu. For this field select Text data type (this may already be selected for you by default).

ÂŠ Sarah Mason

Intermediate Access Training Manual Within the Quick Access Toolbar (top-left of the screen) click on the Save button to save your changes.

Exercise: Modifying column data types You should be viewing the table in Design View (if not switch to Design View now). Click on the Data Type of the field that you wish to modify, in this case click on the Data Type for Person_Phone. Select a different data type from the Data Type drop down menu. For example, select Number data type.

Save the table by clicking on Office Button on the top left and then click on the Save command. Click the Yes button when a warning dialog box appears.

ÂŠ Sarah Mason

Intermediate Access Training Manual Exercise: Formatting Text data type Set the field size for Person_Name, using the Field Size text box, to 50. The Field Size box is displayed towards the bottom of the screen.

NOTE: Field size is the maximum length of characters that can be entered. So in our example the maximum number of characters that can be entered for the personâ&#x20AC;&#x2122;s name is 50. Enter an appropriate formatting symbol in the Format text box. For example enter the > symbol. NOTE: Entering the > symbol will force all entered characters to uppercase, and entering the < symbol will force all entered characters to lower case.

Within the Quick Access Toolbar click on the Save icon. Click the Yes button if a warning dialog box appears.

ÂŠ Sarah Mason

Intermediate Access Training Manual Exercise: Formatting Memo data type Click in the Memo field for Person_Address. Add an appropriate formatting symbol in the Format text box. For example, adding the @ symbol means that the text character (either a character or a space) is required. A semi colon followed by “” quote marks with text as shown below will mean that if nothing is entered the text “Not Specified” is entered.

Within the Quick Access Toolbar click on the Save button to save changes. Examples of formatting a text box or memo Value Format text box

Data entered

Data displayed

@@-@@@@

614235

61-4235

@@@@@@

614235 61-4235

614235 614235

Sarah SARAH

SARAH SARAH

Peter PETER

peter peter

@;”Not Specified”

NULL(nothing entered) Jim

Not Specified Jim

Intermediate Access Training Manual Exercise: Formatting Hyperlink data type Click within the Person_Email field as illustrated below.

Add an appropriate formatting symbol in the Format text box. For example, adding the & symbol means that the text character is not required.

Within the Quick Access Toolbar click on the Save button to save changes.

ÂŠ Sarah Mason

Intermediate Access Training Manual Exercise: Formatting Currency data type Click on the Person_Salary field as illustrated below.

The Data Type is marked as Currency. We will now format the currency. Select an appropriate format from the Format pull-down list.

Within the Quick Access Toolbar click on the Save button to save changes. NOTE: The currency format uses the thousand separator, and reflects the settings specified in Regional Settings in the Windows Control Panel for negative amounts, decimal & currency symbols.

ÂŠ Sarah Mason

Intermediate Access Training Manual Exercise: Formatting Date/Time data type Select an existing Date/Time field, in this case Person_DOB

You can also select an appropriate format from the Format drop down list.

Within the Quick Access Toolbar click on the Save button to save changes.

ÂŠ Sarah Mason

Intermediate Access Training Manual 1.3 ~ Converting between data types Sometimes it may be necessary to change the data type for a field. For example, it may be necessary to change from a Text field, which has a maximum length of 255 characters, to a Memo field that has a maximum length of 65,536 characters. NOTE: Changing between data types can result in a loss of data. This can happen when converting from a larger data type to a smaller data type, or converting between incompatible data types such as from Text to Number. Exercise: Changing a field to a different data type Select an existing field, for instance select Person_Name. Select the desired data type from the Data Type drop down menu, for instance change from Text to Memo data type.

Within the Quick Access Toolbar click on the Save button to save changes.

ÂŠ Sarah Mason

Intermediate Access Training Manual Exercise Open the database Antiquities and open the table artifact sales. Switch into Design View. Note that the Description field has been completed.

This description appears in Datasheet view at the bottom of the screen advising the data entry clerk of the contents of the field for clarity.

Switch back into Design View, click in the Field Name for Artifact. Set the field size to 30

Note: Take care not to reduce the field size to less than the largest possible data length for the field. Characters beyond the field size will be truncated. In some cases it may be necessary to increase the field size above the default value. Change the date format to Short Date. Change the price field to Fixed with no currency symbol Change the Quantity field to Long Integer. Click the Save button

ÂŠ Sarah Mason

Intermediate Access Training Manual 1.2 ~ Specify a required field & default value in a table Procedure You can ensure that a particular field always includes an entry by setting the required property of the field to Yes. You can also have a default entry displayed in a particular field of each new record by setting the Default Value property of the field to the desired entry. Exercise Open the contacts database and open the tbl contacts in Design View Select the Person_Name field. Click inside the Required field property. Choose Yes to make it as a required field. In the Person_Sex field, change the default setting the Male.

Within the Quick Access Toolbar click on the Save button to save changes.

ÂŠ Sarah Mason

Intermediate Access Training Manual 1.3 ~ Define a default field value in a table Procedure As discovered in the previous exercise a default value is one that appears in the database already before anything is typed. This is to speed up data entry. Exercise Using the Antiquities database, open the artifact table in design view Change the default value for the Date field to Date().

NOTE: Date() is a predefined function that gets today’s date and hence if we enter the Date() function in the Default Value field then it will enter today’s date automatically in the Date_Entered as a default value. Within the Quick Access Toolbar click on the Save button to save changes.

Intermediate Access Training Manual 1.4 ~ Alter the caption The caption shows how the field heading is displayed in datasheet view but behind the scenes in Design View you could use a shortened version of the name to save database space. Exercise: Open the Antiquities database and open the Artifact Sales in Design View Amend the caption for each of the fields to the following Person_ID

Person_name

Name

Person_address

Address

Person_dob

Date of Birth

Person_sex

Gender

Person_email

Company email

Person_salary

Annual Salary

Person_phone

Company telephone Number

ÂŠ Sarah Mason

Intermediate Access Training Manual 1.5 ~ Add a picture to a table Access provides two options for adding images to tables: You can embed the images directly or you can add links to the images. Both processes follow these broad steps: Add an OLE Object field to an existing table In the Antiquities Database window, select the table, and then click Design In the first blank row in Design view, under Field Name, type Image. Click the next field (the field in the Data Type column), click the arrow that appears, and then click OLE Object in the list.

Save the table. Switch to Datasheet view Import the information from the text file. From the External Data tab, choose the Import section and then Text File

ÂŠ Sarah Mason

Intermediate Access Training Manual Choose to Append the text file artifact sales

Choose Delimited

Ensure that you select the check box first row contains field headings

ÂŠ Sarah Mason

Intermediate Access Training Manual Complete the append.

Right-click the first field in the Image column of the table, and then click Insert Object on the shortcut menu. Select Create from File, and then click Browse.

Browse to the course folder and find one of the four .bmp files. Select the first image (image glass statue) and then click OK. To link to an image, click Link, and then click OK again to complete the process. To embed the image in the table, leave the Link check box blank and click OK. The words "Bitmap Image" appear in the first field of the Image column in the table. Note If "Package" appears in the OLE Object field, you're trying to link to or embed an unsupported graphics file, such as a GIF or a JPEG. If you need to display those types of files, you can reinstall Microsoft Photo Editor.

ÂŠ Sarah Mason

Intermediate Access Training Manual The picture once clicked on will open in the package that created itâ&#x20AC;Ś..

ÂŠ Sarah Mason

Intermediate Access Training Manual 1.6 ~ Set a primary key for a table Procedure

The Primary key is the field within a table which contains unique records and it must not be null or empty. The Composite key is a primary key which is the combination of more than one field. In other words itâ&#x20AC;&#x2122;s made up of two or more fields within a table. To make the Primary Key, select a field in the table and click on the Primary Key button on the top menu. To create a composite key, select two or more fields in the table and click on the Primary Key button on the top menu. Exercise Using the Antiquities database, return to design view Insert a new row at the top of the list of fields Name the new field Sales ID with data type Auto Number Select the Primary Key icon. Note the key symbol is displayed next to the field name.

Note: the new field has used autonumber to automatically number the sales. ÂŠ Sarah Mason

Intermediate Access Training Manual

ÂŠ Sarah Mason

Intermediate Access Training Manual 1.7 ~ Create a lookup field to a table Procedure You can add a field to a table to look up information in another table. You typically use this technique when you want to create relationships between tables. In this exercise it is used to speed up data entry by selecting options from a drop down list. This also minimises input error. Exercise

Using the database Contacts, switch to design view in the tbl contacts table. Create a new field named Department in the table ContactsTable1. Select the Lookup Wizard from the Data Type drop down menu.

This will open the Lookup Wizard dialog box.

ÂŠ Sarah Mason

Intermediate Access Training Manual In the Lookup Wizard dialog box select I want the lookup column to look up the values in a table or query.

Click on the Next button and you will see the following dialog box displayed. From within the View section (within the dialog box), select Tables option and select TableLookup from the list box above.

ÂŠ Sarah Mason

Intermediate Access Training Manual Click on the Next button and you will see the following displayed. From the Available Fields section, select a Field that you want to use for the lookup. For example select Dept.

Click on the > button to move the selected field into the Selected Fields section.

ÂŠ Sarah Mason

Intermediate Access Training Manual Click on the Next button and the next dialog box allows you to control the sort order of the selected field. In this example select the Dept field from the drop down list and select Ascending from the button.

Click on the Next button and the next dialog box will display the data from the selected lookup field.

ÂŠ Sarah Mason

Intermediate Access Training Manual Click on the Next button. In the next dialog box, type the label of your lookup column. For example type Department.

Click on the Finish button. You will be prompted to save the table. Click on the Yes button to save the table.

Open the table in Datasheet View. Click inside the newly created field Department. TIP: You may have to scroll to the right to see this. A pull-down list will appear and display the data from the lookup table. You can use this to insert the data.

ÂŠ Sarah Mason

Intermediate Access Training Manual

Import the data from the excel file. Append the Contacts data.csv file to the main table.

ÂŠ Sarah Mason

Intermediate Access Training Manual 1.8 ~ Creating a lookup from values you type in Click on the Design View button to open the Contacts Table in Design View (by clicking on the View button, as illustrated below).

Create a new field named Person_Height in the Contacts table. Select the Lookup Wizard from the Data Type drop down menu. This will display the Lookup Wizard dialog box. In the Lookup Wizard dialog box select I will type in the values that I want.

ÂŠ Sarah Mason

Intermediate Access Training Manual Click on the Next button. In the next dialog box enter the number of columns you want in the list. For example enter 1.

ď&#x201A;ˇ

Then enter values in the column as shown below.

ÂŠ Sarah Mason

Intermediate Access Training Manual Click on the Next button. In the next dialog box you can type in the label of your lookup column. In this example leave it as suggested, Person_Height and then click on the Finish button.

Open the table in Datasheet View. NOTE: You may see the following dialog box. If so click on the Yes button.

Click inside the newly created field Person_Height. A pull-down list will appear and display the data from the lookup table.

ÂŠ Sarah Mason

Intermediate Access Training Manual 1.9 ~ Define an input mask for a field Procedure An input mask is a field template that controls where data is to be entered as well as what kind of data and the number of characters that can be entered into a field on a datasheet (as well as into a text or combo box on a form). It defines the format of that data. This template consists of a series of literal characters (for example, brackets, full stops and/or hyphens), which separate input areas that are to be filled in by the user. You define an input mask by setting the Input Mask property of a field. You can do this by manually entering the definition characters (the literal characters mentioned above, also with special characters that determine the actual data that is allowed in the input area) or you can use the Input Mask Wizard to enter the definition characters Exercise Select a field with Text or Date/Time data type, as an input mask only works with Text or Date/Time data types. For example select Person_DOB. Click inside the Input Mask field property, then enter an input mask or click on the button to open the Input Mask Wizard.

If you clicked button, then the Input Mask Wizard dialog box will appear. Choose one of the masks from the list. For example short date.

ÂŠ Sarah Mason

Intermediate Access Training Manual

Type inside the Try It box to see how a selected input mask works.

Click on the Finish button to apply the mask.

ÂŠ Sarah Mason

Intermediate Access Training Manual

Save the table and test out the input mask.

ÂŠ Sarah Mason

Intermediate Access Training Manual 1.10 ~ Filter by form

Procedure The filter by form facility is accessed through the Advanced Filter in the Sort & Filter section. This allows you greater flexibility in your searching criteria yet still allow you to stay within the table

Exercise Staying in the contacts database, switch to Datasheet view. Choose the Advanced filter and then Filter by Form The datasheet appears blank, but it is ready for you to type in your search criteria. Here we have used wildcards to search for all addresses in the N17 postal district. Due to the way that the data is displayed, this is the only method of finding this data.

Choose Toggle Filter

ÂŠ Sarah Mason

Intermediate Access Training Manual

View the 42 records that have been filtered from the main data. Remove the filter by right clicking the address field and choosing Clear filter from address.

ÂŠ Sarah Mason

Intermediate Access Training Manual Chapter 2 – Relationships Issues relating to creating valid relationships When databases were first developed, they stored all information in "flat" text files. The database consisted of a single table of rows and columns (e.g. customer’s addresses in customer’s tables). If different databases contain the same information (e.g. customer’s addresses in customers tables and invoices tables), that information had to be entered separately into each database, thus creating redundancy and increasing data entry workload/errors. Now "relational databases," such as those used in Access, store all information in separate tables. Each table contains unique information (e.g. customers) that can be related to information stored in another table (e.g. invoices). Compared to the old "flat" databases, relational databases allow more efficient data entry, updates, and deletions, as well as data retrieval, summarization, and reporting. Relational database design creates relationships between fields in tables explicitly through common fields (keys). A specific field in a table is designated as a primary key for which each record must have a unique, non-null value. Once a record has been uniquely identified by its primary key, that record can be related to an infinite number of other records in an infinite number of other tables by appearing as foreign keys. Identifying a related table Tables that are related will have fields that are common between them. It is easier to identify relationships if the field names are the same. Related fields should have the same data type.

Intermediate Access Training Manual 2.1 ~ Create and Modify a 1:Many relationship Open the database called Company You will relate the employee list table to the Absences table. These tables share the common field EmpNo. When related, Employee List will be the table on the “one” side of the relationship since it contains unique values in the common field (the primary key of the table); Absences will be the table on the “many” side of the relationship since it contains duplicate values in the common field. In the Database tools tab, choose relationships from the Show/Hide section

Add the two tables to the relationships window

Intermediate Access Training Manual Drag the EMP NO from the employee List table to the EMP No on the Absences table. The Edit Relationships window will open. Choose Create.

A line now joins the two tables. Double click on the line to edit the relationship

ÂŠ Sarah Mason

Intermediate Access Training Manual Click on the Check box to enforce referential integrity

Note: Selecting the Enforce referential integrity option ensures that a record will not be entered into the related table unless a matching record exists in the primary table. Normally it also prevents you from either changing a primary key field in the primary table or deleting a record from the primary table when a matching record exists in the related table. You can, however override these restriction by selecting the Cascade Update Related field option (which updates corresponding entries in the related table when you change a primary key field entry in the primary table) and/or the Cascade Delete Related Records option (which deletes related records in the related table when you delete a record from the primary table).

ÂŠ Sarah Mason

Intermediate Access Training Manual Types of joins in a relationship There are three types of joins in Access such as one-to-one, one-to-many, and many-tomany. One-to-one A one-to-one relationship has only one matching row in each table. Such relationships are not common. An example would be a table with many columns divided into multiple tables that relate to one master key value. One-to-many A one-to-many relationship has a row in one table that matches multiple rows in the related child table. This kind of relationship is the most common of the three relationships. An example would be the relationship between the Employee and the Sale table, where an employee can sell many times. In other words each sale belongs to one employee and each employee is related to many sales. Many-to-many A many-to-many relationship has many rows in one table that matches many rows in a related table. This is a special relationship that involves a third table called a junction table. An example is the relationship between Customer and Products, where these two tables are joined by a junction table called Order Details. This relationship is described as many orders having many products and many products belonging to many orders.

ÂŠ Sarah Mason

Intermediate Access Training Manual 2.2 ~ Display and use a subdatasheet Procedure When you establish a one-to-many relationship between two tables, Access creates a subdatasheet in Table 1. This subdatasheet shows the associated information in Table 2 (the table on the many side of the relationship) for each record in Table 1. A new column is added to the left of the first field in Table q providing a means of displaying and hiding the subdatasheets. Exercise To display the subdatasheet for a single record Click on the expand indicator (which displays a plus sign) to the left of the appropriate record. To hide the subdatasheet for a single record Click again on the expand indicator (which displays a minus sign) To display the subdatasheet for all records: Choose the Records|More|Subdatasheet| Expand all command To hide the subdatasheet for all records Choose the Records|More|Subdatasheet|Collapse All command

ÂŠ Sarah Mason

Intermediate Access Training Manual Exercise: Open the employee list table in the Company database Click on the expand indicator which displays a plus sign to the left of record 1

Click again on the expand indicator which now displays a minus sign to the left of the record. To show all the subdatasheet information at once, choose Records, More, Subdatasheet, Expand All

ÂŠ Sarah Mason

Intermediate Access Training Manual Exercise Open the database file named Sales Info3 Create relationships that enforces referential integrity between the following tables: Customers and Orders Merchandise and Orders

Display the subdatasheet for records in the customers table Display the subdatasheet for records in the Merchandise table. Using the subdatasheet, change the quantity field entry for order number 1001 (for item A1) to 8

Close the database file.

ÂŠ Sarah Mason

Intermediate Access Training Manual Chapter 3 â&#x20AC;&#x201C; Forms 3.1 ~ Design a custom form In the previous introductory course, you created a form using a wizard. This section deals with creating a form from the design view. Exercise: Using the Contacts database select create from the ribbon and then Form Design

This will open a blank form.

Click on the Add existing Fields from the Tools section on the Ribbon.

ÂŠ Sarah Mason

Intermediate Access Training Manual Expand the tbl contacts table to show all the fields Drag and drop the required fields onto the form.

These fields are referred to as Bound Controls as they are linked to a table in the database

ÂŠ Sarah Mason

Intermediate Access Training Manual 3.2 ~ The property sheet The form, each section of the form (header, detail, footer), and each control on the form have a list of properties associated with them, and you set these properties using a property sheet. Each control on a form, each section on a form, and the form itself are all objects. The kinds of properties you can specify vary depending on the object. To open the property sheet for an object, select the object and then click the Property Sheet button in the Tools group on the Design tab. Exercise: Select the Forms Property sheet

The property sheet window for the form will appear Disable the Max and Min buttons

ÂŠ Sarah Mason

Intermediate Access Training Manual

Test out these changes by switching into Datasheet view

ÂŠ Sarah Mason

Intermediate Access Training Manual 3.3 ~ Add a header and footer to a form Procedure The form header/footer display in different places to the page header/footer: The form header prints on the first page only above everything else. The form footer prints on the last page only, below the last record, but not necessarily at the bottom of the page. The page header will print on each page above the record(s). On the first page it is below the form header. The page footer prints on each page at the bottom of the page. Exercise Switch back into Design View of your form Show the Form Header by selecting the Arrange tab, then choose the Show/Hide section from the ribbon and then choose Form Header/Footer

From the Design tab, choose the Controls section on the ribbon and then Aa label icon. This will create an unbound control or a box that is not connected to a table or query

ÂŠ Sarah Mason

Intermediate Access Training Manual Click and drag a text box into the page header section and enter the text:

Make ‘embellishments’ as desired! In the form footer section add the date by clicking on the Date option in the Controls section:

Add page numbers to the page footer section:

Intermediate Access Training Manual Exercise: Open the database file names Sales Info 4 Create a custom form for entering data into the Orders Table. Include the following fields: OrderNo; OrderDate: CustomerID; Quantity; and Price fields. Also add the Item field from the Merchandise table

Add a header to the form to display the form title (order) Add a footer to the form to display your name Save the changes and check the data in datasheet view.

ÂŠ Sarah Mason

Intermediate Access Training Manual 3.4 ~ Filter by form Procedure The filter by form features works in a similar way to that in tables. Choose the Advanced option in the Sort & Filter area on the ribbon and then choose Filter by Form The view appear blank ready for you to type in your criteria Choose toggle filter to view the data Exercise In the Contacts database, open the frm contacts form.

Type in the criteria >1/1/1990 to search for all records with a date of birth greater than 1st January 1990 Click the toggle filter option and you should see that there are 46 records.

ÂŠ Sarah Mason

Intermediate Access Training Manual 3.5 ~ Select & move controls on forms and reports The label control and its related detail control are grouped together. Therefore if you select one of them and try to move them, they will move as a pair.

If you move the cursor over the top left hand corner of the control, it will move the control independently from its label.

If you move the mouse over the handles, you can resize the boxes

ÂŠ Sarah Mason

Intermediate Access Training Manual 3.6 ~ Change text line and colours of controls The line colours and thickness controls are all to be found in the Controls section of the ribbon.

Experiment with the colours to see the effect

3.7 ~ Use spacing and alignment controls There are several tools to help you align your control boxes. They can all be found in the Arrange tab. Experiment with the tools

ÂŠ Sarah Mason

Intermediate Access Training Manual Chapter 4 â&#x20AC;&#x201C; Queries 4.1 ~ Create a query to extract information from multiple tables A query can extract and display information not only from a single table, but from multiple tables as well assuming those tables contain related information. Exercise: To create a query to extract information form multiple tables Open the database Company 4 and create a new query in design view Add the Absences and the Employee tables to the grid From the Absences table select the Ref No and Emp No From the Employee table, select the Last Name and Department fields From the Absences table select the Away, Return and Reason fields. Sort the data into ascending order of Ref No Experiment with the formatting settings in queries

ÂŠ Sarah Mason

Intermediate Access Training Manual 4.2 ~ Using logical operators in a query eg NOT AND OR

Procedure: Originally, logical operators were a concept from algebra used to exclude and include number sets. They are, very simply, NOT, AND, and OR. Sometimes this set is expanded to combinations of the operators, but at core it's only these three. The logical operators are often referred to as the Boolean operators. NOT refers to the logical opposite of your statement. AND joins two items together under a single command. OR makes a command operational if either statement it joins is true. Exercise Using the query just created search for those employees who have been off sick but not with a cold or flu

ÂŠ Sarah Mason

Intermediate Access Training Manual 4.3 ~ Create a query to generate summary information Procedure Although queries are generally used to extract specific entries from a table, they can also be used to generate summary information for records in a table. The following aggregate functions can be applied to records as a whole or to records grouped by entries in a particular field. Function

Computes

Sum

The total of values in a field

Avg

The average of values in a field

Min

The smallest value in a field

Max

The largest value in a field

Count

The number of values in a field

StDev

The standard deviation of values in a field

Var

The variance of values in a field

Exercise Create a new query using the Company 4 database. Insert the fields Dept, LastName, and Salary from the Employee List table Select the TOTALs option from the Show/Hide section of the ribbon

A total row is added to the window. Notice that the default entry for each Total field is Group By. Since records, in this case will be grouped by entries in the Dept field, you will leave the Total entry for that field as is.

ÂŠ Sarah Mason

Intermediate Access Training Manual Click in the LastName column and choose Count for the Total row. Click on the Salary column and choose Sum for the total row.

Run the query.

Save as Department Summary

ÂŠ Sarah Mason

Intermediate Access Training Manual 4.4 ~ Create a query to find unmatched records in a table Procedure You may wish as time to find records in one table that do not have a matching record in a related table of the same database. This can be accomplished with the Find Unmatched Query Wizard. Exercise

Staying with the Company 4 database, click on the Create tab and within the Other group click on the Query Wizard button. In the New Query dialog box select the Find Unmatched Query Wizard and click on the OK button.

ÂŠ Sarah Mason

Intermediate Access Training Manual In the Find Unmatched Query Wizard dialog box select the Employee List table

Click on the Next button. In the next dialog box it asks for the table or query that contains related records. Select the table or query, i.e. select the Absences table.

ÂŠ Sarah Mason

Intermediate Access Training Manual Click on the Next button. In the next dialog box select the matching fields from both tables, i.e. select the EmpNo.

NOTE: If there is only one matching field then Access will show it automatically. Click on the Next button. In the next dialog box select the other fields that you want to include i.e. select all fields by clicking on the >> button.

ÂŠ Sarah Mason

Intermediate Access Training Manual In the next dialog box enter a name for this query i.e. type Employees with no absences and choose the View the results option.

Click on the Finish button. The resulting query will display all the unmatched records.

Close the Query Design window.

ÂŠ Sarah Mason

Intermediate Access Training Manual 4.5 ~ Create a query to find duplicate records in a table Procedure: You may sometimes need to find records in a table that contain duplicate information. This can be accomplished with the Find Duplicates Query Wizard. Exercise:

Click on the Create tab and within the Other group click on the Query Wizard button. In the New Query dialog box select the Find Duplicates Query Wizard.

Click on the OK button. In the Find Duplicates Query Wizard dialog box select a table, i.e. select the Absences table.

ÂŠ Sarah Mason

Intermediate Access Training Manual

Click on the Next button. In the next dialog box that asks for a field to find duplicates, select a field, i.e. select EmpNo, Away, returned and click on the > button.

ÂŠ Sarah Mason

Intermediate Access Training Manual Click on the Next button. In the next dialog box it asks for other fields to show in the query. Select fields, i.e. select all fields to show by clicking >> button and then click on the Next button.

In the next dialog box enter a name for this query i.e. Find duplicates for Absences and click on the Finish button. The resulting query will show all the duplicates for the ProductName field if there are any duplicates.

Close the Query Design window.

ÂŠ Sarah Mason

Intermediate Access Training Manual Run the query and view the results:

ÂŠ Sarah Mason

Intermediate Access Training Manual 4.6 ~ Include a calculated field in a query using multiple tables Procedure: Another feature available in queries is the ability to create calculated fields. These are fields that do not exist in the original table but can be calculated using existing fields. For example if Price and Quantity fields exist, then a Value field could be calculated; if Hours Worked and Hourly Rate fields exist, then a Pay field may be calculated; if SalesValue and Cost exist, then Profit could be calculated. For a single field, a percentage could be applied to a Price field to calculate Discounted Price. The calculation is defined with an expression. The expression may involve one or more fields and one or more mathematical symbols. The standard mathematical symbols available are Add +, Subtract -, Multiply * and Divide /. Once defined, a calculated field may be used in a query like any other field; it may be sorted on, used in selection or used in a further calculation. The name of a calculated field is entered in the Field row of the query grid followed by a colon (:). The expression is then entered with any field names enclosed in square brackets. Exercise: Open the Wineshop database. Create a new query in Design View based on the Sales table. Place the Date, Product Ref, Price, Quantity and Paid fields into the query grid. Click anywhere in the Paid column and select Insert | Columns to place a new blank column between Quantity and Paid. In the Field row of this new column enter the following expression: Sales Value: [Price]*[Quantity] Note: The name of the new calculated field is Sales Value and it is calculated by multiplying two existing fields Price and Quantity, which are enclosed in square brackets to indicate that they are field names. The symbol * represents multiplication and is called a mathematical operator. Make sure the Show box is checked or the field will not be displayed. ÂŠ Sarah Mason

Intermediate Access Training Manual

Note: The width of the Sales Value column in the Query grid may be increased if required to see the whole expression. This does not affect the column widths when the query is run Run the query. The new field is displayed but is not correctly formatted, as it should appear with £ signs. Return to Design View and click anywhere in the new Sales Value column. Click the Properties button,

, to display the Field Properties dialog box.

Select Currency from the Format drop down list.

Close the dialog box using the Close button, and run the query again. Sales Value is now correctly formatted.

In Design View click in the Sort row for the new Sales Value column. Display the drop down list in the Sort field and select Descending. Run the query. Records are sorted in descending order by the calculated field, Sales Value. Return to Design View and enter >200 in the Criteria row for the Sales Value column. Run the query. Only records with a sales value greater than £200 are displayed. Save the query as Big Sales. Close the query and the Wineshop database.

Intermediate Access Training Manual Exercise : Use the Properties database and Commercial table and create a new query in Design View. Place the fields Town/City, Address, Type of Premises, Price and Disabled Access on to the query grid. Using wildcard characters, search for all the properties that have the word Exhibition in the Type of Premises field. Increase the scope of the search to also include all properties with the word Conference in the Type of Premises field.

Run the query. Save the query as Venue. In Design View, remove the criteria from the query, then use it to display all properties with a price between £75000 and £95000 inclusive.

Save the query (using Save As) as Mid Range and close it. Create a new query on the same table and include the fields Town/City, Address, Type of Premises, Price, Unit Area and Offers.

Intermediate Access Training Manual Create a new calculated field in the query, after the Unit Area field. Name the new field Rate and define the calculation as Price divided by Unit Area. Format the new field as Currency. Display the query in descending order of Rate. Save the query as Price per Square Metre and close it.

Close the database.

ÂŠ Sarah Mason

Intermediate Access Training Manual 4.7 ~ Create a parameter query Procedure: A parameter query is one that prompts the user for selection criteria when it is run. A prompt displayed by a parameter query appears in a box that is defined when the query is first set up. Exercise: To create a parameter query: Open the Training Data 4 database Create a new Query displaying the EmpNo, LastName and Dept fields. From the Employees Table and CourseID, Start and End fields from the Schedule table. Under the start field type in the criteria row: Between [Enter the first date:] And [Enter the last date:] Click on Run Entering the above into the Criteria row for a Date field would display two prompt boxes – the first prompting the user for the first date in a date range and the second prompting the user for the last date in the range. The query would then display those records in which the field contained an entry within the specified date range. Type in 1/7/04 for the first prompt box and 31/8/04 for the second Note: you could also use: o

Like [Enter the first character(s) you wish to find:] & “*”

Entering the above into the Criteria row for a Text field would display a single prompt box, prompting the user for one or more characters. The query would then display those records in which the field contained an entry beginning with the specified character(s).

Intermediate Access Training Manual 4.8 ~ Create charts from a query

Procedure A chart is a technique of displaying data using pictures and graphical representations instead of numbers or simple words. It works by drawing figures that would represent numbers, adding colours and shapes to the information presented. Good created and formatted charts can help people and businesses make decisions based on the impact that the images provide. While data analysis as we have seen so far was performed on records displayed on datasheets or forms, data analysis on charts is done using graphics that present pictures. In addition to the pictures, you can add words, also called labels to indicate what the pictures represent. Exercise

Using the Properties database, create a query to show the exhibition hall prices.

Save and close

ÂŠ Sarah Mason

Intermediate Access Training Manual To start a chart, display a new blank form in Design View and, in the Controls section of the Ribbon, click the Insert Chart button , and position the mouse on the form or report. The mouse cursor would appear with small bars:

You can then click the form. You may receive a Microsoft Office Access Security Notice. In the first page of the Chart Wizard dialog box, make sure Average price per unit is selected.

In the second page of the Chart Wizard, in the Available Fields list, double-click Type of Premises and Price. Click Next

ÂŠ Sarah Mason

Intermediate Access Training Manual In the third page of the Chart Wizard, accept the Column Chart in the 1st column - 1st row and click Next

Display the fields as shown below:

ÂŠ Sarah Mason

Intermediate Access Training Manual Click Next and accept the name and then finish.

Save the form and switch into Datasheet view

ÂŠ Sarah Mason

Intermediate Access Training Manual 4.9 ~ The Sections of a Chart Most or every one of these aspects can be hidden, displayed or changed. To perform any action on these parts, after displaying the form or report that holds the chart in Design View, you use Microsoft Graph. To open it: You can right-click the chart, position the mouse on Chart Object and click Open You can double-click the chart This would open a separate application. In Microsoft Graph, you can click or right-click the desired part.

ÂŠ Sarah Mason

Intermediate Access Training Manual 4.10 ~ Editing the values of a Chart To change the values used for a chart without changing the real values, you can open Microsoft Graph. It would present a spreadsheet:

To change the value, in the Datasheet, click the cell that holds the value and type the desired one. After editing the value(s), click the body of the form or report to return to Microsoft Access. The chart would display with the new values. Remember that if you close the form or report, the chart would lose those temporary values. 4.11 ~ A Chart's Legend To show what the graphics on a chart represent, a chart is accompanied by an object on a side called a legend or key.

ÂŠ Sarah Mason

Intermediate Access Training Manual To make changes to the legend, display the Microsoft Graph for the chart, right-click the legend and click Format Legend. This would open the Format Legend dialog box, make the changes, and click OK.

If you do not want to use a legend, you can delete it. To do this, right-click the legend and click Clear or click the legend and press Delete.

4.12 ~ The Title of a Chart To indicate what it is used for, a chart can be equipped with a title. The title is a string that typically displays in the top section of a chart. A title can also be positioned on the left or the right sides, above or below the chart. To move the title, display the form or report in Design View, click and drag the chart in the desired direction. To format the title, you can either double-click it or right-click it and click Format Chart Title. By default, the chart displays without a border, in bold Calibri font. You can change or format it using the Format Chart Title dialog box.

ÂŠ Sarah Mason

Intermediate Access Training Manual 4.13 ~ Chart Figures To represent its numbers, a chart draws some geometric figures, depending on the type of chart. These figures can be rectangles, pie slices, triangles, cones, etc. To paint these figures, by default, the chart engine uses some randomly selected colours from its own list. You can either change these colours or apply some preset drawings available. You can also design and use any custom picture to paint the chart's shapes. To format the geometric figures of a chart, you can right-click one of them and click Format Data Series.

ÂŠ Sarah Mason

Intermediate Access Training Manual 4.14 ~ Chart's Labels By default, when a chart is drawn, it is equipped with shapes and a separate legend. If you want, you can display the value of each part and possibly its name close to it. This is done through a label. On a large chart, a label can also be used in the absence of a legend. In fact, you can delete a legend and simply make use of a label. To add the labels to a chart, right-click a box on the chart and click Format Data Series. Once in the Format Data Series dialog box, click the Data Labels tab:

ÂŠ Sarah Mason

Intermediate Access Training Manual 4.15 ~ The Chart's Background One more way you can enhance the appearance of a chart is to draw a background wall behind it. The wall is just a graphical object. By default, the background of a chart is painted in white. You can use a different colour to paint it, a design pattern or a picture to cover it. Before formatting the chart, open Microsoft Graph. To format its wall, right-click the chart and click Format Chart Area... This would open the Format Chart dialog box where you can make the necessary changes. Right-click an area of the chart -> Chart Object -> Open Right-click the chart and click Format Chart Area... In the Format Chart Area dialog box, in the Area section, click Fill Effects

ÂŠ Sarah Mason

Intermediate Access Training Manual Chapter 5 â&#x20AC;&#x201C; Reports 5.1 ~ The Sections of a Report

The Page Header and the Page Footer Sections

When a piece of paper prints, it is made of a top section, a body, and a bottom section. To support this, a report can be equipped with a Page Header that represents the top part, a Detail section that represents the body of the report, and a Page Footer section that represents the bottom part. If you create a report using either the Blank Report or the Report Design options of the Reports section of the Ribbon, the report would be equipped with a Page Header and a Page Footer sections:

ÂŠ Sarah Mason

Intermediate Access Training Manual If you have a report that does not have these sections, you can add them. To add these sections, you can: Right-click the report and click Page Header/Footer On the ribbon, click Arrange. In the Show/Hide section, click Page Header/Footer

As mentioned previously, the Page Header represents the top section of the printed paper. Therefore, when designing a report, put in the Page Header the objects you want to display on each top part of the printed paper. For example, you can put the common title or the page number in that section. That section is also typically used to display the title of a brochure or book. Because the Page Footer represents the bottom part of each printed page, you can put on it the object(s) that would display on each page. For example, you can use it to display the date the report is being printed. The Detail Section

Like a form, to show the sections of a report, it must be opened in Design View. Like a form, the most fundamental part of a report is the Detail section. The detail section holds the most controls of a report. In fact, a report can have only that section. If you create a report using one of the options from the Reports section of the Create tab of the ribbon, the report would come equipped with various sections. To have only the Detail section, you can right-click the report and click the option of those sections to remove them. You may end up with only the Detail section:

ÂŠ Sarah Mason

Intermediate Access Training Manual You can then equip it with the desired controls. Here is an example:

The Report Header and the Report Footer The report is the primary object used to print the data of a database. To support a front cover and the back page, the report can be equipped with two other sections: The Report Header and the Report Footer sections:

ÂŠ Sarah Mason

Intermediate Access Training Manual If you create a report using either the Report or the Report Wizard options of the Reports section of the Ribbon, the report would be equipped with a Report Header and a Report Footer sections. If you have a report that does not have these sections, you can add them. To add these sections, you can: Right-click the report and click Report Header/Footer On the ribbon, click Arrange. In the Show/Hide section, click Report Header/Footer

As seen in the above screenshot, you can have a report that has a Report Header and a Report Footer sections without the Page Header and the Page Footer sections. If you have a Page Header and the Page Footer sections but do not want to show them on a printed paper, you can completely reduce their heights:

ÂŠ Sarah Mason

Intermediate Access Training Manual When to Display a Section

By default, after adding a section to a report, the section would show in Design View and its contents would appear in the other views. If you want, you can hide the section in either the Design View or the other view. This characteristic is controlled by the Display When enumerated property. To apply it, display the report in Design View and access the Properties window of the section on which you want to control this characteristic. The Display When property has three options: Always: This is the default value of the property. It indicates that the section will display in Design View and its contents would appear in all views Print Only: The section will appear in Design View and Print Preview only, not in Report View or Layout View

ÂŠ Sarah Mason

Intermediate Access Training Manual Report View

Print Preview

Screen Only: The section will appear in Design View, in Report View, and in Layout View, but not in Print Preview

ÂŠ Sarah Mason

Intermediate Access Training Manual 5.2 ~ Creating a Report in Design View Procedure: A Report may be created by selecting fields from tables and/or queries and placing them on to the report in Design View. Exercise: Open the Authors database, click Reports in the Create tab and click Report Design

. The field list should open automatically together with a blank report

ÂŠ Sarah Mason

Intermediate Access Training Manual Click OK to display the report in Design View and maximise if necessary.

Click the Field List button, again to redisplay it.

The Field List is removed from view. Click the button

Double click the Surname field and drag it to the Detail area in the middle of the report. Position the icon at the top centre of the detail area.

Field

Label

Double click the First Name field in a similar way and place it beneath Surname. Click Title from the Field List. Hold down <Shift> and click Literature Drag these two fields on to the grid below First Name. The icon will look like this.

Click the View button, . This is so far a very plain report. Click Design View, again to return to the design. From the Controls click the Label tool, report.

. The cursor changes to a

when over the

In the Page Header area, click and drag a label box to create an area for the title. The cursor should be in the top left of the box, type in Authors. Preview again. The report is still not very impressive, but is ready for formatting. Save the report as rpt Authors. Close the database.

ÂŠ Sarah Mason

Intermediate Access Training Manual 5.3 ~ Create a report of information in multiple tables Procedure In the same as we produced a report from one table, you can pull in fields from any number of tables Exercise Open the Company 4 database and set up a report by choosing Report Design Choose the field shown below from the two tables

Save and close the database

ÂŠ Sarah Mason

Intermediate Access Training Manual 5.4 ~ Add a calculated control to a report Procedure There are a large number of functions that can be used in calculated fields. Functions in Access can be divided into groups. Common groups are Date/Time, Financial, Math, and Text functions. Some common functions within each group include. Date/Time Financial Math Text

Date, DatePart, Day, Hour, Minute. FV, PV, IRR, Pmt. Exp, Rnd, Sqr. LTrim, RTrim, Replace, Len.

Functions will only work correctly when supplied with the correct number of arguments. A tool included within Access, called the Expression Builder, simplifies using functions in calculated fields. Exercise: Using count and sum calculations within a report Click on the Create tab and within the Reports group click on the Report Design button. This will open a blank report. In the Record Source field of the Property Sheet under the Data tab, select a table, i.e. select Products.

Within the Tools group, click on the Add Existing Fields button.

Drag the required fields into the Detail section of the form, i.e. drag ProductID, UnitPrice. Within the Controls group, click on the Text Box button. ÂŠ Sarah Mason

Intermediate Access Training Manual Click on the Report Footer section to place the selected Text box. NOTE: If the Report Footer is not visible then right click anywhere inside the Detail section and select the Report Footer/Header command.

In the Property Sheet, enter a Control Source under the Data tab, i.e. type for this Text box: =Count([ProductID])

Change the text to the relevant label to: Total number of products The screen will now look like this:

ÂŠ Sarah Mason

Intermediate Access Training Manual Within the Controls group, click on the Text Box button. Place the selected text box in the Design section. Bring across the UnitsInStock control box and then create a calculated control as shown below

Add a third calculated control to calculate the total stock value. This will need to be placed in the Report Footer and therefore will appear at the end of the report

ÂŠ Sarah Mason

Intermediate Access Training Manual Exercise: Using sum min, max and average calculations within a report Within the Controls group, click on the Text Box button again. Place the selected Text box in the Report Footer section. NOTE: If the property sheet is not displayed down the right side, click on the Property Sheet button, contained in the Tools group under the Design tab. In the Property Sheet, enter a Control Source under the Data tab, i.e. type for this Text Box: =Min([UnitPrice]) Change the text to the relevant label to: Minimum Price Within the Controls group, click on the Text Box button again. Place the selected Text box in the Report Footer section. In the Property Sheet, enter a Control Source under the Data tab, i.e. type for this Text Box =Max([UnitPrice]) Change the text to the relevant label to: maximum Price Place another Text Box in the Report Footer section. In the Property Sheet, enter a Control Source under the Data tab, i.e. type for this Text Box =Avg([UnitPrice]) Change the text to the relevant label to: Average Price

ÂŠ Sarah Mason

Intermediate Access Training Manual

ÂŠ Sarah Mason

Intermediate Access Training Manual 5.5 ~ Address Labels Procedure Exercise

Open the Tourist database Next, go to Create>Reports>Labels, and the Label Wizard will open up.

The “Label Wizard” pop up window box will appear. Next, from the group of label options, select the labels that you will be using for the printing of the labels, and also search the correct label manufacturer, and click Next.

Intermediate Access Training Manual Next, you can select the font type, size, and color for your labels, and click Next.

In this following section, you need to pick the fields that you want to appear on the labels. So, under available fields, click on the ones that you want to transfer to the “Prototype label” section, and click Next.

In this step, you need to tell Access how to sort the data to organize it for the labels. Review the options under “Available Fields” and click the field or fields that best organizes the information and these selections will appear under “Sort by.” And click Next.

Intermediate Access Training Manual

Now, give your file a name and click “See the labels as they will look printed” option to see a preview and click Finish.

Finally, you will see a preview of the mailing labels and you will be ready to print them or save the file for future use.

100

Intermediate Access Training Manual Chapter 6 â&#x20AC;&#x201C; Manipulating the database 6.1 ~ View object dependencies The object dependencies task pane can be used to determine the dependencies that exist between various objects in a database. If you need to delete a particular object from a database you should make sure that another object depend on the subject to be removed To display the object dependencies task pane: From the tabs, select the Database Tools and the choose Object dependencies

You can see from the pane that has opened that in this instance in the Tourist database, the labels report depends on the Attractions table

ÂŠ Sarah Mason

101

Intermediate Access Training Manual Chapter7 â&#x20AC;&#x201C; Working with Other Applications 7.1 ~ Export an access datasheet to Microsoft Office Excel Exercise Select the Attractions table in the Tourist database. Click on the External Data tab and within the Export group click on the Excel button.

You will see the following dialog box displayed.

Click on the Browse button. Select the folder containing your sample files. ÂŠ Sarah Mason

102

Intermediate Access Training Manual Rename the file to be called Attractions. Click on the OK button again. You will see the following.

Click on the Close button.

ÂŠ Sarah Mason

103

Intermediate Access Training Manual 7.2 ~ Export an access datasheet to a text file Exercise: Select the Attractions table from the Tourist Database. Click on the External Data tab and within the Export group click on the Text File button.

You will see the following dialog box displayed.

Click on the Browse button. Select the folder containing your sample files. Rename the file to be called Attractions Text File.txt

ÂŠ Sarah Mason

104

Intermediate Access Training Manual Click on the OK button again. You will see the following. Choose Windows and click OK.

Click on the Finish button. You will see the following.

ÂŠ Sarah Mason

105

Intermediate Access Training Manual 7.2 ~ Using access for a mailmerge Procedure Mail merging allows you to use data on your database to create letters, labels, envelopes, and other documents that require external data originating from another document. When performing a mail merge, you usually do not need all the fields that are part of a table. Although you can use all fields on a table, you can create a query made only of fields you need for your document. This would include the names and addresses of the recipients. Exercise To start a mail merge, in the Navigation Pane: Click the table that holds the information you want to use. On the Ribbon, click External Data. In the Export section, click More -> Merge it with Microsoft Office Word Right-click the table, position the mouse on Export and click Merge it with Microsoft Office Word This would open the Microsoft Word Mail Merge Wizard. If you want to create a new document, click the second radio button:

ÂŠ Sarah Mason

106

Intermediate Access Training Manual And click OK. Microsoft Word would open. In the Mail Merge window, if you want to create a letter to be sent out, accept the Letters radio button

Otherwise, you can click another radio button for the type of document you want to create. Then click the Next link. In the second page of the wizard, you can specify whether to continue with the currently opened document or you want to use a template. For our example, you would accept to use the current document and click the Next link. The third page of the wizard wants you to specify the list that holds the fields that will be used. If you want to create a new list, you can click the Type A New List radio button. Otherwise, to use the table you selected in Microsoft Access, accept the first radio button and make sure it is specified in the Use An Existing List section.

Once the list is ready, click the Next link. You would then have to create the document. You can insert the merge fields from the drop down list at the top of the page.

ÂŠ Sarah Mason

107

Intermediate Access Training Manual

After creating the document and adding the necessary fields to it, you can preview and review it. To do this, in the Mail Merge window, click the Next: Preview Your Letters link. When you do this, the letter appears with the value(s) of the first record. To review the document with the other values, in the Mail Merge window, you can click the previous

or the next

buttons.

After reviewing the document, in the Mail Merge window, you can click the Next: Complete The Merge link. You can then save, print, and manage the document. In the same way, you can create labels or envelopes.

ÂŠ Sarah Mason

108

Intermediate Access Training Manual Chapter 8 – Sharepoint Services( Data Access Pages) 8.1 ~ Sharepoint services Access 2007 provides new collaboration features that offer more flexibility and security than data access pages. These features include Microsoft Windows SharePoint Services and the use of e-mail messages to collect data. Microsoft Windows SharePoint Services Microsoft Windows SharePoint Services is a feature of Windows Server that lets you store data and deploy database forms and reports over the Web. You can link Access 2007 to Windows SharePoint Services lists, or deploy your whole database to a server and use the collaboration tools that Windows SharePoint Services provides. Data collection by using e-mail messages If the primary purpose of your data access pages was to obtain new and updated data from other users, collecting data by using e-mail messages might be the most effective alternative in Access 2007. In Access 2007, you can collect and update data by using e-mail messages. By using this feature, you can create a data collection message that contains a form which the recipients complete and return. You can process the data manually or choose to have the data automatically entered into the database. After you create a data collection message, you can reuse the message as often as necessary to obtain new and revised data from its recipients. Export a table or query to a SharePoint site The easiest way to export data to a SharePoint site is to run the Export Wizard. After you run the wizard, you can save your settings — the information that you provided when you ran the wizard — as an export specification. You can then rerun the export operation without having to provide the input again.

109

Intermediate Access Training Manual 8.2 ~ View data as a Pivot Table Procedure A pivot table is an interactive table that summarizes database information according to user specifications. Exercise Open the database file called Employee Data Display the Salary Summary query From the View menu choose Pivot Table View The Pivot Table list should automatically appear.

Drag the fields to the correct place as shown below: o

Total Salaries: Drop Total Fields Here

Dept: Drop Column fields here

Position: Drop row fields here

Gender: Drop Filter Fields Here

Use the drop down boxes to analyse the data by different criteria.

ÂŠ Sarah Mason

110

Intermediate Access Training Manual 8.2 ~ View data as a pivot chart The pivot chart is created in a similar way. Using the same data, design a pivot chart to show a summary of sales.

ÂŠ Sarah Mason

111

Intermediate Access Training Manual Index Address Labels ............................................. 98 Append text ................................................. 20

Mailmerge .................................................. 106 Object dependencies .............................. 101

Caption ......................................................... 18 Charts ............................................................ 74 Composite key ............................................. 23 Custom form ................................................. 47 Data type ....................................................... 5 Default value ................................................ 16 Export an access datasheet .................... 102 Filter by form ................................................. 37 Form controls ................................................ 55 Formatting Data type ................................. 10 Hyperlink........................................................ 11 Input mask .................................................... 34 Logical operators......................................... 58 Lookup field .................................................. 25

ÂŠ Sarah Mason

Picture in table ............................................. 19 Pivot chart .................................................. 111 Pivot Table .................................................. 110 Primary key ................................................... 23 Query ~ calculated field ............................ 69 Query ~ duplicate records ......................... 65 Query ~ parameter ..................................... 73 Query ~ unmatched records ..................... 61 Relationship .................................................. 40 Report ~ calculated control ...................... 93 Report ~ Design View .................................. 90 Report ~ Sections ......................................... 84 Required field ............................................... 16 Sharepoint Services ................................... 109 Subdatasheet............................................... 44 Summary queries ......................................... 59

112