Sometimes, you may need to query on tables in different databases. Stonefield Query can easily handle this, even if the databases are in different database management systems, such as one being in SQL Server and the other in Access.

This tutorial shows you how to define joins between tables in different databases. It assumes you completed the "creating a project" tutorial for either Access or SQL Server and that project is open in the Configuration Utility. It uses a sample Access database called Tutorial.mdb that comes with the Configuration Utility (in the Tutorial Files subdirectory of wherever the Configuration Utility is installed).

• Add a database to the Northwind project by selecting the "Databases" node in the TreeView and clicking the Create button (). Select ODBC and Connection string and enter a connection string similar to the following:

  driver=Microsoft Access Driver (*.mdb);
  dbq=path\Tutorial Files\Tutorial.mdb

  where path is the directory where the Stonefield Query Configuration Utility is installed.

  Click the OK button to begin the discovery process.

  

• Expand the Tutorial database node, expand the Tables node, and click the CustomersForContacts table. Change the caption to "Other Contacts."

• Expand the CustomersForContacts node and expand the Fields node. Uncheck Reportable for the CustomerID field.

• Expand the Relations node. Click the Create button to create a new relation. Set the child table to "ContactsForCustomer," the parent table to "Customers," and select "CustomerID" as the field for both tables. Set the join type to "Right Outer Join."

  

• Click the Launch Stonefield Query button ().

• Create a new report with the following fields: Company Name from Customers and Contact Name and Contact Title from Other Contacts. When you preview this report, you see other contact information for certain customers and blanks for the rest; if you had used "inner join" for the join type, you'd only see customers that have other contacts.