Access Web Apps  «Prev 

Creating a grouping style report by using the Page Wizard

  1. Here you are, starting at the Page list of the Database window. Create a data access page.
  2. You are now in the first screen of the Page Wizard. Specify the company to be selected.
  3. The Company field is now moved over to the Selected Fields list box. Now you need to specify another table from which to pull a field. Specify another table.
  4. Now view a list of fields in the Projects table.
  5. You can now see the list of fields from the Projects table in the Available Fields list. Select the ProjectDescription field.
  6. The ProjectDescription field has now been moved over to the Selected Fields column. You are now done with this screen, so go to the next screen.
  7. Now comes the time to specify the field to use for grouping your page. Specify the Company field.
  8. Move to the next screen.
  9. This screen lets you specify the field or fields on which you want to sort. However, we’re not going to worry about that this time around. Move to the next screen.
  10. Here you are on the last screen once again. Open the finished page.
  11. Exit the wizard.
  12. Here is the finished page opened up. Expand the first client’s projects.
  13. There you go. Now you can see the first of the projects for this client.

Grouping on date intervals

Many reports require grouping on dates or date intervals (day, week, or month). For example, Northwind Traders may want a report of January sales grouped on a weekly basis so that week-to-week patterns emerge.
Fortunately, the Access report engine includes just such a feature. An option in the Group, Sort, and Total task pane enables you to quickly and easily group report data based on dates or date intervals. Just as we grouped data based on prefi x characters in an earlier example, we can group on dates using the group's GroupOn property. Figure 3-6 shows the January sales report grouped by each week during the month. This report is named rptSalesJanuaryByWeek.

Figure 3-6: Northwind Sales January

Set up Report

This report is easy to set up. Open the Group, Sort, and Total task pane again and establish a group for the OrderDate fi eld. Set the OrderDate GroupHeader option to Yes and select the the Group On drop-down list (shown in Figure 3-7). Notice that Access is smart enough to present Group On options (Year, Quarter, Month, Week, and so on) that make sense for Date/Time fi elds like OrderDate. Selecting Week from this list instructs Access to sort the data on the OrderDate, grouped on a week-by-week basis. Note, though, that you still need to sort by the entire value of the OrderDate to ensure that they are in sequential order within the week.

OrderDate is a Date/Time field, so the grouping options are relevant for date and time data.
OrderDate is a Date/Time field, so the grouping options are relevant for date and time data.
Figure 3-7: Group On drop-down list
The label at the top of the group identifying the week (the fi rst one reads Week beginning 1/1/12:) is the product of the following expression in an unbound text box in the OrderDate group header: 
="Week beginning " & [OrderDate] & ":"