Whitepapers - Creating a complex query using MS Query

By Ben Lamb, 4th February 2005

Our previous article Using Oracle data in Microsoft Word showed how to retrieve single table data into Word using the mail merge facility. But to be really useful to us, we want to be able retrieve data from multiple tables based upon business conditions (new starter employment letter, award restructure for a certain group etc).

To achieve this, we need to introduce Microsoft Query. With MS Query, users have the ability to write and run database queries with the results integrating back into Word and Excel.

With MS Query you have the ability to use data from multiple tables and use complex functions like conditions, parameters and calculations. It can also accept pure SQL so you can use existing querys so you don't have to recreate the wheel.

Microsoft Query Wizard

To use Microsoft Query, launch it from Microsoft Excel.

In Excel go to:

Data -> Import External Data -> New Database Query

The pop up box lets you choose your data source



Choose your database and click ok. You will then be asked to connect to the database



By default, the query wizard will launch, allowing you to choose the tables and fields you want to use in your query. Press the "+" next to the table to see the fields. Then just add the fields by clicking the ">" button. Once you have all the fields you want click next.



If you have chosen fields from multiple tables a message will appear saying that you must finish your query using Microsoft Query so you can join the tables together.



If this happens, procede to Creating a complex query using MS Query.

If you didn't get redirected to Microsoft Query you will now be asked to enter any conditions you want for your query. This is done by clicking on the condition and then using the drop down boxes to create your condition.



Once you create all the conditions you want click next.

The data can be sorted on this screen by simply choosing the column(s) you want to sort by from the drop down boxes.



Click next and you will be on the last page of the Wizard.

Here you will be given a few choices. The ones you will choose from are:

• View data or edit query in Microsoft Query - this will let you create a more complex query.
• Return Data to Microsoft Office Excel - This will load the data retrieved by the query into Excel
• Save Query - this will save the query and you will be able to access it through Word.

If you're now ready to use your query, go to Running the Query

Using Microsoft Query for Complex Queries

For more complex queries, the MS Query wizard doesn't cut it. You need to use MS Query tool itself.

Linking tables

To Link two tables together simply find the primary key and then click and drag across to the same field in the other table and a line will appear between them. These lines show that the tables are linked.

Creating Conditions (criteria)

To create a condition go to Criteria->Add Criteria.

From here you will be able to choose the field and the operators from the drop down boxes and either enter the value in manually or click the "Values" button for a list of values. Once the criteria is finished click ok.



Notice that a new section has been added to the page for criterias. Now that this section is here you can drag fields from the tables into the criteria field instead of doing the above method.

Creating parameters

Using the above method for adding criterias, you can create a parameter by entering a parameter question into the Value field instead of an actual value. The parameter question must have square brackets [ ] around it.



Once you have done this press the button to refresh the data and you will be asked to enter a value for the parameter



Enter a valid parameter and click "OK" and your data will change

Running the query

Running the query from Word differs depending on whether or not you used parameters in your query

Or if you didn't use parameters go to the Opening your query in Word.

If you did use parameters go to the Opening your query with parameters in Word.

---

Solid Rock provides Oracle consulting, support and development services. See our Oracle Consulting page for more information.