Microsoft Access - Stonefield Query SDK

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 Stonefield Query Studio and create a new project by clicking the New Stonefield Query Project button ().
Enter a project name (for example, "Microsoft Access Sample"), leave Short name at the default (same as Project name), and specify a folder for the new project's files. Click Next.
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 Sample Project folder of the Stonefield Query program directory (for example, C:\Program Files\Stonefield Query SDK\Sample Project).

Click the Finish button to create the project and 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 in the Databases panel. Click the + in front of the Northwind node 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, click the Special button in the properties pane and turn on 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.

Click the Calc button 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 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. Click the Special button and turn on the Display Field From Related Table option, then select Categories from the Table drop-down list and CategoryName from the Field drop-down list.
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 report 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.