Starting Off | Beginning Access 2002 VBA (Programmer to Programmer)

As we work through this chapter, we're going to need a report to work on, so we're going to create a report that summarizes the sales data. We're not going to create this with the Report Wizard, because the wizard automatically does some of the things we want to do manually. That's not to say that you should never use the wizard, but if you work through some of the steps you'll understand what it does.

Try It Out-Creating the Query

This report is going to be based on a query, so create a new query, and add tblCompany and tblSales to it:
Click on the Totals button on the toolbar - that's the one that looks like this:
Select CompanyName from tblCompany and add it to the query by double-clicking it. Now place the cursor in the empty Field box in the next column to the CompanyName and type the following:
```
 MonthName: Format([DateOrdered], "mmmm") 
```
Next, add Quantity from tblSales to the query, as the third field. Then place the cursor in the fourth empty Field box and type the following:
```
 MonthNumber: DatePart("m", [DateOrdered]) 
```

Now check and alter the Total and Sort values. Your query needs to be setup like the following:

Field	Total	Sort
CompanyName	Group By	Ascending
MonthName	Group By
Quantity	Sum
MonthNumber	Group By	Ascending

The MonthName and Quantity fields should have empty Sort boxes.

Your query should now look like this:
Save the query as qrySalesSummary .
Run the query - you should have something that looks like this:

Before we explain our query in the How It Works section we should understand what the design grid is actually doing for us behind the scenes. The design grid is giving us a user -friendly way to develop a SQL query . While we drag and drop fields to the grid, behind the scenes the design grid is building a SQL query; it is that query that will run against the database when we call it. To see code of the SQL query either click the designer tool and select SQL View or select View SQL View from the menu and you should see the SQL query code that has been developed, as the following illustrates:

How It Works

Let's just look at those bits of code we typed in, so you'll understand what's happening. In this report we want the company, the month of the order, and the total of the orders for that month, ordered by the company name and then the month. Ordering by the company name is not a problem since we can just sort on it, but the date is a bit of a problem. We want to show the full month name , but sort on the month number.

So, the first field is the company name and we sort by that.
The second field is the month name. We use the Format function to give us the full name of the month:
```
 MonthName: Format([DateOrdered], "mmmm") 
```
The third field is Quantity , which will be the sum of sales for that month.
The fourth field is the month number. Remember from an earlier chapter, where we looked at DatePart - using a format of "m" allows us to just get the month number of the order:
```
 MonthNumber: DatePart("m", [DateOrdered]) 
```

Why do we need this? Well, what we want to show is the sum of the sales for each month, and we'd like the months to be shown in chronological order. However, the month name is a string, and if we sort on the name we don't end up with the correct order, because strings are sorted in alphabetical order. That means that February would come before January, which is not what we want. So we use another field, which is the number of the month, and we sort on that. This gives us the correct ordering.

So we end up with a correctly formatted query. OK, on to the report.

Try It Out-Creating the Report

Now we need to create a new report based on the above query. Don't use the Report Wizard, as we want to create this manually. Select Reports , and then click the New button.
On the New Report dialog, select Design View from the list at the top, and select qrySalesSummary from the drop-down list at the bottom. This is the query the report will be based upon.
Press the OK button to create a blank report.
Click the Sorting and Grouping button. That's the one that looks like this:
Add CompanyName to the Field/Expression list, and set both the Group Header and Group Footer to Yes .
Add MonthNumber to the Field/Expression list. Now close the Sorting and Grouping window:
Now add the fields to the report. To see the fields, select Field List from the View menu. You can just drag the fields from the list and drop them in the appropriate place on the report.
- You need to drag and drop the CompanyName into the CompanyName Header , and MonthName and SumOfQuantity into the Detail section.
- Add a Label in the CompanyName Header above the SumOfQuantity field with its Caption property as Qty .
- You can leave the CompanyName Footer and Page Footer blank for now.
- You might also like to remove the labels for the fields you've just added, so that the report doesn't look cluttered.
- You can also add a label in the Page Header to act as the report heading and change the background colour to accent the header - the report should now look like this:

Save the report as Sales Summary . There's no need to close it, as we'll be using it straight away. There is a voluntary naming convention that recommends that the name of a report is prefixed by 'rpt' and comprises one word, that is, rptSalesSummary . It is a convention that also applies to other Access objects: we have followed it in naming our query ( qrySalesSummary).

While this convention is widely followed there are developers and users that prefer to have the name of the report to be the same as the title of the report as it appears on the actual report; as we have done here: "Sales Summary". Both methods are valid, but when you develop reports for different users you may need to be able to use both styles. So, in this example we show you how to use the voluntary naming convention when we name the query and the alternative when we name the report.

OK, that's the report set up. Let's look at what code we can now use on it.