Data access pages in Northwind

The sample pages in Northwind show how you can use data access pages for entering, editing, viewing, and manipulating your data on the Internet, on an intranet, or within your Microsoft Access application. You can use the sample pages in Northwind to get ideas for pages that you can use for data entry, for viewing data hierarchically, or for analyzing data and making projections.

Tip   To see what makes a data access page work, open it in Design view. You can look at the page's layout and see the settings for the properties that make the page work.

Which data access pages do you want to read about?

Analyze Sales   View sales interactively by using a PivotTable list.

Review Orders   View orders by selecting a country and then a customer from a list. Click the expand indicator to review a particular order.

Review Products   View products by clicking the expand indicator beside a category name.

Sales   Choose a category in the PivotTable list; the product information for that category appears in the PivotTable list and in the chart that is bound to the list.

View Products   Enter, edit, and view complete product information.


Analyze Sales data access page

Analyze Sales is a data access page on which you can analyze sales dynamically by rearranging the sales data in the PivotTable list. The ExpandItemsByDefault property is set to Never for the PivotTable list, so only the summary data is visible when you first open the page in Page view or in Microsoft Internet Explorer. To view the detail records for a customer, click the expand indicator beside the company's name.

The PivotTable list uses five fields from three record sources and calculates two totals, as described in the following table.

Element in the PivotTable list	Field in database  (Record source)
Filter field	ShipCountry (Orders table)
Row field	ShipName (Orders table)
Column field	LastName (Employees table)
Data field	Sum of Subtotal (the Subtotal field serves as the total source field)
Data field	Count of OrderID (The OrderID field is the total source field)
Detail area field	OrderID (Order Subtotals view)
Detail area field	Subtotal (Order Subtotals view)


Review Orders data access page

Review Orders is a data access page that you can use to view customer orders. When the page is open in Page view or in Microsoft Internet Explorer, you select a customer to display a list of all the orders for that customer. You can click the expand indicator beside an order to see the details of the order.

The page has three group levels: The Customers group level is bound to the Customers table; the Orders group level is bound to the Orders table; and the Order Details Extended group level is bound to the Order Details Extended view. This page is read-only because it has more than one group level.

In the Customers header, the CompanyName1 drop-down list box is a group filter control. In Page view or in Internet Explorer, the CompanyName control is hidden; the CompanyName control is used to add the CompanyName field in the Customers table to the data definition of the page. The other controls on the page aren't displayed until after you select a name from the list. The data in the other group levels is displayed in bound HTML controls.

The expand control, record navigation control, and record navigation section have been deleted for the Customers group level. For the other group levels, the record navigation controls contain just the buttons that are useful on a read-only page. The following properties are set to False for the OrdersNavigation and OrderDetailsExtendedNavigation controls: ShowDelButton, ShowFilterBySelectionButton, ShowNewButton, ShowSaveButton, ShowSortAscendingButton, ShowSortDescendingButton, ShowToggleFilterButton, and ShowUndoButton.

The following tables list the relevant property settings for each group record source in the Sorting And Grouping box.

Customers Group Record Source

Property	Setting
CaptionSection GroupHeader GroupFooter RecordNavigationSection ExpandedByDefault GroupFilterControl GroupFilterField	No Yes No No Yes CompanyName1 CompanyName
Orders Group Record Source

Property	Setting
CaptionSection GroupHeader GroupFooter RecordNavigationSection ExpandedByDefault DataPageSize	No Yes No Yes No 5
Order Details Extended Group Record Source

Property	Setting
CaptionSection GroupHeader RecordNavigationSection DataPageSize	Yes Yes Yes 5


Review Products data access page

Review Products is a data access page that you can use to view Northwind products by category. When the page is open in Page view or in Microsoft Internet Explorer, you can click the expand indicator beside a category name to view the products in that category.

The page has two group levels: The Categories group level is bound to the Categories table, and the Products group level is bound to the Products table. This page is read-only because it has more than one group level.

The data on the page is displayed in text boxes and a check box. Where the data type of the underlying field permits, you can sort and filter the values.

The record navigation controls contain just the buttons that are useful on a read-only data access page on which the data is displayed in text boxes. The following properties are set to False for both the CategoriesNavigation and ProductsNavigation record navigation controls: ShowDelButton, ShowNewButton, ShowSaveButton, and ShowUndoButton.

The following tables list the relevant property settings for each group record source in the Sorting And Grouping box.

Categories Group Record Source

Property	Setting
CaptionSection GroupHeader GroupFooter RecordNavigationSection ExpandedByDefault DataPageSize	No Yes No Yes No 3
Products Group Record Source

Property	Setting
CaptionSection GroupHeader RecordNavigationSection ExpandedByDefault DataPageSize	Yes Yes Yes No 3


Sales data access page

Sales is a data access page on which you can view product sales for 1997 in a PivotTable list and a chart. In Page view or in Microsoft Internet Explorer, you can select a category in the PivotTable list to display the product information for that category in the list and the chart. You can also move the row and column fields to change the way that the chart displays data.

The PivotTable list uses four fields from the Product Sales for 1997 view and calculates a total for one field, as described in the following table.

Element in PivotTable list	Field in  Product Sales for 1997 view
Filter field	CategoryName
Row field	ProductName
Column field	ShippedQuarter
Data field	Sum of ProductSales  (the ProductSales field  is the total source field)
Detail area field	ProductSales
The bar chart, bound to the PivotTable list, was created by using the Chart Wizard. The series data, ShippedQuarter, is shown in columns.

Note   If you are connected to the Microsoft SQL Server version of Northwind, the Sales data access page won't work unless you add a calculated column to the Products Sales for 1997 view.

1	Open the Products Sales for 1997 view in Design view.

2	Insert a row in the Grid (Insert menu, Rows command).

3	In the Column field, type the following:

'Qtr ' + DATENAME(qq, Orders.ShippedDate)

4	In the Alias field, type the following:

ShippedQuarter

5	Make sure that the check box in the Output column is selected and that Group By appears in the Group By column.


View Products data access page

View Products is a data access page on which you can view, enter, and edit product information.

There is one group level, which is bound to the Products table.

The data is displayed in text boxes, drop-down list boxes, and a check box.

The record navigation control contains all the buttons on the control.

The following table lists the relevant property settings for the Products group record source in the Sorting And Grouping box.

Property	Setting
GroupHeader RecordNavigationSection DataPageSize	Yes Yes 1


Overview of Northwind

The Northwind sample Microsoft Access project and Microsoft SQL Server database provide data that you can experiment with and database objects (tables, views, stored procedures, forms, reports, data access pages, macros, and modules) that demonstrate Microsoft Access features that you might want to implement in your own databases. By using Northwind, you can become familiar with how a relational database is structured and how the database objects work together to help you enter, store, manipulate, and print your data.

Exploring Northwind

The best way to become acquainted with Northwind is to use it: Enter and edit data in the forms, retrieve records by using the views and stored procedures, and print the reports. Then switch to Design view to look at the structure or layout of the object, and the properties and events that make the form, report, or other object work.

Show Me, the Help system for Northwind, identifies the interesting features of many of the database objects and shows you where to look to find out how the objects work. To view Northwind Help, click the Show Me button on the menu.

Note   To get Microsoft Access Help, click the Office Assistant, or click Microsoft Access Help on the Help menu.

What do you want to do?

•	Read about tables

•	Read about views and stored procedures

•	Read about forms

•	Read about reports

•	Install data access pages

•	Read about data access pages


Tables in Northwind

Each table in Northwind stores information about a particular category of information; for example, the Employees table stores all the information about employees. The information about employees is broken up into facts. Each fact is stored in a column; for example, FirstName. A set of facts for one employee makes up one record; for example, facts stored in columns called FirstName, LastName, Address, City, and so on.

0	Each column has a data type that determines the type of information you can store in it. The FirstName column has a varchar data type, because you store names in it. The HireDate column has a datetime data type, because you store dates in it.

Tip   To see a column's data type, look at the table's Column Properties view in the database diagram.

You can use the columns in Northwind to get ideas for the data types you can use for the columns in your tables. The following tables identify the data types used in the Suppliers, Employees, and Products tables.

Suppliers table

Data type	Used for	Column
int (with the Identity property set)	Unique numbers that are automatically inserted when you add a record.	SupplierID
varchar	Text, a combination of text and numbers (such as addresses), or numbers (such as phone numbers).	CompanyName, Address, Phone, and others
Employees table

Data type	Used for	Column
datetime	Dates and times.	BirthDate
text	Lengthy combination of text and numbers, such as notes or descriptions.	Notes
image	Objects such as Microsoft Word documents and pictures that were created in other programs that use the OLE protocol.	Photo
Products table

Data type	Used for	Column
money	Currency values.	UnitPrice
bit	Yes/No, True/False, and On/Off values.	Discontinued
smallint	Numeric data that can be used in mathematical calculations.	UnitsInStock
Tip   You can see an overview of all the tables in Northwind, and the relationships between them, by opening the database diagram. (In the Database window, click Database Diagrams under Objects, and then click Relationships.)


Views and stored procedures in Northwind

The views and stored procedures in Northwind gather data from two or more related tables, find data that meets criteria, and calculate totals. Some views and stored procedures are used as the basis for a form or report; others are used to manipulate data, the results of which you can display in Datasheet view.

Tip   To see the structure of a view or stored procedure, open it in Design view.

Which type of view or stored procedure would you like to read about?

•	Views or stored procedures that use criteria or properties to limit the records retrieved

•	Views that calculate totals

•	Union views


Union views

Union views bring data from several tables together in ways that a view based on a single SQL SELECT statement cannot.

0	The Customers and Suppliers by City view, which is based on the Customers and Suppliers tables, combines corresponding columns from the two tables into one column. As a result, the CompanyName column in the view contains the company names of customers and suppliers. The ContactName and City columns also contain combined data.

Note   You look at the structure of a union view in the SQL pane in the Query Designer.


Views and stored procedures that use criteria to limit the records returned

You can use the Criteria grid column in Design view of a view, or a WHERE clause in Design view of a stored procedure, to determine the records that the view or stored procedure returns.

Set criteria that doesn't change

The Current Product List view limits the products returned to those that are not discontinued. The Current Product List view specifies =0 in the Criteria grid column for the Discontinued column.

0	The Sales Totals by Amount view limits the records returned to those that have sale amounts greater than $2,500 and a shipped date in 1997. The Sales Totals by Amount view specifies >2500 in the Criteria grid column of the SaleAmount column and Between '19970101' And '19971231' in the Criteria grid column of the ShippedDate column.

Be prompted for criteria each time you run the stored procedure

The Employee Sales by Country stored procedure is the underlying record source for the Employee Sales by Country report. The stored procedure prompts you for beginning and ending dates when you open the stored procedure in Datasheet view or open the report in Print Preview.

0	You define the prompts as parameters in Design view of the stored procedure. You define the data type as datetime for Beginning_Date and Ending_Date when you define the parameters. And you pass the parameter values in the WHERE clause of the SELECT statement:

1	Alter procedure "Employee Sales by Country"
@Beginning_Date DateTime, @Ending_Date DateTime
AS SELECT...
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

Get the criteria values for the stored procedure from a custom dialog box

The Sales by Year stored procedure is the underlying record source for the Sales by Year report and Sales by Year Subreport report. This stored procedure gets the values for its criteria from the BeginningDate and EndingDate text boxes on the Sales by Year Dialog form, which is opened in the Open event of the report when you preview or print the report.

0	You define the beginning and ending dates as parameters in Design view of the stored procedure. You define the data type as datetime for Beginning_Date and Ending_Date when you define the parameters. And you pass the parameter values in the WHERE clause of the SELECT statement:

1	Alter procedure "Sales by Year"
@Beginning_Date DateTime, @Ending_Date DateTime
AS SELECT...
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

2	You assign the parameter values to the form text box controls in the InputParameters property of the Sales By Year report:

3	@Beginning_Date datetime = Forms![Sales by Year Dialog]![BeginningDate] , @Ending_Date datetime = Forms![Sales by Year Dialog]![EndingDate]

Set criteria by using an SQL SELECT statement

The Products Above Average Price view is based on the Products table. It uses an SQL SELECT statement as the criteria for the UnitPrice column. When you use an SQL SELECT statement in the Criteria grid column, that SQL SELECT statement is called a subquery.

Set criteria by using SET ROWCOUNT number

The Ten Most Expensive Products stored procedure is based on the Products table. The SET ROWCOUNT option of the stored procedure is set to 10, and the UnitPrice column is sorted in descending order; these settings limit the records returned to those with the 10 highest UnitPrice values.

0	Note   Alternatively, if you are using Microsoft SQL Server version 7.0 or Microsoft Data Engine (MSDE), you can create a view by using the SQL SELECT statement argument, TOP n, to produce the same results. For example, in the SQL pane in the Query Designer, type:

1	SELECT TOP 10 ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice


Views that calculate totals

In many cases, it's easier and more efficient to calculate totals in a view. You can calculate a total for each record or many records in a view. You can also use the result of the calculations in one view to calculate totals in another view.

Calculate a total for each record

The Order Details Extended view is based on the Order Details and Products tables. The ExtendedPrice column uses arithmetic operators to calculate the extended price for a product and the Convert function to round the currency values consistently.

Calculate a total for a group of records

The Order Subtotals view, which is based on the Order Details table, groups records by OrderID. The Subtotal column alias calculates the subtotal for each order by using arithmetic operators to calculate the extended price for a product, specifies Sum in the Group By grid column to total the extended prices for all the products for an order, and uses the Convert function to round the currency values consistently.

Use the result of one view to calculate totals in another

In some cases, it takes two views to group and total records. The Product Sales for 1997 view calculates the total sales for each product. The Category Sales for 1997 view uses the total sales calculated by the Product Sales for 1997 view to calculate the total sales for each category.

0	The Products Sales for 1997 view is based on four tables: Categories, Products, Orders, and Order Details. This view calculates the total sales by product, and groups by CategoryName and ProductName. The ProductSales column calculates the total sales for each product by using arithmetic operators to calculate the extended price for a product, specifies Sum in the Group By grid column to total the extended prices for all the products for an order, and uses the Convert function to round the currency values consistently.

The Category Sales for 1997 view, which is based on the Product Sales for 1997 view, groups records on the CategoryName column, and calculates the total sales for each category by specifying Sum in the Group By grid column of the CategorySales column alias.


Forms in Northwind

The forms in Northwind show how to make the most of viewing, entering, and editing your data in an attractive and effective online format. You can use the forms in Northwind to get ideas for forms in which you can enter, edit, or view data from a table, view, or stored procedure; dialog boxes in which you prompt for input and then carry out an action based on the input; and switchboard forms from which you can open other forms and reports in your application.

Tip   To see how a form works, open it in Design view.

Which forms do you want to read about?

•	Customer Phone List   View and print phone numbers for all customers or filter company names by first letter by clicking a command button.

•	Employees and Employees (page break)   Add and edit employee information on a tabbed or two-page form.

•	Orders and Orders Subform   Add, edit, or delete orders, including order details.

•	Customer Labels Dialog, Sales Reports Dialog, and Main Switchboard   Use dialog boxes to preview or print reports. Use a switchboard form to open other database objects.

•	Suppliers, Products, and Product List   Add and edit supplier information. Also, click command buttons to display other forms on which you can review the current supplier's product list or enter new products.


Customer Phone List form

Customer Phone List, which is a tabbed form you can use to view and print customer phone numbers, is bound to the Customers table. In Form view, you can filter company names by first letter by clicking a command button in the form footer. When you print the form, the page header and footer are displayed. If you have filtered the records, only the filtered records are printed.

0	The macros used in this form are in the Customer Phone List and Sample Autokeys macro groups.

Note   You must change the name of the Sample Autokeys macro to Autokeys and reopen Northwind in order for CTRL+P to automatically print the report rather than displaying the Print dialog box.

The Customer Phone List form has the following features:

•	The command buttons in the CompanyNameFilters option group in the form footer filter the records in the form by using the Alpha Buttons macro (in the Customer Phone List macro group) attached to the AfterUpdate property of the option group.

•	Labels for the online title and column headings are in the form header.

•	Labels for the printed title and column headings are in the page header.

•	Text boxes for page number and print date are in the page footer.

•	Key combination CTRL+P, which prints the records displayed on the form when pressed. (The ^p macro in the Sample Autokeys macro group maps the key combination CTRL+P to the Print macro in the Customer Phone List macro group.)

The following properties are set so that the form is attractive whether you view it online or print it.

Form area	Property	Setting
Form	ScrollBars	Vertical Only
	RecordSelectors	No
Form header, form footer	DisplayWhen	Screen Only
Detail section	BackColor	16777215 (White)
All text boxes and labels	BorderStyle	Transparent


Employees and Employees (page break) forms

The Employees and Employees (page break) forms show two ways of separating information, either on different tabs or on different pages.

Employees form

Employees is a tabbed form that you can use to add, edit, and delete corporate and personal information about employees. The Employees form is bound to the Employees table.

0	The Employees form has the following features.

•	An expression in the EmployeeName text box concatenates the values of the FirstName and LastName fields. The text box is in the form header, so the name is displayed for both tabs.

•	The event procedure in the AfterUpdate property of the form requeries the ReportsTo combo box after a record has been added or changed.

•	The event procedure in the OnCurrent property makes the palette of the application associated with the objects in the Photo field available to the PaintPalette property of the Employees form. This improves the quality of the display of the photos on the form.

•	The Tab control in the detail section separates employees' corporate information and personal information onto separate tabs.

•	The form's Cycle property is set to Current Page so that the focus stays on the current tab. To switch to a different page, click its tab.

Employees (page break) form

The Employees (page break) form displays the same information as the Employees form, but it uses a page break control to separate corporate and personal information. The Employees (page break) form uses macros in the Employees (page break) macro group.

0	The Employees (page break) form has the following features:

•	The RequeryReportsTo macro in the AfterUpdate property of the form requeries the ReportsTo combo box after a record has been added or changed.

•	The PageBreak control, located below the PersonalInfo command button, divides the detail section into two pages.

•	The event procedure in the OnCurrent property makes the palette of the application associated with the objects in the Photo field available to the PaintPalette property of the Employees form. This improves the quality of the display of the photos on the form.

•	The PersonalInfo macro in the OnClick property of the PersonalInfo command button moves the focus to the second page of the form.

•	The CompanyInfo macro in the OnClick property of the CompanyInfo command button moves the focus to the first page of the form.

•	Access keys are specified with an ampersand (&) in the Caption property of the CompanyInfo and PersonalInfo command buttons.


Orders and Orders Subform forms

The Orders form is a main form with one subform. The main form, Orders, displays order information, which you can add, edit, or delete. The Orders form is bound to the Orders Qry view. The Orders Subform form displays information about the products ordered, which you can also add, edit, or delete. The Orders Subform form is bound to the Order Details Extended view.

Orders form

The Orders form uses expressions in the following controls:

•	The Subtotal text box uses an expression in the ControlSource property to refer to the value of the OrderSubtotal text box on the Orders Subform.

•	The Total text box uses an expression to calculate the total for the order.

•	The OrderDate text box uses Date function in the DefaultValue property to automatically fill in today's date.

The form uses the following settings in form and control properties:

•	The form's Cycle property is set to Current Record so that the focus stays on the current record in the form rather than automatically advancing to the next record. You can use the navigation buttons to switch to a different record.

•	The HyperlinkAddress property of the DisplayProducts command button is set to Products.doc, a Microsoft Word document. The document opens in Word when you click the command button.

•	The ControlTipText property of the CustomerID combo box specifies a tip that is displayed for the control in Form view when the mouse pointer rests on the combo box.

The form uses event procedures in the following properties:

•	The event procedure in the BeforeUpdate property of the CustomerID combo box displays a message if you leave the combo box without adding a value.

•	The event procedure in the AfterUpdate property of the CustomerID combo box sets the values of the controls in the Ship To area of the form to the values of their corresponding controls in the Bill To area of the form.

•	The event procedure in the OnClick property of the PrintInvoice command button prints the Invoice report for the current record. (Specify the view as the filtername argument of the OpenReport method.)

Orders Subform form

The ControlSource property of the OrderSubtotal text box uses the Sum function in an expression to calculate the subtotal for the order.

0	The form uses event procedures in the following properties:

•	The event procedure in the OnError property of the form displays a message and undoes any records entered in the subform if the BillTo combo box on the Orders form doesn't have a value.

•	The event procedure in the AfterUpdate property of the ProductID combo box fills in the UnitPrice value for the selected product.

•	Event procedures in the BeforeDelConfirm and BeforeUpdate properties of the form and in the BeforeUpdate property of the ProductID combo box display a message and undo changes if you open the subform as a stand-alone form and attempt to add, change, or delete records.


Suppliers, Products, and Product List forms

The Suppliers, Products, and Product List forms are a group of forms that work together. From the Suppliers form, you can click the Review Products command button to display the products for the current supplier on the Product List form. You can click the AddProducts command button to add new products for the supplier by using the Products form.

Suppliers form

Suppliers is a columnar form you can use to add, edit, and delete supplier information. The Suppliers form is bound to the Suppliers table. It displays a hyperlink to some of the suppliers' home pages in the HomePage text box. Setting the IsHyperlink property to Yes for the text box makes the text in the HomePage field behave as a hyperlink.

0	The form uses event procedures in the following properties:

•	The event procedure in the OnClick property of the AddProducts command button opens the Products form and sets the OpenArgs property of the form to the current SupplierID. (The Products form uses the value in the property to set the value in the SupplierID combo box if you add a record.)

•	The event procedure in the OnClick property of the ReviewProducts command button opens the Product List form and displays the current supplier's products if there is a value in the SupplierID combo box. Otherwise, it displays a message asking you to select a supplier.

•	The event procedure in the BeforeUpdate property of the form displays a message if the value in the PostalCode text box is invalid for the country specified in the Country text box.

•	The event procedure in the OnClose property of the form closes the Products and Product List forms if they are open when you close the Suppliers form.

•	If the Product List form is open, the event procedure in the OnCurrent property of the Suppliers form automatically synchronizes the values in the Product List form as you move from record to record on the Suppliers form.

Note   The Suppliers macro group duplicates all the functionality of the Sub procedures in the form's module. You can compare the two methods to see the correlation between macros and Sub procedures.

Products form

The Products form is a columnar form you can use to add, edit, and delete product information. It is bound to the Products table.

0	The Products form uses event procedures in the following properties:

•	The event procedure in the OnClick property of the OutputToHTML command button outputs the Alphabetical List of Products report as an HTML document and opens the document in an Internet browser. The event procedure uses Nwindtem.htm, a template for Northwind reports.

•	The event procedure in the AfterUpdate property of the ProductName text box sets the value of the SupplierID combo box to the value in the OpenArgs property of the form, if that property has a value. (The property will have a value only when you open the Products form from the Suppliers form.)

Product List form

The Product List form is a read-only, continuous form that displays limited product information. It is bound to the Products table.

0	The following properties of the Product List form are set to make it read-only and continuous.

Form property	Setting
DefaultView	Continuous Forms
AllowEdits	No
AllowDeletions	No
AllowAdditions	No
DataEntry	No


Customer Labels Dialog, Sales Reports Dialog, and Main Switchboard forms

The three dialog box forms covered in this topic show three different ways to create dialog boxes that carry out actions based on your input. The Customer Labels Dialog and Sales Reports Dialog forms use event procedures, and the Main Switchboard form uses functions in the event properties.

Customer Labels Dialog form

The Customer Labels Dialog form is a dialog box that you can use to preview or print the Customer Labels report. You can preview or print labels for all countries or for a country you select.

0	The form uses event procedures in the following properties:

•	The event procedure in the OnClick property of the Preview command button previews the Customer Labels report. If you clicked the SpecificCountry option button in the PrintLabelsFor option group but didn't select a country, the event procedure displays a message, returns the focus to the SelectCountry combo box, and stops.

•	The event procedure in the OnClick property of the Print command button prints the Customer Labels report. If you clicked the SpecificCountry option button in the PrintLabelsFor option group but didn't select a country, the event procedure displays a message, returns the focus to the SelectCountry combo box, and stops.

•	The event procedure in the OnClick property of the Cancel command button closes the form.

Note   Northwind.mdb, the sample Microsoft Access database, uses macros in this form because the Microsoft Jet database engine can pass parameters in a WHERE clause. NorthwindCS.adp, the Microsoft Access project, uses event procedures instead. Microsoft SQL Server requires the WHERE clause to be a string, so the event procedure creates the WHERE clause string value and then passes it to the report. You can use the Convert Macro Wizard (Tools menu, Macro submenu, Convert Macros to Visual Basic command) to convert a macro to Visual Basic for Applications code.

Sales Reports Dialog form

The Sales Reports Dialog form is a dialog box that displays a list of reports you can preview or print. For the Sales by Category report, you can preview or print sales for all categories or for a category you select.

0	This form uses the following event procedures:

•	The event procedure in the AfterUpdate property of the ReportToPrint option group enables the SelectCategory list box if you selected the Sales by Category report as the report to preview or print.

•	The General section of the form's module contains the PrintReports Sub procedure, which is used in the Preview_Click and Print_Click Sub procedures in the same module. The PrintReports Sub procedure opens the report selected in the ReportToPrint option group and either prints the report or previews the report, depending on which button you click.

•	The event procedure in the OnClick property of the Preview and Print command buttons runs the PrintReports Sub procedure.

•	The event procedure in the OnClick property of the Cancel command button closes the form.

Main Switchboard form

The Main Switchboard form uses a custom function to open Northwind forms and uses event procedures to display the Database window and quit Microsoft Access.

•	The event procedure in the OnClick property of the ExitMicrosoftAccess command button quits Microsoft Access.

•	The event procedure in the OnClick property of the DisplayDatabaseWindow command button closes the Main Switchboard form and displays the Database window with the focus on the first table, Categories.

In the General section of the form's module, the OpenForms custom function specifies the code that is used in the OnClick property of the command buttons that open forms. The OnClick properties of the command buttons in the following table use that function.

Command Button	OnClick property
Categories	=OpenForms("Categories")
Suppliers	=OpenForms("Suppliers")
Products	=OpenForms("Products")
Orders	=OpenForms("Orders")
Sales Reports Dialog	=OpenForms("Sales Reports Dialog")


Reports in Northwind

The reports in Northwind show how to present data effectively in a printed format. You can use the reports in Northwind to get ideas for grouping and sorting data, calculating totals, and arranging data in a format that meets your needs.

Tip   To see how a report works, open it in Design view. You can look at the report's layout, and see the settings for the properties and events that make the report work.

Which report do you want to read about?

•	Catalog   Presents products grouped by category, starting with a two-page introduction and ending with an order form.

•	Employee Sales by Country   Presents sales grouped and calculated by country and employee in tabbed format.

•	Products by Category   Presents products grouped by category in multiple-column format.

•	Sales by Category   Presents category sales in a chart and in tabbed format.


Catalog and Catalog Subreport reports

The Catalog report, a main report with a subreport, presents information about Northwind products in a format suitable for distribution to customers. The Catalog report prints the report header on two pages, groups products by category, and prints an order form in the report footer. The Catalog Subreport report prints the product details. The Catalog report is bound to the Categories table, and the Catalog Subreport report is bound to the Products table.

0	In this case, using the combination of a report and subreport provides better performance than using a view or stored procedure that joins the two related tables together, because the Picture field is retrieved only once each time the report encounters a new group.

1	In addition to text boxes and labels, this report uses the following controls:

•	The HeaderPageBreak control in the report header divides the section into two pages.

•	The LargeLogo, SmallPictureLogo, and NameLogo image controls in the report header and footer display the Northwind logos and name.

•	The Picture bound object frame control in the CategoryName header displays a photo for each product category.

The properties in the Sorting And Grouping box of the Catalog report are set as follows.

Field/ Expression	Sort Order	Group Property
CategoryName	Ascending	GroupHeader:  Yes 5 GroupFooter:  Yes 6 GroupOn:  Each Value 7 GroupInterval:  1 8 KeepTogether:  Whole Group
The properties in the Sorting And Grouping box of the Catalog Subreport report are set as follows.

Field/ Expression	Sort Order	Group Property
ProductName	Ascending	(Default settings)
The following properties are set in the Catalog report so that the information in the report prints correctly.

Report area	Property	Setting
Report	PageFooter	Not with Rpt Hdr/Ftr
Report header	ForceNewPage	After Section
Report footer	ForceNewPage	Before Section
Page header	Height	0
CategoryName header	KeepTogether	Yes
Description text box	CanGrow	Yes


Employee Sales by Country report

The Employee Sales by Country report groups sales by country and employee and calculates totals and percentages for each country and employee. The Employee Sales by Country report is bound to the Employee Sales by Country stored procedure.

0	The report's Picture property is set to Confidential.bmp, which is displayed as a background picture on every page in the report.

1	The properties in the Sorting And Grouping box are set as follows.

Field/ Expression	Sort Order	Group Property
Country	Ascending	GroupHeader:  Yes 7 GroupFooter:  Yes 8 GroupOn:  Each Value 9 GroupInterval:  1 10 KeepTogether:  With First Detail
=[LastName] & ", " & [FirstName]	Ascending	GroupHeader:  Yes 13 GroupFooter:  Yes 14 GroupOn:  Each Value 15 GroupInterval:  1 16 KeepTogether:  With First Detail
OrderID	Ascending	(Default settings)
This report uses event procedures in the following properties:

•	The event procedure in the report's OnNoData property displays a message and cancels previewing or printing the report if there are no records for the dates entered. (The Employee Sales by Country stored procedure prompts for beginning and ending dates.)

•	The event procedure in the OnFormat property of the Country header resets the page number to 1 when a different country group starts.

•	The event procedure in the OnFormat property of the =[LastName] & ", " & [FirstName] header displays the ExceededGoalLabel and SalespersonLine controls when the value in SalespersonTotal text boxes meets the specified criteria.

This report uses expressions in the following controls:

•	The Range text box in the report header prints the beginning and ending dates you entered in the prompts displayed by the Employee Sales by Country stored procedure.

•	The PercentOfSalespersonTotal text box in the detail section calculates the percent that the sale amount for the current order is of the salesperson's total sales.

•	The PercentOfCountryTotal text box in the detail section calculates the percent that the sale amount for the current order is of the country's total sales.

•	The SalespersonTotal text box in the =[LastName] & ", " & [FirstName] footer uses the Sum function to calculate the total sales for the salesperson.

•	The PercentOfCountryTotal2 text box in the Country footer calculates the percent that the employee's sales are of the country's total sales.

•	The CountryTotal text box in the Country footer uses the Sum function to calculate the total sales for the country.

•	The PercentOfGrandTotal text box in the Country footer calculates the percent that the country's sales are of the grand total.

•	The GrandTotal text box in the report footer uses the Sum function to calculate the total sales for all countries.


Products by Category report

Products by Category is a multiple-column report that starts each category of products in a new column. The Products by Category report is bound to the Products by Category view.

0	Controls in this report have the following features:

•	The NumberOfProducts text box in the CategoryName footer uses the Count function to calculate the number of products in a category.

•	All text boxes and labels for group information and detail records are located within the leftmost 3 inches of the report design. (The use of the leftmost 3 inches in group header and detail section corresponds to the width specified in the Page Setup dialog box. The height of detail section matches the setting in Page Setup dialog box. See the following table for details.)

The following properties are set in the Page Setup dialog box.

Tab	Property	Setting
Page	Orientation	Landscape
Columns	Number Of Columns	3
	Column Spacing	0.25"
	Width	3"
	Height	0.25"
	Same As Detail	Cleared
	Down, Then Across	Selected
The following properties are set for sections of the report.

Report area	Property	Setting
Report header	BackColor	12632256 (Light gray)
Page header	BackColor	12632256  (Light gray)
	Height	0.938"
CategoryName header	NewRowOrCol	Before Section
The following properties are set in the Sorting And Grouping box.

Field/ Expression	Sort Order	Group Property
CategoryName	Ascending	GroupHeader:  Yes 37 GroupFooter:  Yes 38 GroupOn:  Each Value 39 GroupInterval:  1 40 KeepTogether:  With First Detail
ProductName	Ascending	(Default settings)


Sales by Category and Sales by Category Subreport reports

The Sales by Category report is a main report with one subreport. The main report, Sales by Category, displays the category name and a sales chart. The Sales by Category Subreport report displays product name and sales. Both the main report and subreport are bound to the Sales by Category view. The chart also uses this view as its record source.

Sales by Category report

The controls in the Sales by Category report have the following features:

•	Because the detail records are printed in the Sales by Category Subreport control, there are no controls in the detail section and its Height property is set to 0.

•	CategoryID, a hidden control, serves as the linking field between the chart and the main report.

•	The ProductLabel and SalesLabel controls are the column headings for the data in the subreport.

•	The CanGrow property of the Sales by Category Subreport control is set to Yes.

•	The bottom edges of the Sales by Category Subreport control and the Graph10 chart control align so that the subreport won't grow until it has filled the amount of space equal to the height of the chart beside it.

The following properties are set in the Sorting And Grouping box.

Field/ Expression	Sort Order	Group Property
CategoryName	Ascending	GroupHeader:  Yes (Other properties are default settings)
Sales by Category Subreport report

The Sales by Category Subreport report has only a detail section. The detail section is just high and wide enough to contain the ProductName and ProductSales text boxes.

The following properties are set in the Sorting And Grouping box.

Field/ Expression	Sort Order	Group Property
ProductName	Ascending	(Default settings)


Add sample data access pages from Northwind.mdb
Add sample data access pages

The NorthwindCS.adp sample Microsoft Access project that you installed with Microsoft Access 2000 doesn't contain the sample data access pages. To add the sample pages, import the pages from the Samples Folder and update the connection information for each page.

1	Import the pages from the Samples Folder.

How?

2	Update the connection information for each page.

How?


Import data access pages from Northwind.mdb
Import data access pages from the Samples Folder

1	On the Insert menu, Select Page.

2	In the New Data Access Page dialog box, select Existing Web Page and click on the OK button to invoke the Locate Web Page dialog box.

3	In the Look In box, click the drive on your machine where you installed Microsoft Office 2000 or Microsoft Access 2000.

4	In the Program Files\Microsoft Office\Office\Samples folder, double-click on a CS Data Access Page (identified by the letters "CS" at the end of the file name).

5	Repeat the Steps 1 to 4 to import the others CS Data Access Pages.


Update data access page connection information

Follow these steps for each data access page you've imported from the Samples Folder.

1	In the Database window, click Pages  [image]  under Objects, and then click the imported page.

2	Click Design.

3	In page Design view, click Field List  [image].

4	In the field list, right-click the database icon, and then click Connection.

5	In the Data Link Properties dialog box on the Provider tab, click Microsoft OLEDB Provider for SQL Server.

6	On the Connection tab, enter the appropriate information in the dialog box. At a minimum, you need to:

Type the name of the server where the NorthwindCS Microsoft SQL Server database is stored (for example, the name of your local computer).

Type a user name to log on to the database (for example, sa).

Type the name of the NorthwindCS database (by default, NorthwindCS).