Microsoft Access

This tutorial leads you through the process of creating a version of Stonefield Query for the Northwind sample database that comes with Stonefield Query.

Start the Stonefield Query Configuration Utility and create a new project by clicking the New Stonefield Query Project button ().
Add the Northwind sample database that comes with Stonefield Query to the data dictionary. Start by selecting the "Databases" node in the TreeView and clicking the Create button (). Enter "Northwind" for the caption. Because you don't have an ODBC data source set up for this database, select ODBC and Connection string and enter a connection string similar to the following:
```
driver=Microsoft Access Driver (*.mdb);dbq=path\Northwind.mdb
```
where path is the location of the Northwind.mdb file in the Samples folder of the Stonefield Query program directory (for example, C:\Program Files\Stonefield Query SDK\Samples).
Click the OK button to begin the discovery process.
During the discovery process, you're notified that the Employees table is involved in a self-join and asked to enter the name of a subtable to create. Enter "Managers" and choose OK.
Stonefield Query also finds there are two relationships defined between the Orders and Employees tables. Enter "ShipEmployees" as the subtable name and click OK.
If the discovery process was successful, you'll see the Northwind database under the "Databases" node in the TreeView. Click the + in front of the Northwind and Tables nodes to see the tables in this database.
Click the + in front of the Orders table, then click the + in front of the Fields node, and click the ShipVia field. This integer field is really an enumerated field; let's assume that 1 means Fedex, 2 means UPS, and 3 means Mail. To define ShipVia as an enumerated field to Stonefield Query, select the Special page in the right side of the Configuration Utility and check the Enumerated Values option. Then, click the Add button to add a new value to the list, and in the grid, enter "1" in the Value column and "Fedex" in the Description column. Do the same for 2 (UPS) and 3 (Mail).
Because the extended price for an order item is a derived value (unit price multiplied by quantity), it isn't stored in the database. However, the user may want to query on that, so let's create a calculated field for it. Expand the Order Details table, expand the Fields node, and click the Create button to create a new field. Enter "total_price" for the name, set the data type to Currency, enter "Total Price" for Caption, set Format to "$," and enter "999,999.99" for Picture.
Select the Calc page and enter the following for Output Expression:
```
UnitPrice * Quantity
```
Turn off the Stonefield Query Expression option, since this expression can be evaluated by Access.
Click "newfield" in the TreeView to save this change. You'll see the name in the TreeView change to "total_price."
Click the + in front of the Products table, then click the + in front of the Fields node, and click the CategoryID field. This field contains foreign key values to the Categories table, so we would rather display the CategoryName field from that table than the foreign key values. Select the Special page in the right side of the Configuration Utility and check the Display Field From Related Table option, then select Categories from the Table drop-down list and CategoryName from the Field drop-down list.
Expand the Configuration node and click Application. Select the Application Name property and enter "Stonefield Query for Northwind" as the value.
Click the Launch Stonefield Query button ().
Since this is the first time Stonefield Query for Northwind is run, the Setup dialog appears. Leave the Location of Stonefield Query files option at the default (the same directory where you created the Stonefield Query project) and click the Finish button.
Create a new report with the following fields: Company Name from Customers; Order ID, Order Date, and ShipVia from Orders; Quantity, Unit Price, and Total Price from Order Details; and Product Name from Products. When you preview this report, you'll see that ShipVia is displayed as Fedex, UPS, or Mail rather than 1, 2, or 3 and that the Total Price for each item is shown.