Building and Saving a Query

Begin by opening the Query Builder Dialog box, which could be done in any of the following ways:

• Click the Create/Edit SQL button on the Database Access toolbar.

• Open the Command Window and type dbEdit at the command prompt.

• From the Origin menu, select File: Database Import: New.

Connecting to a Datasource

If you have not previously connected to your datasource, you will need to set up a connection:

1. From the Query Builder Dialog menu, choose Query: Data Source: New. This opens the Data Link Properties dialog box.
2. Select the OLE DB Provider (Provider tab), then click Next.
3. Select or enter a database name (If the database is not listed, simply type its name).
4. Enter User name and Password.
5. Click OK (other tab controls are optional) to connect to your data source.
6. You may also need to set some SQL syntax options, depending on the type of database being queried, in the Setting: Options menu.

Building a query

Once you have established a connection to your data source, you will see a list of objects to the right side of the Query Builder Dialog box.

1. Drag the desired objects from the Tables pane to the Query Building pane.
2. In the Query Building pane, select the fields that you wish to include in your query. To include all fields from an object, select the "*" box.
3. Use the controls in the Columns pane to remove fields from the output (Output), sort the output (Sort Type and Sort Order), define criteria for the Expression (Criteria), Define multiple Criteria using the Or columns, or define groups (Group By). For more information on use of the Columns pane controls, see The Query Builder dialog box in the Origin Help file..
4. To preview the first 50 lines of your query, click the Show Preview button and click Preview.
5. Click the Import button to import data to your Origin workbook.

Creating a custom query by SQL scripting

With a data source connection, you can create a custom query using SQL scripts. Click the Show SQL button to open the query string dialog box. Then you can enter your own SQL script and click the Apply button. After this, you may see the changes in Column pane and Query Building Pane. If you are satisfied with the query, click the Import button to import data.

Saving a query

You can save a query to a workbook or an ODQ file by selecting either Query: Save to Active Sheet or Query: Save as ODQ File from the menu of the Query Builder dialog box. In addition, clicking the Import button in the Query Builder dialog box will cause the query to be saved in the target workbook.

Saving a query to workbook allows you to preview the data on the workbook and import the data at any time you want. The workbook can be saved as a template, which can also store information on the formatting of the workbook. Next time you open the template, you will have a formatted workbook with a customized query saved in it. Merely clicking the Import Data button on the Database Access toolbar will fill the formatted workbook with data acquired from the data source.

ODQ files can be loaded by clicking the Load ODQ File button on the Database Access toolbar or by selecting Query: Open from the Query Builder dialog box. After an ODQ file is loaded, you can apply the query saved in it to the active workbook. This is useful when you want to apply the query to many different workbooks.