MS Access 2007 Step By Step - Asim Abbasi

Chapter 4: MS Access Reports & Forms

Page #: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

Step 3: Add Grouping Levels If Required

1. The ‘Report Wizard’ then asks for, to add any grouping level. Select any field that you want to appear at the top of each grouped data as shown in Figure 4-3. This will get further clearer once you look at the output of the report. We have selected the ‘MANAGER_ID’ option.

2. Click the button having greater than ‘>’ sign as shown in Figure 4-3 with label 2. This action will add ‘MANAGER_ID’ at the top of the group.

Figure 4-3: MANAGER_ID added as grouping level
________________________________________

3. Finally click the <Next> button.

Figure 4-4: Add the grouping levels in Report Wizard.
________________________________________

Step 4: Add Sort Order If Required

Figure 4-5: Setting up sort order in Report Wizard
________________________________________

1. The Report Wizard then asks for sorting of the report columns. Select the field/column you want to select for sorting purpose as shown in Figure 4-5 with label 1. By default it would be descending but if you want the ascending order then click the <Ascending> button.

2. Finally click the <Next> button.

The wizard provides the option of sorting up till 4 fields. In real world, this is more than enough as we seldom go beyond two and very rarely beyond 3 column sorting. It’s very easy to understand one column sorting but let’s have a look on what really meant with two or more columns sorting. This can be better explained with the help of following example.

Let’s suppose we have a table having FIRST_NAME and LAST_NAME fields. If we sort this table based on FIRST_NAME the information will look like as shown in Figure 4-6.

Figure 4-6: Table sorted based on FIRST_NAME field.
________________________________________

But if you sort first by FIRST_NAME and then by LAST_NAME then the information will look like as shown in Figure 4-7. Have a focus on persons having first name as David in both the cases.

Figure 4-7: Table sorted first by FIRST_NAME field and then by LAST_NAME field.
________________________________________

Here is the bottom line whenever you have multiple column in the ORDER BY clause of the query, the output data will first get sorted based on the first column and if there are similar records in the first column then MS Access looks for the second column in the ORDER BY list to decide which records should be at first place.

While designing query, under every field that you will select, there is a Sort option available as shown in Figure 4-8. You can either have sorting order as Ascending or Descending. The ‘SQL View’ of the query shown in Figure 4-8 will look like as follows.

SELECT [My Test Table].FIRST_NAME, [My Test Table].LAST_NAME
FROM [My Test Table]
ORDER BY [My Test Table].FIRST_NAME;

Figure 4-8: Query Design View has Sort option under each selected field name.
________________________________________

If you select both fields for sorting then the Design and SQL View will look like as shown below respectively.

Figure 4-9: Two fields selected for Sorting.
________________________________________

SELECT [My Test Table].FIRST_NAME, [My Test Table].LAST_NAME
FROM [My Test Table]
ORDER BY [My Test Table].FIRST_NAME, [My Test Table].LAST_NAME;

To switch between Design and SQL view of the query, the starting point is View command on the ribbon as shown in Figure 4-10. The View menu gives the Design as well as SQL view options for a query.

Figure 4-10: View command has both SQL and Design View options.
________________________________________

Chapter 4: MS Access Reports & Forms

Page #: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

Share with others:

products

books

FREE VB, ASP Projects

MS Access 2007 Step By Step - Asim Abbasi