Lesson 7: Working with Queries

Lesson 7 will guide you through creating and running a query. You will use a query to retrieve information from Training-By-Design's database. The PRODORDS.DB table contains information on past customer orders, the total cost of each order, the date the orders were made, and the date the order was paid. For this lesson, you will use a query to find out which customers have placed orders above $50,000.00, identifying Training-By-Design's larger orders and the customers who placed the orders.

Paradox provides four different methods to create queries for your database. For this lesson you will use the Query By Example (QBE) method. To perform a QBE query, you give Paradox an example of the result you want by selecting conditions and example elements to define the query. 

In this lesson you will

  create a new QBE query
  
  join two tables for the query
  
  specify selection conditions
  
You'll need about 20 minutes to complete this lesson.

To see what your completed query will look like, click here.

Note

  Make sure your working directory is set to the folder where you saved the Tutorial sample files. If necessary, see "Setting your current working directory" in Lesson 2 for more information.
  
Creating a Query

A query is a way to retrieve information from your database. You can query one table or several tables to get the information you need. Paradox finds the records that meet the conditions you specify and presents the results to you in an Answer table. If a query does not obtain the results you want, you can refine the query and run it again. You can also save a query definition to use it again.

For this lesson, you want to retrieve information about product orders and also about the customers that placed the orders. You will create a query based on two tables and then specify selection conditions to refine the query.

To create a new query

1.	Click File menu, New, Query.

2.	In the Select File dialog box, double-click the PRODORDS.DB table.

Paradox displays a query image of the PRODORDS.DB table in the Query window.

Joining two tables 

You want to retrieve information about product orders from the PRODORD.DB table, but you also want to retrieve information about the customers who placed the orders. You must, therefore, join the two tables together to perform a query that will retrieve information from both tables, and create the ANSWER.DB table based on that information. 

To join tables

1.	On the toolbar, click the Add table button.

2.	In the Select File dialog box, double-click CUSTOMER.

CUSTOMER.DB is added to the Query window.

3.	On the toolbar, click the Join button.

You have to join the two tables by common fields. A message in the Status Bar notifies you that you are performing a join. 

4.	Click in the CustomerID field of the CUSTOMER.DB table. 

Paradox places "join1" in the field.

5.	Click in the CustomerID field of the PRODORDS.DB table.

Paradox places "join1" in the field, indicating the two fields are joined, as seen in the following picture:



Specifying selection conditions

For this query, you only want to see records that have a total order cost of $50,000 or more. For the query to find only these records, you must specify selection conditions. You must specify which fields you want Paradox to search, and set certain conditions for the TotalOrderCost field.

To specify selection conditions

1.	In the Query window of PRODORDS.DB, click the checkbox beside the following fields:

  OrderID
  
  CustomerID
  
  TotalOrderCost
  
2.	In the Query window of CUSTOMER.DB, click the checkbox beside the following fields:

  FirstName
  
  LastName
  
  CompanyName
  
3.	In the PRODORDS.DB table, click beside the check box of the TotalOrderCost field.

A cursor appears allowing to specify selection conditions.

4.	Type >=50,000 to specify that you want Paradox to retrieve all records with a TotalCostField of $50,000 or more.

Now that you have set all the selection conditions, you may want to save the query so you can use it again.

To Save the Query

1.	Click File menu, Save As.

2.	In the File name box, type in TBDquery.

Paradox automatically assigns a *.QBE file extension.

3.	Click Save.

Running the Query

You have now entered all the selection conditions for the query. When you run the query, Paradox will retrieve only the records that match the selection conditions.

To run a query

  On the Toolbar, click the Run Query button.
  
Paradox runs the query and generates an ANSWER.DB table. The ANSWER.DB table lists the records of customers who have made orders of $50,000.00 or more. Because of your selection conditions, you can determine the OrderID, CustomerID, Name and Company Name, and the Total Order Cost. Training-By-Design could use this new table to keep a record of its highest ordering clients.

Paradox automatically generated the ANSWER.DB table in your PRIVATE directory. If you want this file in a different directory or with a different name, you must specify the Table name in the Query Properties dialog box.

To save an ANSWER.DB table with a new name

1.	Click File Menu, Close to close the ANSWER.DB table. 

Don't worry about saving this table because you can run the query again and recreate the table. Once you close the table, the Query window is displayed.

2.	Click Query Menu, Properties.

3.	In the Properties dialog box, click the Answer tab.

4.	In the Query answer type section, enable ANSWER table.

5.	In the Table type section, enable Paradox.

6.	Under the Table name box, click the Browse button.

7.	In the Save file as dialog box, use the Up one level button to locate the TUTORIAL folder and select it. The location will depend on where you installed the Tutorial sample files.

8.	In the File name box, type HIGHORDER.DB and Click Save.

9.	Click OK to close the Query Properties dialog box.

Since you made changes to the Query Properties, you must save the Query to retain the changes.

To save the Query

  Click File menu, Save.
  
You can now run the query again and Paradox will automatically generate the ANSWER table as HIGHORDER.DB and save it in the TUTORIAL folder.

Summary of Lesson 7

In this lesson you learned how to

  create a Query By Example query
  
  join two tables in a query
  
  specify query conditions
  
  run and save a query
  
  generate and save an ANSWER.DB table
  
In Lesson 8 you'll use the Launcher Expert to create a small tabbed form. You can use the form to open or launch the forms and reports you created in this tutorial.

Finished Query








