Sorting Records

Introduction

• We could sort orders based on the order date.
• We could sort our customers by the state in which they live, or by their zip codes.
• We could sort our books by author, category, or price.

Access 2007 allows you to sort the records in your database tables based on a field or value that is either text (like author's last name) or numerical (like a customer's zip code or a book's price). Depending on the type of value, Access offers different sorting options.

Sorting on Text Values

When Access 2007 sorts on a text value, it offers the two options described in the table below:

Sort Option	Also Called	Description
A to Z	Ascending	Values closest to A are displayed first
Z to A	Descending	Values closest to Z are displayed first

To Sort Based on a Text Value

To sort based on a text value:

• Click the drop down arrow at the top of the field you wish to sort.
• When the menu appears, select either the Sort A to Z or the Z to A option.
• With A to Z, the records will be sorted based on the chosen field's value with the value closest to A at the top of the table, as seen below.
• With Z to A, the records will be sorted the using the chosen field's value with the value closest to Z at the top of the table.

Sorting on Numeric Values

Access 2007 also offers the two options when sorting based on a numeric value. These options are described in the table below:

Sort Option	Also Called	Description
Smallest to Largest	Ascending	Values closest to 1 are displayed first
Largest to Smallest	Descending	Values furthest from 1 are displayed first

To Sort Based on a Numeric Value

To sort based on a number value:

• Click the drop down arrow at the top of the field you wish to sort.
• When the menu appears, select either the Lowest to Highest or the Largest to Smallest option.
• With Smallest to Largest, records will be sorted based on the chosen field's value with the value closest to 1 at the top of the table.
• With Highest to Lowest, records will be sorted the using the chosen field's value with the value furthest from 1 at the top of the table, as seen below.

Challenge!

• Open the Customers table and the Books table.
• Sort the Last Name field in the Customers table from Z to A.
• Sort the Books table according to Price using the Lowest to Highest option.
• Clear your sorts.

Introduction

Using Reports To Make Data Meaningful to Others

As you know, queries make the data in a database meaningful to you. Sometimes, though, you need to share that data with someone else. A report is an effective way to present your data using an attractive layout. The text can be formatted in an Access report like it can be in Word documents.
Microsoft Access 2007 offers tools that allow you to create and format a report. The Report Wizard walks you through the steps of creating a report. The Report command, however, is much easier to use, and all of the formatting options are still available to you in Layout View once the report is created. With these tools, you can create a report based on a table or on a query.

Creating a Report Based on a Table

One of the easiest ways to create a report is using a table as the source of the report. For example, in our bookstore scenario we have a table that lists all of the books in our inventory. We want to create a Book Price List report that lists all of the details for each book in our store's inventory. The Report command makes this incredibly easy, as it automatically includes every field in the source table in the report.

To Create a Report Based on a Table using the Report Command

To create a report based on a table using the Report command:

• Choose the table you wish to use as the source of your report. To do that, you can either open the table, or just highlight the table name in the Navigation Pane. In our example, we used the open Books table to create the report.
• Select the Report command on the Create tab in the Ribbon, as seen above.
• The report is automatically generated and includes every field in the table in order of their appearance in the table. This can be seen in the example below, which was created form the table above.

Creating a Report Based on a Query

Access 2007 can create a report using a query as the source, as well. The process for creating a report based on a query is identical to the process for creating a report based on a table that was outlined on the previous page. And just like when making a report from a table, every field and record that appears in the query results will appear on the report.

To Limit the Number of Records in a Report

It is possible to limit the number of records in a report, provided that the report was based on a query. The limit is set in the query itself, using the query design screen.
To limit the records returned in a query:

• Open the query in Design View
• Use the Return option in the Query Setup command group to set the number of records you want to see in the query results and the final report.
• Click Run! to make sure the query results look like you want the report to look.
• Create the report using the Report command on the Create tab

      ·  Format the report as desired.

Grouping Items on a Report

Grouping items on a report can make it much more readable. Microsoft Access 2007 offers a quick and easy way to add grouping to a report.

To Add Grouping to a Report

• With the report open, select the Group & Sort command from the Grouping & Totals command group on the Format tab in the Ribbon.
• This opens a Group, Sort, and Total dialog box in the lower portion of the window.
• In the Group, Sort, and Total dialog box, select Add a group.
• Select the field you wish to group by from the drop down list. We chose to group our list by Category.
• When you release the mouse button, the report will now appear with items grouped. Our report is grouped on Category now, as seen below.

Formatting a Report in Layout View

Access opens the created report in Layout View, so that you can easily make modifications. In Layout View, you can change the look of your report in many different ways, including:

• deleting columns and other report elements
• moving and resizing columns
• adding a logo
• changing the title and other text on the report headings
• applying a report style with AutoFormat
• modifying the page layout

To Delete a Column or Other Report Element

To delete a column or other report element:

• Highlight the element by clicking on it.
• Hit the Delete button on your keyboard.

To Move a Column or Other Report Element

To move a column or other report element:

• Highlight the element by clicking on it.
• Drag and drop the element to a new location on the report.

To Re-size a Column or Other Report Element

To re-size a column or other report element:

• Highlight the element by clicking on it.
• Drag and drop the edge of the element to the new size on the report.

To Add a Logo to the Report

To add a custom picture or company logo to a report:

• Click on the Logo command on the Format tab in the Ribbon.
• When the Insert Picture dialog box opens, find the picture file.
• Click OK.

• Click on the Title command on the Format tab in the Ribbon.
• When the highlight appears, type in the new title.

• Highlight the text you want to change
• Select the formatting option you wish from the lists that appear when you click on a command.
• The change appears when you release the mouse button.

• Click on the AutoFormat command in the Ribbon.
• Select a format from the drop down list. The change is applied instantly.

• Switch to Print Preview using the Views command in the Ribbon.
• Select the layout option you wish to alter from the Page Layout command group on the Ribbon.

Page Layout Option	Description
Margins	To set the margins for narrow, wide, or normal
Orientation	To select either a Portrait or Landscape orientation
Size	To set the paper size

Saving a Report

When you have created and modified a report and try to close it, Microsoft Access 2007 will prompt you to name and save the report. If you do not ever need this report again, you need not save it. However, if you think you may want to publish it again, it is best to save.

To Save a Report

As with all Access objects, to save a report:

• Right click on the report tab.
• Choose Save from the list that appears.
• When the Save as dialog box opens, give the report a name.
• Click OK.

Challenge!

• Create a report based on a table.
• Create a report based on query.
• Modify the layout of a report by:
• Resizing or moving columns
• Deleting report elements
• Giving the report a new title
• Applying an AutoFormat style to the report
• Use groups, sorts, or totals in a report

Introduction

Using Totals in a Query

Sometimes, you may want to see your query results grouped or counted in some way. Access 2007 offers several options that make these functions possible. Perhaps the easiest of these is the Totals command, whose optional functions are very similar to the functions used in Microsoft Excel. These functions include:

• Sum -- used to add a column of numbers.
• Average -- used to find the average of a column of numbers.
• Maximum -- returns the highest value in a field.
• Minimum -- returns the lowest value in a field.
• Count -- used to count the number of same values in a query.

One of the most useful Totals functions to use in queries is the Count function.

Using Count and Group By in a Query

When you use the Totals command in a query, Access will automatically group every field by the values in each field. This just means that it will look for repeating values and group the like values together, so they appear as one record, rather than as many records. This is called the Group By function.
Notice that we get a record back for every order of each book that has been ordered.
In our bookstore example query, we want to see these titles grouped together, so that we see each ordered titles only one time. To do this, we use the Count and Group By options.

To Use the Count and Group By Options in a Query

To include the Group By and the Count functions in a query:

• Click on Totals in the Show/Hide group on the Ribbon.

• Next, click in the Totals row for the field you wish to count. We want to count the number of times the same Book ID appears in the Orders table.
• From the list of optional Totals functions, select Count.

• Click Run! to see your results. Notice that each title, author, price and category is now listed only one time for each book, with an extra column that indicates the number of times this Book ID appeared in the Orders table.

Sorting and Filtering Query Results

Once you have the results looking how you want them, you can sort and filter them to narrow your results down even further. This can be done using the methods of sorting and filtering covered in Lesson 10 and Lesson 11, or by applying the sort and filter in the query design itself.

To Sort via the Query Design

To add a Sort to any field in the query design:

• Click on the Sort row for the field you wish to sort. A drop down list will appear:
• Choose one of the options:
• Ascending will show your results sorted with the lowest numerical value or the text value closest to A first.
• Descending will show the results with the highest numerical value or the text value furthest from A first.
• (not sorted) will keep your records grouped, but not sort them.
• Click Run! to see the results.

To Filter via the Query Design

To add a filter to your query design:

• Click the Criteria row in the query design.
• Add your filter criteria by typing the value in the cell, as we did in the following example:

Challenge!

• Create a query that uses the Count and Group By options.
• Modify a query design to include a Sort
• Run the query and view your results.
• Save the query.
• Modify a query design to include filter Criteria.
• Run the query and view your results.