COURSEWARE. Microsoft Access 2010 Advanced

(1)

COURSEWARE

Microsoft

®

(2)

COPYRIGHT

publication, including the student manual, instructor's guide and exercise files, or any part thereof, may not be reproduced or transmitted in any form or by any means, electronic or mechanical, without prior written permission of

EZ-REF Courseware.

TRADEMARKS

EZ-REF is a trademark of EZ-REF Courseware. All other products or brand names mentioned are trademarks or registered trademarks of their respective holders.

DISCLAIMER

While EZ-REF Courseware has taken care to ensure the accuracy and quality of these materials, all material is

provided without any warranty whatsoever, including, but not limited to, the implied warranties of merchantability or fitness for a particular purpose. Any persons or businesses mentioned in the text of this manual are strictly fictitious. Any resemblances to existing or deceased persons, or existing or defunct businesses, is entirely coincidental.

(3)

ADVANCED MICROSOFT ACCESS 2010

This book was developed using generic exercises. The course is broken down into four "modules" along with an “If You Have Time” module that cover the objectives that should be achieved during this class.

Each major topic starts on a new page followed by the following sections:

USAGE

This section offers a brief explanation of what the command/function is used for, how it works and some possible examples.

This icon will be placed to the left of all mouse actions so that you can easily see when the mouse may be used to perform a task.

This section lists the keystrokes or function keys the user may press as a shortcut for performing the current command.

NOTE: This box alerts you of things to watch out for. The post-it note in the left column always indicates an important note to remember.

TIP: This box will let you in on a little secret or shortcut when working with Access. When you see this icon, you’ll know that a "TIP is available.

(6)

(7)



Indexing a Table



Parameter Queries

(8)

INDEXING A TABLE

USAGE:

One of the major tasks of working with your database is to get it to run faster and more efficiently. A large part of that speed and efficiency will come from the way you design your table structures.

However, you have probably noticed that a lot of your database work involves querying, searching and generating reports. These functions can take a long time if you are working with a large database or are searching several different fields.

One feature that can increase the performance of a table and, yet, is often overlooked in setting up a table, is the index.

Indexes are based on fields. Any field that is often used for searching or sorting should be indexed. Indexes work similar to a book index - if you need to find something quickly, Access looks it up in the index and can then go directly to the specified location.

Basically, an index is a pre-sorted list of the field, which means that Access doesn't have to sort each time you use that field.

Although indexes usually do speed up queries and searches, they can slow you down a little while adding or changing records, especially if you have a lot of indexes.

The reason is that Access must update all your indexes each time you add a new record or if you change the data in a field that has been indexed.

(9)

CREATING AN INDEX

In order to create or modify an index, you must be in the Design view for the table.

NOTE: You cannot index Memo, Hyperlink or OLE Object

data type fields.

Once the table is selected, switch to Design View.

The current table structure will be displayed in the same window that was used to create the table, as shown below:

Notice the Indexed property (within the General tab) shown at the bottom of the window. Whenever you select a field in the top portion, this property will reflect the current setting.

(10)

To create/change an index for a field, highlight the field in the top portion of the design window and then click on the Indexed property in the lower portion of the window.

Click on the down arrow (located to the right of the Indexed property) to view a list of index options.

The following options are available for indexing the field:

No This is the default setting. Use this to remove an index from a field.

Yes (Duplicates OK) Use this option to index the current field and to allow duplicate entries. You will need this for fields such as lastnames, zipcodes, etc. where you will probably have more than one record with the same entry.

Yes (No Duplicates) Use this to index the current field but not allowing duplicate entries. You will need this to make sure that no two records will have the same value for this field. Possible uses are for social

security numbers and employee ID numbers.

(11)

MODIFYING AN INDEX

Once an index has been created, you may want to modify the index by changing its name or the order in which the index is to be sorted (ascending or descending). To view/modify indexes, you must access the Indexes window.

Click on this tool to open the Indexes window.

Use the first column to change the name of the index. The second column is used to select the field to be indexed (you can also use this window to create indexes).

The last column is where you define the order in which the field is to be indexed. When you select this column, you can click on the down arrow to choose Ascending (A-Z, 0-9) or Descending (Z-A, 9-0). In the bottom section there are three Index Properties that can be applied to the current index, as described below: Primary Specifies whether the current index is the

primary key for the table. You can only have one primary key per table.

Unique Specifies whether duplicates are allowed. Ignore Nulls Indicates whether or not null (empty) field

(12)

MULTIPLE-FIELD INDEXES

When working with indexes, you may find that you end up

searching on more than one field at a time in order to find a specific record.

For example, if you do a lot of searching based on a last name, you may find that you have several groups of people with the same last name.

To narrow the search down to the person you are looking for, you may end up adding a first name to the query, along with the last name.

Access allows you to combine more than one field into an index to help narrow such searches.

This index can also be used to prevent more than one record from having the same data in all the fields of the index.

Using the previous example, you would create an index that consists of both the last and first name. Since you end up using both fields during a query anyway, you may as well have one index to take care of both fields.

You could then make the index unique, preventing more than one record with the same last name and first name.

If the last name field is defined first in the index, you would be allowed to have multiple records with the same last name.

(13)

To create a multiple-field index, you would enter a name for the index and then specify the fields to be indexed on multiple rows.

Access will include all rows as part of the same index until it reads a new index name. Don’t add anything else to the Name column (other than the initial index name). Simply add each field to be included in the combined index on a new row. The order in which you place the fields determines the sorting order.

In the figure shown below, the multi-field index is labeled Address and consists of both the Address1 and Address2 fields:

If you want to make sure that no two records contain the same last and first name, use the index properties to make the index Unique.

(14)

PRACTICE EXERCISE

Instructions:



Open the SALES database and then view the CLIENTS table in design mode.



Create indexes for the City and Zipcode fields allowing duplicates.



Make the CustId field the primary key.



Create a multiple-field index based on the last and first names. Name the index Full Names. Do not allow duplicates.



Add a new record using the following information: George Wilson

7887 West 57th Street New York, NY 10019



Open the ORDERS table in design view.



Create a multiple-field index based on the CustID, Date and Item# fields. Name the index Sales.

(15)

PARAMETER QUERIES

USAGE:

If you use a particular query often, but tend to change the criteria each time, you might find using that query becomes tedious since you have to constantly enter the design view and make changes. For example, you may create a sales list each day summarizing the sales of the previous day. Normally, you would have to edit the query and change the sales date every time the query is run. Access provides a feature referred to as a Parameter query which allows you to run the query without having to manually change it each time. Instead, a box will pop up asking you to fill in the criteria to be searched for. If you have more than one criterion, you will get more than one dialog box (displayed in sequence).

You can also use these parameter queries for forms and reports. To create a parameter query, first create/edit the query you want to use and make sure you are in the design view.

To create the prompt for the dialog box, access your query and switch to Design View. Simply enter your prompt, enclosed in square brackets [ ], in the Criteria cell. The prompt can contain the field name, but cannot be just the field name.

When you run the query, you will see the prompt in a dialog box:

(16)

SPECIFYING DATA TYPE

In some cases you may want to specify the data type for the

prompt. This can be helpful in restricting the user from entering an incorrect data type or format.

Once you have your parameter(s) created, you should click on this tool (located within the Show/Hide section on the Design Ribbon). The following diagram illustrates how to enter your prompt:

Enter your prompt(s) in the left column exactly as you entered them in the query - including the brackets.

TIP: To be sure you enter it exactly the same, you might want to simply copy it from the query and paste it in the Parameter column.

Choose the data type by clicking in the right column and selecting it from the pull-down list.

(17)

CREATING MULTIPLE PARAMETER PROMPTS

If you need to prompt for more than one value for a field, you can add multiple prompts to the Criteria cell.

This comes in handy whenever you need to ask for a range of values, such as a beginning and ending date.

To create a multiple parameter prompt for a range, use the Between operator in the criteria cell.

Example:

Between [Enter the first date:] AND [Enter the last date:]

When using multiple parameter prompts, Access will display a separate prompt for each entry required.

(18)

PRACTICE EXERCISE

Instructions:



Using the CLIENTS table, create a query with prompts that will ask for the last and first name of a client.



Using the ORDERS table, create a query that will ask for a beginning and ending sales date. Be sure that only date formats are accepted.

(19)

CROSSTAB QUERIES

USAGE:

Crosstab queries show your data in rows and columns similar to a spreadsheet. This allows you to create new types of listings that can be summarized and grouped in different ways.

Crosstabs provide a compact overview for large amounts of data. For example, you could create a listing that breaks your sales down by customer and by product.

A simple query might summarize the data in columns but makes it difficult to get the complete picture, as illustrated below:

By creating a Crosstab query, you can organize the data in a more readable format, as shown in the diagram below:

(20)

A crosstab requires a minimum of three fields of information:

Row heading This field will be listed in the first column of the crosstab and groups the data by row. In the previous example, Salesperson is the field used for the row headings.

Column heading This field is listed across the columns. Each unique entry is listed in a separate column. In the previous example, Product is the field used. Notice how each individual product is listed in a separate column.

Value This field is used for the actual calculation to be performed. In the previous example, the sales amount was used for calculating the total sales by product for each salesperson.

Although you can create the crosstab manually, it is easier to use the wizard, as outlined in the following steps:



Select the Query Wizard tool.

Access displays the New Query dialog box:

(21)

The first screen of the wizard asks you to select the table (or query to be used), as shown below:



Select the query or table to be used in the crosstab query. If you do not see your query/table listed, use the radio buttons located within the View section to list other objects.

(22)



You are now ready to select the field to be used for the row headings. Select the field to be included from the list in the left box and click on . The field name will be moved to the list in the right section.

If you change your mind, select the field to be removed from the right box and click on . Click on to remove all fields from the right column.

If you select more than one field, the query will be broken down into groups starting from the leftmost column. For example, if you chose the fields region and salesperson (in that order), the query would be summarized by region first. Each region would then be summarized by salesperson. Select up to three fields to be used for row headings.

Notice how Access displays a sample in the lower portion of the box so that you can better visualize the final report.

(23)



The next step is to select the field for the column headings. Each unique entry in the field you select will display as a new column so be selective about which field you choose. For example, choosing Product for your column heading will result in a new column for each product within the table.

When you are done, click on .



Select the field to have calculated from the list in the left box:



(24)



Select the type of calculation from the list on right side. If you want a separate sum for each row (this can be useful when using more than one row heading), click the check box in the middle left. When you are done, click on .



The last step is to name the crosstab query so it can be used. Enter the name in the space provided if you want to change the suggested name that Access displays.

You can also choose to view the data when you are done defining the query, or you can return to the query design view if you feel you need to customize the query further. For additional help after setting up the crosstab query, click on the help checkbox in the bottom of the dialog box. Use the button to trace back your steps if you want to make a change in the fields you selected.

(25)

EDITING THE CROSSTAB

Using the Query Wizard to create the initial crosstab structure is simple and efficient. However, you should know how to manually edit the crosstab so that you can customize the layout if you change your mind after it has already been created.

As always, you should be in the design view for the crosstab query before continuing. Notice the new row labeled Crosstab that will be used to change the status of a field.

The following crosstab options can be used to define a field: Row Heading Use this setting to designate that the

current field be used for the rows. Column Heading Use this setting to designate that the

current field be used for the columns. Value Use this setting to designate the current

field as the value to be used for the calculation. Once you have selected the field, choose the type of calculation you need from the pull-down list located on the Total row.

(not shown) Use the current field for grouping, sorting or criteria, but don't display the data in the final query.

(26)

PRACTICE EXERCISE

Instructions:



Using the COMPUTERS database, create a crosstab query for the “Sales Data” table that breaks the sales down by region and then by salesperson.



Edit the query and add another row heading so that the sales are grouped by region first and then by product.

(27)



Designing Forms



Adding Bound/Unbound Objects



Changing Object Properties



Adding Combo/List Boxes



Adding Group Options



Designing Command Buttons



Saving the Form



Using the Form

(28)

DESIGNING FORMS

USAGE:

Access forms are similar to the daily "paper" forms that you have to fill out. You are constantly filling out insurance forms, job

application forms, renewal forms and many other "data-gathering" forms. These "paper" forms are used to collect specific type of data that are used in reports and other functions. Access provides an easy-to-use form system that allows you to use the computer as the data gathering "form" instead of the paper form.

A form is made up of controls, which are text boxes, buttons, graphics, lines and anything else you place on a form. These controls can be bound to a table or query. A bound control represents a specific field that is connected (bound) to a

table/query. The data for the field is usually represented by a text box, radio buttons, or drop-lists. Other unbound controls include lines, labels, and other objects that are there for the design of the form. A title is an example of an unbound control since it isn't connected to a table/query. There are also calculated controls that are based on formulas or expressions. These expressions can be a combination of math operators (i.e., + and -), field names, other controls, constants or built-in functions.

There are a number of forms that you can use within Access, as discussed below.

To quickly create a form, use one of these tools:

Click this tool to create a Form which allows you to enter information for one record at a time.

Click this tool to create a Blank Form which you will then need to manually design.

Click on this tool to choose from a list of More Forms.

Click the Form Design tool to create a blank form and immediately begin editing it from design view.

(29)

USING FORM WIZARDS

Although you can design forms on your own, to quickly get started with forms, Access includes a wizard which will walk you through the basics steps.

The wizard provides a selection of form layouts and styles so you can easily create professional looking forms.

The Form Wizard is also a quick way of getting started on your own custom forms, since you can use the wizard to create the basic layout and design.

To create a new form using the wizard, follow these steps:



Click on this tool (located within the Forms section of the Create Ribbon).



The first step within the wizard is to select which fields are to be included on the form. Not all fields have to be stored on one form.

(30)

Highlight each field you want to add to the form and then click on the button. This adds the highlighted field to the form. Continue this process for each field to be included on the form. If you click on , all the fields will be added to the form.

If you want to remove a field from the list, highlight the field name in the right column and click on .

To remove all the fields you have added, click on the . If you need to return to the previous step, click on . Once you have made all your selections, click on to continue with the next step of the wizard.

(31)



The next step of the Form Wizard allows you to select a layout for the form (e.g., columnar, tabular).

When you select a layout from the four choices provided on the right side of the dialog box, Access will display a preview to the left so that you can see how the form will look.

Once you have selected a layout, click on to continue to the next step of the wizard.

(32)



The final step allows you to add a title to the form and view the data or return to the design mode where you can move things around on the form.

Once you have chosen whether you want to Open the form or to go to the design mode to Modify it, click on .

Depending on which option you selected, Access will either open the form or allow you to now modify it.

(33)

CUSTOMIZING THE FORM

USAGE:

Once you have created a basic form, you will more than likely want to change the layout by moving the fields around, changing colors, fonts or adding graphics.

Switch to Design View.

The form will change to a screen similar to the one shown below:

NOTE: The number of controls will vary depending on the form.

You can use the Rulers to help line up the Controls within the different Sections of the form.

Whatever controls you place in the Form Header or Form Footer are displayed only once per screen.

Objects placed in the Details section are displayed for each record and may be repeated several times per screen. In other words, there may be room to display more than one record on a screen.

(34)

DISPLAYING THE FIELD LIST

If you want to quickly add a field to the form, you can select the field from a list and drag it to the required location.

First you must display the field list, as shown below:

If for some reason, the field list has been removed from view, click on this tool (located within the Tools section on the Design Ribbon) to display the field list.

A box similar to the following diagram will be displayed:

To place a field from the list onto the form, click on the field name in the list and drag the field to the location on the form.

(35)

SELECTING CONTROLS

As you work with a form, you will need to select one or more of the controls to move them around the form or to change their

properties.

To select a control, you must use the pointer tool.

Once you select the pointer, click on the control. Small rectangular handles appear around the control, as illustrated below:

Some controls, such as the text box shown above, have an attached label that will also be selected.

The larger handles (black rectangles) are used to reposition the control. If, for example, you wanted to move the label for the control, you would drag the move handle for the label.

If you only wanted to move the text box, you would drag the move handle for the text box. To move both the control and its attached label, place the mouse pointer over a border until it changes to a small hand, as illustrated in the diagram below:

Once you have the hand pointer, click and drag both items to their new location on the form. To select more than one control, hold the

(36)

RE-SIZING CONTROLS

If you need to make a control larger or smaller, you must first select the control to view the sizing handles, as shown below:

To resize a control, follow these two steps:



Place the mouse over one of the smaller sizing handles.



When the mouse pointer changes to a double-sided arrow, drag the handle in the direction you need. When you release the mouse button, the control will be resized.

COPYING A CONTROL

To copy a control, follow the three steps outlined below:



Select the control(s) to be copied.



Click on the COPY tool (located within the Clipboard section on the Home Ribbon). The control is temporarily copied to the clipboard.



Click on the PASTE tool (located within the

Clipboard section on the Home Ribbon). Each time you click on the Paste tool, another copy of the control is placed beneath the original one. You will need to move these copies by dragging them to a new location.

(37)

DELETING CONTROLS

If you no longer need a control, you can easily delete the item by following the two steps outlined below:



Select the control(s) to be removed from the form.



Press

=

.

UNDOING YOUR ACTIONS

If you accidentally deleted the wrong item or moved an object to the wrong spot, you can easily reverse your last action using the Undo option.

Click on the Undo tool (located on the Quick Access Toolbar across the top of the screen).

If you click on the down arrow (to the right of the tool), you can scroll through the last several actions.

(38)

You can access the Format Ribbon to customize the look of the form if needed.

Click on the down arrow beside this tool to choose a different font for the selected item.

Click on the down arrow beside this tool to select a new font size.

Click on this tool to turn bold on and off.

Click on this tool to turn italics on and off. Click on this tool to turn underline on and off.

Click on this tool to change the font color. Use this tool to left-align text within the report.

Use this tool to center text.

Click on this tool to right-align text.

Click on this tool to display/hide gridlines.

This tool is used to copy a format from one area within the report to another. Begin by selecting the text you want to copy the format from and then choose this tool. Your mouse pointer will change shape . You can now “paint” that format onto other report text

(39)

Click on this tool to apply a background color.

Alternates the fill/background colors for rows (records).

Click on this tool to specify formatting options based on a condition that you set. For example, you might set the condition of values between 100 and 200 to be displayed in red.

From within the Conditional Formatting dialog box (shown above) select the format to be applied and then the condition (rule) that must be met. When done, click .

You can also format numeric fields within your report by using this set of tools on the Format Ribbon.

Click on the down arrow beside this tool to choose from a list of formats.

This tool formats the current selection for currency. Formats the current selection for percentage.

Formats the selection for comma by adding a comma as a thousand separator and two decimal places. Increases the number of decimal places displayed. Decreases the number of decimal places displayed.

(40)

Use this set of tools to set the font, font size, text and background color and alignment.

Use these tools to add a

background image to the form. You can also specify alternate row color to break the form up so that it is easier to read.

From the Design Ribbon, choose this tool to quickly apply a theme to your form.

Themes are used to enhance reports, making them appear more professional with the application of uniform color designs.

Notice as you scroll the list. Your report reflects the currently selected theme.

From the pull-down list, select the theme you would like applied to your form.

You can also adjust the width of the columns by moving the mouse pointer just to the right of the column margin you wish to adjust. The pointer changes to a cross-hair indicating you are on the margin line. Once you see the double-arrow, double-click and the column will be widened to accommodate the largest entry.

(41)

SAVING/OPENING A FORM

USAGE:

As you can see, creating a form can be an involved task.

Designing a decent form can take several hours or even several days depending on the complexity of the form. Therefore, you should save the form often. Once a form has been saved, you can "Open" the form whenever you need it.

SAVING THE FORM

Click on this tool (located on the Quick Access Toolbar across the top of the screen) to save the form.

Access will automatically assign a generic name to the new form.

NOTE: If you would rather assign a unique name to the form, you will need to access the File Ribbon and select

Save As from the pull-down menu.

CLOSING A FORM

If you are finished with a form, you can close it by clicking on the close button (which is located in the upper right corner of the form).

If the latest changes have not been saved, Access will warn you and give you a chance to save them.

(42)

OPENING A FORM

Once you have saved a form, you can use it repeatedly without having to recreate the design of the form.

To use a previously saved form, you must open it.

You open forms the same way you open tables - from within the database window.

The list of forms should be displayed along the left side of the database window. From this list, simply double-click on the form you wish to open.

A new tab will have been added to the middle of the database window and will contain the form you selected.

(43)

CUSTOMIZING PROPERTIES

When working with any control on the form, Access allows you to customize its properties. Properties include such items as font and point size for text and can be easily modified using the tools located on the Design Ribbon (as shown on the previous pages). However if you want to modify the more advanced properties (such as what message appears on the Status Bar when the object is selected), you will need to access the control’s property sheet.

Select the object whose properties you want to customize.

Click once on the Property Sheet tool (located within the Tools section on the Design Ribbon).

Access displays a window along the right side of the screen with various properties. These properties can be customized for the currently selected control object, as shown below:

The properties are divided into four major groups (tabs) that allow you to control/customize the object’s format, data, event

information and other miscellaneous settings.

You can choose a tab to work on just that group or you can click on the tab labeled All to see a list of all available settings in one list.

(44)

Below is a list of the most commonly used properties: PROPERTY DESCRIPTION

Name Name given to a control

Control Source Data to be assigned (field or expression) Status Bar Text Sets custom message for display on status bar IME Hold Selects whether the Kanji Conversion Mode is

restored when the user moves to another field and then returns to this field.

IME Mode Converts your keystrokes into Asian characters. IME Sentence

Mode

Specifies the writing style to use when

converting to Kanji. Choose Normal for literary documents, Plural for entering names or

addresses, Speaking for conversational or No Conversion to leave the field as it was entered. Visible Makes the object visible or hidden

Display When Determines when the object is displayed Enabled Activates/deactivates the control

Locked Prevents changes in form view Scroll Bars Makes scroll bars appear in control Can Grow Expands control when printed to fit text

Can Shrink Shrinks control when printed to eliminate blanks Left Determines horizontal position

Top Determines vertical position Width Determines width of control Height Determines height of control

Special Effect Sets 3-D effects (normal, raised or sunken) Back Color Selects interior color

Back Style Selects style for background (clear or normal) Border Color Select a color for the border

Border Width Select the width of the border Border Style Sets line style (solid, dashes, dots) Fore Color Selects foreground color

Font Name Selects font to be applied to a text control Font Size Selects point size to be applied to a text control Font Weight Selects weight of font (various weights for bold) Font Italic Determines whether text will be italicized

(45)

Other properties you may encounter include the following: PROPERTY DESCRIPTION

Text Align Sets the alignment of text within the control (choose left, center, right)

Format Defines format of numbers, dates, times, text Decimal Places Sets the number of decimal places for

numbers

Input Mask Specifies how data is entered/displayed Default Value Sets the default value for each new record Validation Rule Sets expression to be evaluated when data is

added/changed.

Validation Text Sets the message that appears if the validation rule has not been satisfied. Auto Tab Determines whether Access automatically

moves to the next control when the last character of a field or input mask is entered. Enter Key Behavior

Defines whether the

E

key moves to the next control or creates a new line.

Allow AutoCorrect Defines whether AutoCorrect (for spelling mistakes) will be enabled.

Tab Stop

Determines if

T

moves to this control Tab Index Defines the tab order for the control Help Context ID Defines the identifying topic number for a

custom Windows help file

Tag Stores additional control information Before Update Runs a macro before a control is updated After Update Runs a macro after a control is updated

You may see additional properties, such as On Enter, On Exit, On Click, etc. These properties are used to run macros or Visual Basic procedures when these events take place.

For more detailed information, click on the property and press

!

. Access will display a page describing the selected property.

(46)

USING THE DRAWING TOOLS

In addition to adding text to the form, you can customize the form even further by adding your own drawings. Simply select the drawing tool of your choice and begin creating whatever customized picture you require.

TIP: Double-Click on any tool that you want to use more than once.

To add a rectangle to your form, follow the steps outlined below:



Click on the Rectangle tool (located within the Controls section on the Design Ribbon).



Move the mouse pointer to where the box should appear. The pointer changes to a thin cross-hair with a rectangle attached to it .



Click and drag to draw the box.



When done, release the mouse button.

To add a line to your form, follow the steps outlined below:



Click on the Line tool (located within the Controls section on the Design Ribbon).



Move to where the line should begin. The mouse pointer changes to a thin cross-hair with a line attached to it .



Click and drag to draw the line.

(47)

ADDING A CHART TO YOUR FORM

Access allows you to quickly add a chart to your form. To do so, follow the steps outlined below:



Click the Chart tool (located within the Controls section on the Design Ribbon).



Your mouse changes to a pointer with a small chart attached for you to click and drag to specify the size of the chart on the form.

When you release the mouse button, a dialog box appears:



This step is used to select the table or query that will contain the data to be charted.

(48)



The next step of the wizard is now displayed. You will need to specify the fields to be plotted, as shown below:

At least one field must be numeric. You will choose at least two fields (one for the actual values being plotted and another for the labels which identify the values).

To select a field you may either double-click on its name or highlight it and then click on . Click on to add all fields. To move to the next step, click on the button.

(49)



Select the type of chart (e.g., pie, column) to be added to the form and then click on to move to the next step.



This step is used to preview the chart as well as change how the data will be summarized (e.g., sum, average).

Click on the Preview button (located in the upper left corner) to preview the chart. To change how the data is summarized, double-click on the field listed on the left side of the dialog box - towards the top of the chart.

Another dialog box will be displayed, as shown below:

Select the type of calculation to perform and click on . Click on to access the next step of the wizard.

(50)



This next step allows you to specify if you want to chart the change from record to record in the form.



This final step is used to enter a title for the chart. You may also specify whether a legend should be displayed. Only if you have more than one series of values being plotted will you need to display a legend.

If you need additional help once you are done with this wizard, check the option located at the bottom of the dialog box. Use the button to return to previous steps. When done, click on .

(51)

PRACTICE EXERCISE

Instructions:



Using the ORDERS, CLIENTS and ITEMS tables, create a query called Client Sales that contains the client's name and their order information.



Create a form called SALES using the new query that displays the client and information as well as a chart that displays the total sales for only the client that is currently being displayed.

(52)

ADVANCED FORM FEATURES

USAGE:

There are a number of ways in which you can customize the form. For example, you can add bound and unbound objects, add combo boxes, include Control Tips, and background pictures.

BOUND & UNBOUND OBJECTS

When you embed or link an object in an Access form, it is displayed in a control referred to as a Frame. Access offers two types of frames for its objects: Bound and Unbound.

An unbound object frame is used to add an OLE (Object Linking and Embedding) object to a form without actually storing the object in a table. Therefore, the object frame is not bound to a field within the table. For example, if you wanted your company's logo on the form, this would be considered an unbound object - it is not linked to a field and does not change when the record changes.

A bound object frame is used to display OLE objects that are stored within a field of a table. For example, you could add pictures of items that your company sells by linking the object to a Product Code field. Whenever the product code changes (from record-to-record), the picture would also change.

ADDING A BOUND OBJECT

Although you could use the Bound Object tool, Access offers a much quicker method for adding a bound object to your form.



Select this tool.



Drag the desired field from the list to the form. Access will automatically create a bound object based on the field you have selected from the list.



You may now customize the size and properties of the bound object - as you would any object on the form.

(53)

ADDING UNBOUND OBJECTS

To add an unbound object to your form, follow the steps below:



While in design mode, select the Unbound Object tool (located within the Controls section on the Design Ribbon).



Move to where the object should appear.



Click and drag to size the object on the form.



When done, release the mouse button.



The Insert Object box defaults to creating a new object. Select the application to create the object from and then click on the button.



You will be taken into the selected application where you may create the new object. When done, exit out as you would any other application. You will be prompted to update the form with the newly created object.

(54)

If you have an existing file you want to use (and, therefore, do not want to create an object from scratch), select Create from File while in the Insert Object dialog box (Step 5).

In the provided box, enter the name and path of the file containing the object to embed.

If you are unsure of the correct path, select to have Access browse through your drive/directory listing.

After choosing the file to be used as the object, select .

Regardless of whether you have created an object from scratch or accessed an existing file, at this point you should now be returned to Access with the object frame on the screen.



You may now customize the size and properties of the unbound object - as you would any other object contained within the form.

(55)

PRACTICE EXERCISE

Instructions:



Using the SALES form you created earlier, add an unbound object to the upper left corner that will store the logo from the file

LOGO.BMP.



Add a bound object for the field called Picture. Place the object to the right of the form and size it as needed.

(56)

ADDING A COMBO/LIST BOX

A list box provides a list of selections from which the user chooses an item by using a scrollbar. A combo box is a text box and a list box combined. It provides the user with a list of predetermined options but also allows them to enter a new value (not contained in the list). Access allows you to easily create either of these boxes. It is easier to create a combo/list box using the wizard.

The steps outlined below are used to create a combo box but the steps for creating a list box are similar.



Be sure that the wizard tool has been selected.





Click on the Combo Box or the List Box tool (located within the Controls section on the Design Ribbon).





Click and drag to size the box. When you release the mouse button, the following dialog box is displayed:



This step determines how the values in the list will be obtained. If you want the list of options to be automatically updated based on the values in another table/query, choose the first option. If the list will not change, choose the second option and enter the values yourself. Use the last option to select a record from within the current table based on the

(57)



The next step depends on the choice you made on the previous screen as to what is displayed at this point. The dialog box displayed below assumes you selected an existing table/query as the source of the values.

A list of tables and queries appears in this dialog box. Select the name of the table/query where the values are stored for the list. Use the radio buttons to determine what objects are to be listed (tables, queries or both).

(58)



After selecting the table/query, you must choose the field(s) to be displayed in the list, as shown below:

You may want more than one field to help identify the selections. For example, you might have the user select social-security-numbers but without the names to display alongside the ssns, the user won’t know which number to choose. To select a field, double-click on its name or highlight it and click on . To select all fields, click . Choose to move to the next step:

(59)

Choose to move to the next step:



This step determines the width of the columns in the list. Use your mouse to drag the right edge of the column. If you double-click, Access determines the appropriate width. Click on to move to the next step.

NOTE: If you selected more than one field for the combo list display, the next step will ask you to choose which one of the fields should actually be stored in the database.

(60)



This step is used to choose whether the values selected by the user from the Combo Box should be stored for later use (they might be used in calculations later) or if the values are to be stored within the field and saved with the record.

If you choose to store the data in a field, select the name of the field from the pull-down list located to the right.

(61)



The final step asks for a name for the Combo or List Box. Notice you can also specify whether you want help displayed with the Combo Box.

Use the button to return to previous steps. When done, click on .

(62)

PRACTICE EXERCISE

Instructions:



Using the form called SALES, replace the CLIENT ID control with a three-column combo box for the client's ID field. The combo box should display the ID and the client's last and first name, but should only store the ID in the table.

(63)

CREATING AN OPTION GROUP

An Option Group is a set of toggle buttons, radio buttons, or

checkboxes. The user is presented with a set of options which may be turned on/off.

The easiest method for creating an option group is by accessing the wizard, as outlined in the steps below:



Be sure that the wizard tool has been selected.





Click on the Option Group tool (located within the Controls section on the Design Ribbon).



The mouse pointer changes shape . Click and drag the outline box for the group. You can always resize it later. When you release the mouse button, the following dialog box will be displayed:

Enter the list of items that will make up the group of options from which the user will choose.

(64)



The next step allows you to determine whether a default choice should appear in the group. For example, whether an option should automatically be checked (enabled).

Click on the button to move to the next step.



The next step automatically assigns a value to each item within the group, as shown below:

(65)

This value will be stored in the field or can be used later to perform a task. By default, each item is numbered sequentially beginning with 1. When you read a value from a field, it should correspond with the correct item in the group.

For example, if you had three items (check, money order and COD) a 2 stored in the field would indicate the option money order. Click on the button to move to the next step.



Use the next step to determine what to do with the value (option) the user selects from the list:

You can choose to store it temporarily until you can check the value at a later time or you can store the value immediately into a field. Click on to move to the next step.

(66)



Use this step to select the 3-D display style and the type of buttons to use for the group. The sample on the left

changes to display the currently selected style.

Click on the button to move to the next step.



The last step asks for a name for the option group. Mark the Display Help on customizing the option group checkbox to obtain help once you return to the form.

(67)

PRACTICE EXERCISE

Instructions:



Create a new form using the ORDERS table (name it “Order Details”) that includes all of the existing fields.



Create an option group in place of the item number. Add the

following items to the option group, making sure you keep the order of the items the same.

Chia Pet Pet Rock

Ginsu Knife Set PEZ Dispenser Lava Lamp Mood Ring

Berlin Wall Chunks Football Phone Elvis Plates 3-D Glasses Dancing Flowers

California Raisin Figures



Rearrange the option group so that the items fit side by side in two columns (six per column).



Using the default assigned values, store the value in the Item# field.

(68)

ADDING COMMAND BUTTONS TO THE FORM

You can add buttons to your forms that can be assigned to specific functions using Visual Basic commands so that when the user clicks on the button, an action is performed.

The easiest way to create a command button without having to learn Visual Basic is to use the Command Button wizard. The wizard provides a list of common operations for working with such items as records, forms, reports, and applications and automatically creates the Visual Basic code for the button.

To create a command button, follow these steps:



Be sure the Control Wizard is turned on.





Click on the Command Button tool(located within the Controls section on the Design Ribbon).



The mouse pointer changes shape . Click and drag to size the button on the form. When done, release the mouse button. If you simply click the mouse button, a default button will be created. The following dialog box will be displayed:



From the wizard screen, select from the list of operation Categories provided in the left column.

(69)



Select the command from the right column. For example, if you wanted a button that would add a new record each time it is selected, you would choose the Record Operations category and then select Add New Record as the command (from the right column).

Click on the button to access the next screen.



The number of steps within the Command Button Wizard will vary depending on what action you selected.

Typically, the next screen allows you to determine whether the button you are creating will contain text or a picture:

If you select Picture, Access displays the various pictures which may be assigned to the button. The sample to the left of the dialog box displays the currently selected picture. If you would like a larger selection of pictures, mark the Show All Pictures checkbox.

(70)



You are now asked to name the button being created:

This can be especially important if you plan on referring to the button in programming.

If you want to change anything, click on the button to access previous steps.

If you think you may need some additional help working on the command button, check the box located in the lower left.

If everything is correct, select .

You will now be returned to the design view where you should see your new command button. To make any changes to the button, access its Properties - as you would any other object on the form.

(71)

PRACTICE EXERCISE

Instructions:



Using the Order Details form you created earlier, add command buttons to the bottom of the form that will add and delete records.



Also add a button to print the current record and one to undo the last record change.

(72)

ADDING CONTROL TIPS TO THE FORM

Access provides a feature that adds short pop-up messages to controls that act similar to the ToolTips that pop-up when pointing to a tool on the toolbar. Since these tips are associated with the controls, they are called Control Tips. Use Control Tips to help explain a field, provide a list of possible choices, or to display other helpful information a user might need when working with a

particular control.



Open the form to be modified in design mode.



Select the control to add the Control Tip to.



Select the Property Sheet tool.





Select the Other tab from across the top of the box.





Click in the ControlTip Text box and enter the tip you want to add to the currently selected control.

(73)

ADDING BACKGROUND PICTURES TO THE FORM

If you want your form to look more polished, you may want to add a background picture. For example, you could add your company logo or a photograph to the background of a form. Of course, you would have to select a background that would still allow your fields and other controls to be easily seen.

To add a background picture to your form, follow these steps:



Open the form to be modified in design mode.





Select the Property Sheet tool.





Be sure the “Form” is selected and then select the Format tab from across the top of the box.

The properties labeled Picture pertain to the form’s background graphic.

(74)

The following fields are used for the background picture:

Picture Type Click on to specify the picture type. Use embedded to make the picture part of the database file. Use linked to have Access create a simple pointer to the location of the picture.

Picture Use this section to enter the full path and filename of the picture to be used. If you do not know the name or location of the file, click on the button. Picture Tiling Click on to specify whether the

picture will be tiled on the form. This, of course, assumes the picture is smaller than the form. This is useful for

background textures such as paper, wood, marble, etc.

Picture Alignment Click on to specify the position on the form where the picture should be

placed. This, of course, assumes that the picture is smaller than the form. Picture Size Mode Click on to select how the picture is

to be sized. Choose clip to display the picture in its actual size, but if the picture is larger than the form, it will be cut off (clipped). Choose stretch to allow Access to increase the picture width and height to fill the entire form. Choose zoom to increase the size of the picture as much as possible without losing the original proportions.



Fill in the picture information in the boxes provided.

(75)



Creating Custom Reports



Changing Object Properties



Displaying Choices



Saving & Opening Reports

Module Three

(76)

CREATING REPORTS

USAGE:

Access reports provide a flexible means of displaying and

presenting your data. They allow you to design and customize your data to fit your specifications.

If you want to customize your reports, the best way is to start with one of the built-in reports and then customize it to fit your needs. Some added benefits of customizing your reports are the ability to create customized company reports with graphics and logos and the ability to add calculations based on certain criteria.

A report is made up of "controls", which are text boxes, rectangles, graphics, lines and anything else you place on a report. These controls can be bound to a table or query. A bound control

basically means that it represents a specific field that is connected or bound to a table/query. The data for the field is usually

represented by a text box.

Other unbound controls are simply lines, labels, rectangles and other objects that are basically just there for the design of the form. A report title is an example of an unbound control since it is not connected to a table/query field. There are also calculated controls that are based on formulas or expressions. These

expressions can be a combination of math operators (i.e., + and -), field names, other controls, constants or built-in functions.

Controls also have various "properties" that can be customized or set. These "properties" include such items as color, font, style, thickness, format and other features that directly affect that control. To quickly create a report, use one of these tools:

Click this tool (located within the Reports section of the Create Ribbon) to create Labels. A wizard will walk you through the steps.

Click on this tool to create a Blank Report. You will then need to customize it manually.

Click the Report Design tool to create a blank report and immediately begin editing it from design view.

(77)

Click on this tool (located within the Reports section on the Create Ribbon) to have Access create a basic report based on the data within the currently selected table or query.

You will not be prompted to enter any information. Instead, Access will simply create a new tab containing a generic report consisting of the information within the currently selected table or query.

You can access the Format Ribbon to customize the look of the form if needed.

Click on the down arrow beside this tool to choose a different font for the selected item.

Click on the down arrow beside this tool to select a new font size.

Click on this tool to turn bold on and off. Click on this tool to turn italics on and off. Click on this tool to turn underline on and off. Click on this tool to change the font color. Use this tool to left-align text within the report. Use this tool to center text.

Click on this tool to right-align text.

Click on this tool to display/hide gridlines.

This tool is used to copy a format from one area within the report to another. Begin by selecting the text you want to copy the format from and then choose this tool. Your mouse pointer will change shape . You can now “paint” that format onto other report text

(78)

Click on this tool to apply a background color.

Alternates the fill/background colors for rows (records).

Click on this tool to specify formatting options based on a condition that you set. For example, you might set the condition of values between 100 and 200 to be displayed in red.

From within the Conditional Formatting dialog box (shown above) select the format to be applied and then the condition (rule) that must be met. When done, click .

You can also format numeric fields within your report by using this set of tools on the Format Ribbon.

Click on the down arrow beside this tool to choose from a list of formats.

This tool formats the current selection for currency. Formats the current selection for percentage.

Formats the selection for comma by adding a comma as a thousand separator and two decimal places. Increases the number of decimal places displayed.

(79)

Use this set of tools to set the font, font size, text and background color and alignment.

Use these tools to add a

background image to the report. You can also specify alternate row color to break the report up so that it is easier to read.

From the Design Ribbon, choose this tool to quickly apply a theme to your report.

Themes are used to enhance reports, making them appear more professional with the application of uniform color designs.

Notice as you scroll the list. Your report reflects the currently selected theme.

From the pull-down list, select the theme you would like applied to your report.

You can also adjust the width of the columns by moving the mouse pointer just to the right of the column margin you wish to adjust. The pointer changes to a cross-hair indicating you are on the margin line. Once you see the double-arrow, double-click and the column will be widened to accommodate the largest entry.

(80)

USING THE REPORT WIZARD

If you don’t want to manually create a report, you can use the Report Wizard which guides you through the entire process.

To access the Report Wizard, select this tool (located within the Reports section on the Create Ribbon).

If the table or query name that you want to create the report from is not already displayed in the text box, click on the down arrow located to the right of this box and select the table/query to use. Access displays the available fields in the left column and the fields currently selected for your report in the right column.

To add all the fields to your new report, click on .

If you only want some of the fields, highlight each field and click on to add one field at a time.

If you accidentally added a wrong field, or decide to remove fields from your new report, use to remove all the fields, or use to remove one field at a time.

(81)

The next screen is used to break your report down into groups or categories of records. This is useful for large groups of data and can make your report easier to read. For example, a list of customers could be broken down by state and then by city. The state would be the first group and then cities would be sub-groups under each state.

Use the left column of fields to choose what groups of records should be created. As you add a field for grouping, you will see the sample on the right adjust to show how the records will be

displayed.

If you add more than one field for grouping, you can use the Priority arrows to rearrange the order in which the records will be grouped.

Most users will not need to make additional changes, but if you only want the groups to be determined by the first few characters of a field, access and select the number of initial characters to scan before determining its group.

(82)

Next you will be prompted to specify how the report will be sorted, as shown in the diagram below:

You can select up to four fields on which to sort your data. Use the pull-down arrows to define which fields to sort the records on and in what order to sort them.

Click on this button next to each field to define whether that field should be sorted in ascending or descending sequence.

If you selected a field to group in the previous step, Access will automatically sort by that field first.

(83)

Now you can choose the layout of the report, as shown below:

As you make a selection from the Layout column, you will see an example of the layout in the left window. This will help you decide which layout best fits your report.

You can also change the page Orientation to match the type of report you want. Landscape is good if you are trying to fit a lot of columns across one page. Portrait is useful if you don't have many columns, but want to fit as many records as you can on a page. Use the checkbox at the bottom to automatically adjust the field lengths so that all your fields fit on the page. However, if you select this option, some of the fields may be truncated in order to make room for the others.

(84)

The final step is to name the report, as shown below:

Use the text box at the top to enter a title for your report.

Once the report is done, choose whether you want to Preview the final report or to switch to the design mode and Modify it.

When you are done, click on .

(85)

PRINTING OPTIONS

Before printing the report, you may need to change the paper size, page orientation, margins, or other features that affect the page layout.

Click on this tool (located within the Page Layout section of the Print Preview Ribbon).

The following dialog box will be displayed:

CHANGING MARGINS

Be sure that the first tab labeled Print Options has been selected at the top of the screen. As mentioned, you can adjust the top, bottom, left or right margins.

A preview box is provided on the right side of this dialog box to see how your new margin settings will change the document. This box also allows you to display/hide headings as well as specify whether only the form or only the datasheet should be printed.

(86)

CHANGING PAPER SIZE

To change the paper size and/or page orientation, select the second tab at the top of the screen, labeled Page.

Orientation The page orientation (portrait or landscape) can be changed by clicking on the appropriate radio button. Paper Click on the down arrow to the right of the "Paper

Size" section to select from one of many predefined sizes. You may also select the source (tray) for the paper.

Printer Allows you to specify the printer to be used. Click on to choose a printer other than the default.

COURSEWARE. Microsoft Access 2010 Advanced

COURSEWARE

Microsoft

®

TABLE OF CONTENTS

ADVANCED MICROSOFT ACCESS 2010

USAGE



Indexing a Table



Parameter Queries

INDEXING A TABLE

USAGE:

PRACTICE EXERCISE















PARAMETER QUERIES

USAGE:

PRACTICE EXERCISE





CROSSTAB QUERIES

USAGE:





















PRACTICE EXERCISE







Designing Forms



Adding Bound/Unbound Objects



Changing Object Properties



Adding Combo/List Boxes



Adding Group Options



Designing Command Buttons



Saving the Form



Using the Form

DESIGNING FORMS

USAGE:









CUSTOMIZING THE FORM

USAGE:















=

SAVING/OPENING A FORM

USAGE:

E

T

!



