One of the easiest ways to manipulate your database data is to perform a sort. Sorting allows you to put your database records in a logical order to match any criteria that you specify. For example, with a contacts database, you might want to sort the records alphabetically or based on the zip code. You can sort in ascending order or descending order.
You can either sort records in a table, or you can use a form to sort records. See Chapter 19 to learn more about Accesstables. See Chapter 20 to more learn about forms.
Sort Records
SORT A TABLE
Click Ascending (
Click Descending (
Access sorts the table records based on the field that you choose.
SORT USING A FORM
Click Ascending (
Click Descending (
Note
See Chapter 20 to learn how to navigate records in Form view.
Note
What happens if I have empty records and perform a sort?
If you perform a sort on a field without any data for some of your records, those records are included in the sort. Any empty fields are sorted first when you perform an ascending sort, or last with a descending sort.
How do I remove a sort order?
With the sorted table open, click the Clear All Sorts button (
You can apply a simple filter on one field in your database using the Selection tool, or you can filter several fields using the Filter by Form command.
You can use the filter feature to view only specific records that meet your criteria. For example, you may want to view all of the clients buying a particular product, or anyone in a contacts database that has a birthday in June. You can use an Access filter to temporarily filter out all of the records except those that you want to view.
Filter Records
APPLY A SIMPLE FILTER
Access filters the records.
Note
See Chapter 20 to learn how to navigate records in Form view.
FILTER BY FORM
Access filters the records.
To return the database to its original order, you can click the Toggle Filter button again.
Note
Yes. You can filter out records that do not contain the search criteria that you specify. To do so, first click in the field that you want to filter in the form, click the Toggle Filter Selection button on the Home tab, and then click an exclusion option. Access filters out any records that do not contain the data found in the field that you selected.
What can I do with an Advanced filter?
You can filter by multiple fields, so that you can designate multiple criteria for a filter. Click the Advanced button on the Home tab, and then click Advanced Filter/Sort to open the feature. Start by choosing a primary field for the filter and specifying the criteria, and then enter secondary fields as needed. When you are ready to filter the records, click the Toggle Filter button.
You can use a selection query to extract information that you want to view in a database. Queries are especially useful when you want to glean data from two or more tables. Queries are similar to filters, but offer you greater control over the records that you want to view. You can use the Query Wizard to help you select which fields you want to include in the analysis.
Perform a Simple Query
CREATE A QUERY WITH THE WIZARD
Note
You need at least one table to perform a selection query. See Chapter 19 to learn how to add tables to your database.
The New Query dialog box appears.
The Simple Query Wizard opens.
You can repeat Steps 5 and 6 to choose another table from which to add fields.
Note
When using fields from two or more tables, the tables must have a prior relationship.
Note
What relationships exist between tables?
You can use relationships between tables to bring related information together for analysis. If you create your database using a template, then Access has already defined some table relationships for you. For example, one table might include customer names and addresses, while another table might contain orders placed by your customers. By defining a relationship between the two tables, you can create queries to find all customers ordering the same product. You can click Database Tools and then click Relationships to define relationships between yourtables.
Are there other types of queries that I can create?
Yes. When you display the New Query dialog box, you can choose from several other query types to create a query. For example, the Crosstab Query Wizard helps you to group related information for a summary.
During the process of creating a new query, the wizard asks you to give the query a unique name. All queries that you create are saved in the Queries objects in the Navigation pane. You can open a query in Design view to add criteria to the query.
ADD CRITERIA TO A QUERY
Note
See Chapter 19 to learn how to switch views.
The table now shows only the records matching the criteria.
Note
How do I add another table to my query?
Switch to Design view, click the Design tab on the Ribbon, and then click the Show Table button. This opens the Show Table dialog box where you can add another table to the query and choose from among the available fields to customize the query.
Can I sort or filter my query?
Yes. You can use the sorting and filtering features to further define your query results. To learn how to sort data, see the "Sort Records" task, earlier in this chapter. To learn how to apply a filter, see the "Filter Records" task, also earlier in this chapter.
You can use the Report tool to turn any table, form, or query into a professional report document. The Report Wizard guides you through all of the steps necessary to turn your database data into an easy-to-read printout.
Create a Report
The Report Wizard opens.
The next wizard screen asks how you want to group related data in the report.
The next wizard screen asks you how you want to set the sort order.
You can add more sort fields as needed.
Note
Can I choose different fields from different tables to create a report?
Yes. First choose fields from one table, and then follow Steps 3 and 4 in this task to select another table containing fields that you want to use in the report. You can choose fields from more than one table or query for your Access report.
How do I remove a field that I do not want in the report?
With the first Report Wizard screen open, you can click a field that you previously placed in the Selected Fields list box and click the Subtract button (
As the Report Wizard guides you through the steps for building a report, you are asked to decide upon a sort order and a layout for the report's appearance. After you create the report, you can print it.
The next wizard screen asks you to select a layout for the report.
Note
How do I print a report?
To print a report from Print Preview, click the Print button on the Print Preview tab on the Ribbon. You can also click the Office button and then click Print to open the Print dialog box and assign any printing options before printing the report.
How can I customize a report in Access?
You can further customize a report using Design view. You can change the formatting of fields, move fields around, and more. To learn more about switching to Design view and using the formatting tools, see Chapter 20.
