Ms Access # 14

Introduction

Now that you know how to use queries to analyze the data in a database, it is time to find out how to create a report that will make the data meaningful to someone else. This lesson will show you how to create a report using the Report command. It will also show you how to use grouping options and query limits to make the report easier to read, as well as identify several report formatting and layout options that can be set in Layout View. Finally, you will see how to use Print Preview and how to save the report.

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.


Report from Books Table


• 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.

Book Price List

The layout and formatting of the report can be manipulated in Layout View.

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.


Return Limit


• 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

To add a level of 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.


Grouping Command for Reports


• This opens a Group, Sort, and Total dialog box in the lower portion of the window.


Group, Sort, and Total Dialog Box


• 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.


Grouping Drop Down List


• When you release the mouse button, the report will now appear with items grouped. Our report is grouped on Category now, as seen below.

Books Price List Grouped on Category

The Group, Sort, and Total dialog box will remain open until you close it.

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.


Logo Command for Report


• When the Insert Picture dialog box opens, find the picture file.
Insert Picture Dialog Box


• Click OK. 

To Modify the Title of the Report

To modify the title of a report:

• Click on the Title command on the Format tab in the Ribbon.


Title Command for Report


• When the highlight appears, type in the new title.

To Modify Text in Report Headings

If you don't like the standard font face and size that Access used to create your report, you can modify them using common Microsoft Office text formatting commands. You can modify the size, font face, font color, alignment, and much more. They all work basically the same way:

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


Text Commands for Report


• The change appears when you release the mouse button.

To Apply an AutoFormat Style

Like with forms, Microsoft Access 2007 offers a variety of report styles in the AutoFormat command. To apply a style:

• Click on the AutoFormat command in the Ribbon.


AutoFormats for Report


• Select a format from the drop down list. The change is applied instantly.

To Change the Page Layout

When a report is created, it opens in Layout View, like the one in the picture below. The dotted lines are showing where the edge of the page will be in Report View.

Report in Layout View

To change the page layout options:

• Switch to Print Preview using the Views command in the Ribbon.


Print Preview Command


• Select the layout option you wish to alter from the Page Layout command group on the Ribbon.

Page Layout Options for Report

All of the standard Microsoft page layout options are available, including:

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

To Modify the Title of the Report

To modify the title of a report:

• Click on the Title command on the Format tab in the Ribbon.


Title Command for Report


• When the highlight appears, type in the new title.

To Modify Text in Report Headings

If you don't like the standard font face and size that Access used to create your report, you can modify them using common Microsoft Office text formatting commands. You can modify the size, font face, font color, alignment, and much more. They all work basically the same way:

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


Text Commands for Report


• The change appears when you release the mouse button.

To Apply an AutoFormat Style

Like with forms, Microsoft Access 2007 offers a variety of report styles in the AutoFormat command. To apply a style:

• Click on the AutoFormat command in the Ribbon.


AutoFormats for Report


• Select a format from the drop down list. The change is applied instantly.

To Change the Page Layout

When a report is created, it opens in Layout View, like the one in the picture below. The dotted lines are showing where the edge of the page will be in Report View.

Report in Layout View

To change the page layout options:

• Switch to Print Preview using the Views command in the Ribbon.


Print Preview Command


• Select the layout option you wish to alter from the Page Layout command group on the Ribbon.

Page Layout Options for Report

All of the standard Microsoft page layout options are available, including:

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

Challenge!

If you haven't already done so, save the sample Ready2Read database on your own computer.


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

Ms Access # 13

Introduction

You already know how to plan and run a basic query. This lesson will show you how to run a query that includes a Totals function to group and count the records in the results. It will also deal with ways to further sort and filter via your query design to refine the results even more. 

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.
Take our bookstore database for example. If we run a query to see the information for every book that has been ordered, we'd get a list that looks like this:

Query for Ordered Books

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.
  
  
  Totals Command
  
  The Total row will instantly appear in the bottom portion of the query design screen.



Total Row in Query Design



• 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.
  
  
  Total Options in Query Design
  
  
• 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.
  
  
  Count and Group By Functions in Query Results

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:
  
  
  Sorting via Query Design
  
  
• 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:
  
  
  Filtering via Query Design
  
  
• Click Run! to see the results. 

Challenge!

If you haven't already done so, save the sample Ready2Read database on your own computer.


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.

Save the query.

Ms Access # 12

Introduction

The real power of an Access 2007 database is in the ability to pull data for quick analysis, which is what happens when you run a query. Queries allow you to retrieve information from one or more tables based on a set of search conditions you define. Access 2007 will display your results in their very own table that you can analyze and manipulate further. This lesson will explain how to plan a query using a three-question planning process. You will learn how to use the Query Design command to run the query, as well as how to modify the query to hide fields or other information in your query results. Finally, it will show you how to save the query for later use. 

Using Queries

Queries retrieve information from one or more tables based on a set of search conditions that you set up and then combine that information in a way that is easy for you to analyze. If you have used an Advanced Filter in Access 2007, then you have already run a very basic query on only one table. If you want to pull data from more than one table, though, you will need to use either the Query Design command or the Query Wizard.
Before using the Access 2007 query tools, it is important to plan out the query using a logical process. Otherwise, you may not get the results you expect.

Planning a Query

There are three questions you need to answer when you are planning a query:

• What do you want the results to look like? Identify every field or bit of information that you want included in the results.
• Where is the information stored in the database? List which tables (and/or queries) hold the information that you want to see.
• What conditions do you want the data to meet? This helps determine how to set the criteria so Access can search the records properly.

Planning: Which Customers Ordered Technology Books?

Let's think about this process for our bookstore database scenario. We have new technology series coming out soon, and we want to send coupons to customers that have ordered technology books from us in the past. A query can help us answer the question: which customers have ordered technology books from us already? Let's use the three-question process to plan this query.

• What fields do we want to see in the results? We need a list of customer names and addresses in order to mail the coupons to our customers, so we'll need the results to show:
  
  
  Fields in Results
  
  
• In which tables is the information stored? Well, for this query we'll need:
  • Customers table - to get the customers' names and addresses
  • Books table - to know which books are technology books
  • Orders table - to know which customers ordered those books.
• What is the condition we want the data to meet? We want Access to look for only the books where the book's category is technology.

Criteria of the Query Condition

 

Using the Query Design Command

Once you have planned out your query, you can build and run it using Access 2007's query tools.

To Build a Query using the Query Design Command

To build and run a query using the Query Design command:

• Select the Query Design command from the Create tab on the Ribbon.


Query Design Command


• Use the Show Table dialog box to select which tables (and/or queries) to include in the query. Our plan called for all three tables.
  
  
  Show Table Dialog Box
  
  
• Drag and drop the fields you want to see in your results to the bottom portion of the query design screen.



Add Fields to Query Design Screen



• Enter the condition in the Criteria row for the condition field. For our query, we typed Technology in the cell labeled Criteria for the Category field. As seen above, Access 2007 puts quotation marks around the term to show it is looking for exactly that term within the designated field.
  
  
• Once the condition is set, click Run! in the Results group on the Ribbon.


Run Query Command


• Finally, view your results to determine if they match your desired results.
  
  
  Customers of Tech Books Query Results
   

  Hiding Fields or Other Information in the Results

  
  Sometimes the results of a query will include information that is seemingly unnecessary to you. Access 2007 allows you to easily hide these fields.
  

  To Hide Part of the Query Result

  To hide a field in your query results:
  

  • In the query design window, de-select the Show option by clicking on it.
  
  
  Show/Hide Field
  
  
  • Now, when you run your results, that field will be hidden, as seen below.
    
    
    Query Results with Category Field Hidden
     

    Saving the Query

    
    Sometimes you will not need to save your results or your query design. Other times, you may want to keep it to run again later or to modify it slightly. Saving a query is very easy to do.
    

    To Save a Query

    To save the query for later use:
    

    • Right click on the query tab.
    • When the Save As dialog box opens, give your query a meaningful name.
    
    
    
    Saving the Query
    
    
    • Click OK.
    • The query will now be listed in the object list on the left side of the Access window.

    Challenge!

    
    

    If you haven't already done so, save the sample Ready2Read database on your own computer.
    

    Plan a query to find out which customers order a certain category of books.

    Use the Query Design command to set up the query.

    Run the query and view your results.

    Save the query.

    Modify the query to hide a field.

     

   

 

Ms Access # 11

Introduction

Another useful way to look at, or analyze, the data in the database is by filtering it. Filtering groups your data together based on one or more criteria for a given field, then displays only the records that contain those specified values and criteria. Filtering data is easy with Access 2007. This lesson will show you how to filter records using common filter commands. You will also learn how to use the toggle button to switch between filtered and unfiltered results, and to perform advanced filters, to save the filtered results, and more. 

Filtering Records

When you tell Access 2007 to filter your records, you are asking it to:

• Search all the records in one table
• Find every record in that table that meets the criteria you set
• Display the results for you in the table by hiding the records that do not meet the criteria

You can filter data using what Microsoft calls the Filter by Field and Filter by Selection methods. Additionally, Access 2007 can perform an Advanced Filter, which is really a small query that is run on only one table. These options are described in the table below.

Method	Description
Filter by Field	Lists all of the values that have been entered in a specified field, so that you can choose which value or values you want Access to find.
Filter by Selection	Offers the ability to search records based on the value that is currently selected in a table. You can ask it to find records that: Equal the value Do not equal the value Contain the value Do not contain the value
Advanced Filter	Narrows filtered results further by performing additional filters or sorts.

Filter by Field

When you Filter by Field, Access 2007 finds all of the values that have been entered in the specified field. Access lists all these values for you, so that you can choose which value or values you want Access to find for you within that field. Once you choose which value to filter by, Access returns the results to you as a subset of records in the table.

To Filter by Field

To Filter by Field:

• Highlight the column for the field you want to use as a filter. In the example below, we are filtering using the State field.
• In the Sort and Filter group in the Ribbon, click on the Filter command. It looks like a funnel.


Filtering by Field


• When the dialog box appears, select the value that you want to use as the filter value:
  • Checking Select All will return every record in the table. However, clicking on this option when the all values are checked will clear the selections.
  • Checking Blank will find only records in which the field is empty.
  • Check as many of the values as you would like to see returned in your results.
• Click OK.

Results for Filter by State for NC Customers

The results will appear in the table. In the example above, the results are showing all records for customers living in NC. Notice that all other records are hidden from view, but that the table name is still Customers.

To Toggle Between Filtered and Unfiltered Results

As with most actions in Access, you can toggle between your filtered results and unfiltered tables in a variety of ways:

• Use the label that appears in the navigation bar, as seen above.
• Use the Toggle Filter button on the Ribbon


Toggle Filter Button

Filter by Selection

When you Filter by Selection, Access 2007 returns the results to you as a subset of records in the table. It assumes that your current selection is what you want to use as your filter value, or criteria.

To Filter by Selection

To filter by selection:

• Place your cursor in the cell that contains the value you want to use as your filter value.
• Click on the Selection command in the Sort & Filter group in the Ribbon.
• When the list appears, choose one of the options:
  • Equals will return all records that have exactly the same value as your selected value.
    In the example below, this option would return records for the city of Raleigh only.
  • Does Not Equal will return every record that does not have the same value as your selected value. In the example below, this option would return all records except for the ones with Raleigh as a city.
  • Contains will return every value that has your selected value somewhere in it. In the example below, choosing Contains would return records with a city value of Raleigh, Caraleigh, or Raleighville.
  • Does Not Contain will not return records with the selected value anywhere within the search field. In the example below, clicking Does Not Contain would return all records except for records that have Raleigh in the city name -- Raleigh, North Raleigh and Raleighville, for example, would not show up in the results.

Filtering by Selection of Raleigh

Sorting or Filtering the Filtered Results

Access 2007 lets you narrow the filtered results even more by applying another level of sorting or filtering. To do this:

• In the table showing your filtered results, select the field or value you wish to use as the basis for a deeper sort or filter.
• Apply the additional filter or sort.

Another way to do the same thing is to use the Advanced Filter command.

Advanced Filters

An Advanced Filter is very similar to a multilevel sort in Microsoft Excel. An Advanced Filter can really help you narrow down your records. This is like running a miniature query on only one table. It uses a screen very similar to the query design screen, and can even save your results as a query so you can use them again later. Queries are covered in more detail in other lessons.

To Apply an Advanced Filter

To apply an Advanced Filter to your table:

• Click on the Advanced command in the Sort & Filter group in the Ribbon.
• From the menu that opens, select the Advanced Filter/Sort option.
• The design screen will open so that you can set up your filter criteria and/or sorting order for the various fields in the table.


Advanced Filter Design Screen


• To sort or filter another field, drag the field name from the table at the top of the screen to the design portion of the screen in the bottom half of the window. Then:
• To sort the new field:
• Click in the Sort cell for the field.
• Choose the sort option you want to use from the drop down list that appears.
• To filter the new field:
  • Click in the Criteria cell for the field.
  • Enter your criteria by directly typing it into the cell. Access will put quotation marks around your criteria, showing that it is searching for that exact value.
Important: Access will perform the sorts left to right, according to the way they are displayed on the Advanced Filter design screen. Once all the sorts are completed, Access will perform the filter functions. Like the sorts, Access filters from left to right, based on the order displayed on the Advanced Filter design screen.

To view the results, click on the Toggle Filter button on the Ribbon.

Your filtered results will appear in a new table. You can save the advanced filter, in the event that you may want to run it again later.

To Save the Advanced Filter

To save the Advanced Filter:

• Right click on the Advanced Filter design tab.
• Select Save.
Saving an Advanced Filter


• If the Save as Query dialog box comes up, you must give the advanced filter a name.
  This dialog box will only appear the first time you save.

Access 2007 saves Advanced Filters as queries, because they are very simple queries run on only one table.

Challenge!

If you haven't already done so, save the sample Ready2Read database on your own computer.


Use the Filter by Field method to filter the Customers table to find all customers from NC.

Use the Filter by Selection method to filter the Customers table, looking for all NC customers NOT in Raleigh.

Hint: Use the Does Not Equal option.




Practice toggling between the filtered results and the unfiltered tables.

Apply an Advanced Filter on the Books table to find all the Kids books, sorted on price with the highest price first.

Save your Advanced Filter with whatever name you choose

Ms Access # 10

Introduction

Once a database is populated, it is time to think about how to look at, or analyze, the data. One very basic way to analyze data is to sort it. Sorting data is easy with Access 2007. This lesson will show you how to sort text-based and numeric data using common sorting commands. It will also show you how to clear sorts. 

Sorting Records

Sorting is probably the simplest way to look at data; it keeps like things together. In our bookstore database, for example, we could sort our data a number of ways:

• 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.


Sorting A to Z


• 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.
  
  
  Records Sorted A to Z on Category
  
  
  • 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.
  The sort commands in the Sort group on the Ribbon is another way to begin the sort.

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.


Sorting Largest to Smallest


• 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.
  
  
  Records Sorted Largest to Smallest on Price
  The sort commands in the Sort & Filter group on the Ribbon can be use to begin the sort, as well.

  To Clear a Sort

  
  To clear your sort:
  
  • Click the Clear Sort command in the Sort & Filter group on the Ribbon.

Clear All Sorts Command

 

Challenge!

If you haven't already done so, save the sample Ready2Read database on your own computer.


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.