Overview of Northwind

The Northwind sample database provides data that you can experiment with and database objects (tables, queries, 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, distribute, 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 using the queries, 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.

Which objects do you want to read about?

•	Tables

•	Queries

•	Forms

•	Reports

•	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 field; for example, FirstName. A set of facts for one employee makes up one record; for example, facts stored in fields called FirstName, LastName, Address, City, and so on.

Each field has a data type that determines the type of information you can store in it. The FirstName field has a Text data type, because you store names in it. The HiredDate field has a Date/Time data type, because you store dates in it.

Tip   To see a field's data type, open the table in Design view.

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

The Suppliers table

Data type	Used for	Field
Autonumber	Unique numbers that are automatically inserted when you add a record.	SupplierID
Text	Text, a combination of text and numbers (such as addresses), or numbers (such as phone numbers).	CompanyName, Address, Phone, and others
Hyperlink	Text or combinations of text and numbers stored as text and used as a hyperlink address.	HomePage
The Employees table

Data type	Used for	Field
Date/Time	Dates and times.	BirthDate
Memo	Lengthy text and numbers, such as notes or descriptions.	Notes
Text (Lookup)	Text or a combination of text and numbers in a Lookup field. When you enter data, you can choose from a list of values (generated from a list you specified) by using a combo box.	TitleOfCourtesy
Number (Lookup)	Numeric data in a Lookup field. When you enter data, you can choose from a list of values (generated from the values in the same table) by using a combo box.	ReportsTo
OLE Object	Objects such as Microsoft Word documents and pictures that were created in other programs using the OLE protocol.	Photo
The Products table

Data type	Used for	Field
Currency	Currency values.	UnitPrice
Yes/No	Yes/No, True/False, and On/Off values.	Discontinued
Number	Numeric data that can be used in mathematical calculations	UnitsInStock
Number (Lookup)	Numeric data in a Lookup field. When you enter data, you can choose from a list of values (generated from the values in a different table) by using a combo box.	CategoryID
Tip   You can see an overview of all the tables in Northwind, and the relationships between them, by opening the Relationships window. (On the Tools menu, click Relationships.)


Queries in Northwind

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

Tip   To see the structure of a query, open it in Design view.

Which type of query would you like to read about?

•	Queries that use criteria or properties to limit the records retrieved

•	Queries that calculate totals

•	Union and crosstab queries


Union and crosstab queries

Union and crosstab queries bring data from several tables together in ways that a simple select query can't.

Union query

The Customers and Suppliers by City query is based on the Customers and Suppliers tables. It combines corresponding fields from the two tables into one field. As a result, the CompanyName field in the query contains the company names of customers and suppliers. The ContactName and City fields also contain combined data.

Note   You look at the structure of a union query in SQL view, not in query Design view.

Crosstab query

The Quarterly Orders by Product query is based on the Orders, Order Details, and Products tables. For each product, the query shows which customers purchased it, how much of it they purchased, and in which quarter they purchased it.

The query uses expressions in the following fields:

•	The Quarters field uses the DatePart function to return the quarter value of the OrderDate field.

•	The OrderYear field uses the Year function to return the year value of the OrderDate field.

•	The ProductAmount field uses the Sum function to return the total amount of the products ordered and the CCur function to round the currency values consistently.

•	The OrderDate field uses the Between…And operator in its Criteria cell to return only those records from 1997.


Queries that use criteria or properties to limit the records returned

You can use the Criteria cell in the query design grid or query properties to determine the records that the query returns.

Set criteria that doesn't change

The Current Product List query limits the products returned to those that are not discontinued. It specifies No in the Criteria cell for the Discontinued field.

The Sales Totals by Amount query limits the records returned to those that have sale amounts greater than $2,500 and a shipped date in 1997. It specifies >2500 in the Criteria cell of the SaleAmount field and Between #1/1/1997# And #12/31/1997# in the Criteria cell of the ShippedDate field.

Be prompted for criteria each time you run the query

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

The prompts are specified in the Criteria cell of the ShippedDate field:

Between [Beginning Date] And [Ending Date]

The data types for [Beginning Date] and [Ending Date] are specified as Date/Time in the Query Parameters dialog box, which you display by clicking Parameters on the Query menu in query Design view.

Get the criteria values for the query from a custom dialog box

The Sales by Year query is the underlying query for the Sales by Year report and Sales by Year Subreport. It 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.

The references to the text boxes on the form are specified in the Criteria cell of the ShippedDate field:

Is Not Null And Between [Forms]![Sales by Year Dialog]![BeginningDate] And [Forms]![Sales by Year Dialog]![EndingDate]

You specify the data types for [Forms]![Sales by Year Dialog]![BeginningDate] and [Forms]![Sales by Year Dialog]![EndingDate] as Date/Time in the Query Parameters dialog box, which you display by clicking Parameters on the Query menu in query Design view.

Set criteria by using an SQL SELECT statement

The Products Above Average Price query is based on the Products table. It uses an SQL SELECT statement as the criteria for the UnitPrice field. When you use an SQL SELECT statement in the Criteria cell, it's called a subquery.

Set criteria by using the TopValues property

The Ten Most Expensive Products query is based on the Products table. The TopValues property of the query is set to 10, and the UnitPrice field is sorted in descending order; these settings limit the records returned to those with the 10 highest UnitPrice values.


Queries that calculate totals

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

Calculate a total for each record

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

Calculate a total for a group of records

The Order Subtotals query is based on the Order Details table. It is a totals query that groups records by OrderID. The Subtotal field calculates the subtotal for each order by using arithmetic operators to calculate the extended price for a product, the Sum function to total the extended prices for all the products for an order, and the CCur function to round the currency values consistently.

Use the result of one query to calculate totals in another

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

The Products Sales for 1997 query is based on four tables: Categories, Products, Orders, and Order Details. It calculates the total sales by product. It groups by CategoryName and ProductName. The ProductSales field calculates the total sales for each product by using arithmetic operators to calculate the extended price for a product, the Sum function to total the extended prices for all the products for an order, and the CCur function to round the currency values consistently.

The Category Sales for 1997 query is based on the Product Sales for 1997 query. It groups records on the CategoryName field, and calculates the total sales for each category by specifying Sum in the Totals cell of the CategorySales field in the query design grid.


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 query)
Detail area field	Subtotal (Order Subtotals query)


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 query. 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 query and calculates a total for one field, as described in the following table.

Element in PivotTable list	Field in  Product Sales for 1997 query
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.


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


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 in a table or query; 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 database.

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 is a tabular form you can use to view and print customer phone numbers. It 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 those records print.

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 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. It is bound to the Employees table.

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. It uses macros in the Employees (page break) macro group.

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 (page break) 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 form and Orders Subform

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

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 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 select query as the filtername argument of the OpenReport method.)

Orders Subform

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

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 standalone 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 Add Products command button to add new products for the supplier using the Products form.

Suppliers form

Suppliers is a columnar form you can use to add, edit, and delete supplier information. It is bound to the Suppliers table. It displays a hyperlink to some of the suppliers' home pages in the HomePage text box.

The form uses event procedures in the following properties:

•	The event procedure in the OnClick property of the AddProducts 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.

This 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. It 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.

The following properties of the 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 form uses macros, the Sales Reports Dialog form uses 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 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. The macros for this form are in the Customer Labels Dialog macro group.

The form uses macros in the following properties:

•	The Preview macro 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, it displays a message, returns the focus to the SelectCountry combo box, and stops the macro.

•	The Print macro 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, it displays a message, returns the focus to the SelectCountry combo box, and stops the macro.

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

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.

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. It opens the report selected in the ReportToPrint option group and either prints it or previews it, 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 exits 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   Lists products 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 tabular format.

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

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


Catalog report

The Catalog report is a main report with one subreport; it presents information about Northwind products in a format suitable for distribution to customers. It prints the report header on two pages, lists the products in each category, and prints an order form in the report footer.

The main report, Catalog, is based on the Categories table, and displays the name and the description of each category.

In addition to text boxes and labels, the main 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.

In the main report, the properties in the Sorting And Grouping box are set as follows.

Field/ Expression	Sort Order	Group Property
CategoryName	Ascending	GroupHeader:  Yes GroupFooter:  Yes GroupOn:  Each Value GroupInterval:  1 KeepTogether:  Whole Group
The Catalog Subreport report is based on the Products table. In Catalog Subreport, the properties in the Sorting And Grouping box are set as follows.

Field/ Expression	Sort Order	Group Property
ProductName	Ascending	GroupHeader:  No GroupFooter:  No GroupOn:  Each Value GroupInterval:  1 KeepTogether:  No
The following properties are set 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. It is bound to the Employee Sales by Country query.

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

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

Field/ Expression	Sort Order	Group Property
Country	Ascending	GroupHeader:  Yes GroupFooter:  Yes GroupOn:  Each Value GroupInterval:  1 KeepTogether:  With First Detail
=[LastName] & ", " & [FirstName]	Ascending	GroupHeader:  Yes GroupFooter:  Yes GroupOn:  Each Value GroupInterval:  1 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 query 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 query.

•	The PercentOfSalespersonTotal text box in the detail section calculates the percent 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 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 =[LastName] & ", " & [FirstName] footer calculates the percent 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 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. It is bound to Products by Category, a hidden query.

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 GroupFooter:  Yes GroupOn:  Each Value GroupInterval:  1 KeepTogether:  With First Detail
ProductName	Ascending	(Default settings)


Sales by Category report, Sales by Category Subreport

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. Sales by Category Subreport displays product name and sales. Both the main report and subreport are bound to the Sales by Category query. The chart also uses this query 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

The Sales by Category Subreport has only a detail section. It 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)