To create a filter:

Click on the selection (for example, if you have a city field, the name of a particular city in the city column)

Click on the Filter by Selection icon

USING FILTER BY FORM – lets you filter information based on specified multiple criteria

To create a filter using multiple criteria:

Click the Filter by Form icon

Type criteria into fields

Apply Filter

QUERYING THE TABLE - a query specifies how Access will look at and organize the data in a database table. The most common query is a select query which is used to ask questions about your data and analyze the results. The output of a query creates a dynaset which looks and behaves like a table, but is actually a dynamic view of data from one or more tables, selected and sorted as specified in the query. The dynaset created by a query will to displayed in a Query Datasheet.

To start a new query:

Select File, New, Query or click the New Query icon (1st icon with star)

Select Query Wizards for Access query or New Query to create a select query (query will be defined in grid area located in the lower portion of the window; the grid will be referred to as a QBE grid of columns and rows

Add fields (press Alt + down arrow, select field name and press ENTER or click and drag field from field list to grid; use SHIFT for multiple fields

To view the dynaset created by a query, Select View, Datasheet

MOVING COLUMNS - To move a column of data:

Click on the column title to highlight the column

Hold down the left mouse button until you see a box indicating that you can move the column

Drag to the desired location and release the mouse button

QUERYING WITH WILDCARD OPERATORS - wildcards allow you to narrow your search when search information is sketchy or incomplete. wildcard operations * and ? can be used in a search expressions to substitute for search character criteria.

To use wildcard operators:

* any characters, ? single character, # single numeric digit

Press F1 after cursor is on any property to learn more about that field property

Make any changes

Save new design

 

VIEWING SQL STATEMENTS - SQL (Structured Query Language) are statements that define the actions of the query and are created by Access each time a query is created in the Query Design window. To view SQL statements:

Select View, SQL or click SQL icon

USING THE EXPRESSION BUILDER - for specifying AND/OR conditions

Click the Build icon (...)

Set conditions; for example to find all employees who have a hire date less than 1/1/91 and have a first name that starts with a T:

Click <, type 1/1/91, choose OK; enter a T* in the First Name criteria cell

Run the Query

QUERYING WITH TWO TABLES - two tables can be joined to produce a display that combines information from each of the tables; this is called a multi-table query.

To use 2 tables:

Open database Employ3

Select Employ3 table and Open

Select File, New, Query

Select Query, Add Table

Select Position, Add, Close

Add fields: Lastname, Firstname, Date Hired, Department

Run Query

Follow same process to query with 3 tables

Select Query, Add Table ; Select Payrates, Add, Close

CREATING A CALCULATED FIELD - table queries are not limited exclusively to the fields in the database tables. Other query fields can be created based on table information. This type of field is caled a calculated field. To create a calculated field:

Return to the Query design window

Move to the Field row of a blank column

Type a new field name and a colon(:)

Press the spacebar

Type an expression that will be used to calculate the data (field names must be enclosed in brackets)

press the enter key

Run the query

USING ACCESS FUNCTIONS - access can create totals based on groups of data or expressions

To view a calculated field total:

Choose View, Totals or click Sum icon (by default Group by will appear in all query fields)

Select the type of calculation from the list (AVG, SUM, MAX, MIN)

Delete all columns from the display that are not to be calculated

Run the query

Any printed output generated from a table or query is considered a report.

Access lets you create a professional custom report that can be enhanced with text formats, styles, and different layouts.

The report feature also lets you create groups, subtotals, and grand totals.

To create a columnar AutoReport:

Open the database you want to use to create the report.

Open the Report Objects window.

Choose: .

The New Report dialog box displays six ways to create a report.

Select: AutoReport: Columnar.

Select: The table or query you want to base the report on from the drop-down list.

Click: .

When the report is generated:

The table or query name appears at the top as a title in a large font size.

Field labels will be displayed at the left margin.

Field data will be displayed in boxes to the right of the field labels.

The Zoom feature is available in all Print Preview windows to allow you to change the magnification of the screen display from the default of 100%.

To change the character size display:

Click: on the Zoom Control drop-down list.

Choose: The zoom percentage you want to use.

To display the entire page:

Click: on the Zoom Control drop-down list.

Choose: Fit.

When you use this setting, you can see the layout of the page. In addition to the report title at the top of the page, you can see the current date and page number displayed as a footer at the bottom of the page.

You can view multiple pages of a report:

Click: Two Page to view two pages.

Click: One Page to return the view to one page.

The will zoom to the last magnification level you set. The will zoom to the Fit magnification.

To close the file without saving:

Choose: File/Close

Choose: No when prompted to save.

USING THE REPORT WIZARD - To create a report using the Report Wizard:

Move to: The Reports tab.

Double-click Create report by using Wizard.

The Report Wizard works very much like the Form Wizard. In fact, the first dialog box is identical to the Form Wizard dialog box.

As you did in the Form Wizard, select the table you want to use from the drop-down list, and add the fields in the order you want them displayed.

Click: to move to the next dialog box, where you can group the report.

Click: to move to the dialog box used to sort the fields on the report. To specify the sort fields:

Click: in the first list box.

Select: The field that you want to sort on first.

Repeat the above procedure for subsequent fields.

Click: to move to the next dialog box.

In the next dialog box, you select a report layout and select the orientation you want if different from the defaults.

Unless otherwise specified, the Report Wizard will use the Tabular layout.

Portrait orientation is the default, however, you will want to specify Landscape if your report will have several fields.

Click: to move to the next dialog box.

Select: A style for the report.

Click: .

Type: A report title. The title will appear as an object at the top of the report.

to generate the report.

Once the report has been generated, fit the report so you can view the entire page to get an idea of the report layout. Use the View/Pages command to view all the pages in the report, if desired. Then, set the magnification back to 100%.

The first sort field will be displayed followed by subsequently selected sort fields.

MODIFYING THE REPORT DESIGN - To change the layout of the report:

Click: Design View

The Report Design window will be displayed with its own menu and three toolbars:

The Report Design toolbar contains standard buttons and buttons that are specific to the Report Design window.

The Formatting toolbar contains buttons that let you add enhancements to the text and the control borders.

The Toolbox toolbar contains buttons that help you add and modify controls. For example, you can add check boxes and list boxes to the form.

The window is bordered at the top and along the left edge by rulers, which help you line up the controls.

The Form Design window is divided into three sections:

Controls - Reports and forms are linked to the underlying table by using controls.

Controls are graphical objects that can be selected and modified. A text control box creates a link to a field from a table and displays the field entry in the report or form. This is called a bound control.

A label control initially displays the field name from the underlying table and is called an unbound control. A calculation control displays the results of a calculation in the form. It uses an expression that uses data from the underlying table. Compound controls are text box and label controls that are connected and act as one when manipulated.

To select and modify label controls:

Click on the desired control in the desired section.

The label control is surrounded by eight sizing handles. Sizing handles are used to size the control.

The move handle, a large box in the upper-left corner, is used to move the selected control.

Hold the [Shift] key while clicking on another text box control to select two or more controls.

Once controls are selected, use the hand mouse pointer to drag to a new location.

A grid of dots helps to position controls. Controls are "snapped" to the nearest grid line.

To size a control:

Select the desired control.

Point to the middle handle on the right edge of a selected control.

Use the pointer to drag the control.

Use Print Preview and zoom to see the change in the report.

To change page orientation to landscape:

Choose: File/Page Setup.

Open the Page tab.

Select Landscape.

Choose Preview and zoom the window to fit.

PRINTING A SELECTED PAGE - To print only selected pages:

Move to the desired page.

Choose: File/Print.

Select: Pages.

Enter the page number in the Form and To text boxes.

Click: .

Close the report window saving the changes. The report object name is listed in the Reports object box. The Report Wizard uses the report title as the object name.

CREATING A REPORT FROM A QUERY - Just as you can use a multitable query to create a multitable form, you can use a multitable query to create a report.

To create a tabular report based on a query using the Report Wizard:

Open the Queries tab and select the query you want to use.

Click: from the Object list.

Select: .

The selected query will be displayed in the list box of the New Report dialog box.

Choose: Report Wizard and click .

Add the fields you want to include

Click: .

Print the report.