5. QUERYING THE DATABASE
(1) The Ask Option
The Ask option on the main menu is used to ask questions about, and to manipulate, the data held in Paradox tables.
The questions asked are called Queries. Queries can be asked of one table, or many tables within one query.
In a query the following can be chosen:
- Which table to be queried
- Which fields to be displayed in the answer table
- Which records will be placed in the answer table
- What Calculations to be performed on data in the tables
The method of asking questions of databases is called Query by Example. Within queries an example of the data you require is entered, Paradox picks up the example and automatically finds the fastest way of getting the answer or performing the operation.
Queries are flexible, interactive and iterative. If a query does not provide the required results the first time, it can easily be fined tuned and performed again. By constructing queries that build on each other, "What if?" Queries can be performed on your data.
(2) Selecting Columns
Choose F10 Ask from the main menu and choose the required table to query. A blank query form will be displayed which looks similar to the DataEntry table. It will include all of the fields from your table, but none of the records, often referred to as a skeleton.
When the query is performed an Answer table will be displayed which will look similar to the original table. However, it will only have the fields and records you selected. To select a column (Field) use the arrow keys to move to the required column and press the F6 (Checkmark) key. As many or as few fields as you wish can be selected by this method. However, if all fields are to be selected then press F6 in the record no. column. The Checkmark key is a toggle switch, this means that pressing F6 a second time will turn the checkmark off.
NOTE The Checkmark key will only select one example of each criteria requested. Therefore if only one field is to be selected use Alt F6 to give all examples of the criteria selected.
(3) Setting Conditions
Having Checkmarked various fields, type the required selection criteria in the relevant field. For example if you want to know which customers live in Essex, type Essex in the County Column. Nb. The example must be spelt correctly with the correct case. ESSEX and essex would not be correct.
(4) Performing the Query
Once the query form has been completed, perform the query by pressing the F2 (DO-IT!) key.
The resulting answers will be put in an Answer table which is a temporary table. The Answer table is stored on the hard disk, but the information held within it will be changed and updated each time a new query is performed.
To print the results of an Answer table place the cursor on the Answer table and press Alt F7 for a quick report.
To save the Answer table, rename it to a new name so that the table can be used later to produce reports. To save choose F10 Tools Rename, the current name of the table is Answer, type the new name for the table and press <Enter>.
(5) Querying with Range Operators
The simple queries above only allow for simple questions, such as which clients live in London (eg. County = London) or Which clients have a credit limit of #500 (eg. Credit Limit = 500).
Paradox queries can be built up to ask much more complicated questions such as questions involving a range of information (eg. who has a credit limit of more than #500 but less than #2000) or multiple questions within one query (eg. Who lives in London AND has a credit limit of #500). To perform these more complicated queries the following Operators are needed.
Ranges can be specified on a query skeleton by preceding a value with One of the following Range Operators:
| Operator | Meaning |
| = | Equal to (DEFAULT) |
| > | Greater than |
| < | Less than |
| >= | Greater than or Equal to |
| <= | Less than or Equal to |
(6) Using Range Operators
To perform the Query "Who has a credit limit greater than or equal to #500 in the Client table":
a. Choose F10 Ask client <Enter>, and the client skeleton will be displayed on the screen.
b. Choose the Company Name and the Surname fields to be placed in the answer table (move to first field and press F6 to checkmark the field and then repeat for further fields).
c. Place the example query in the correct field (move to the Credit Limit field and type >=500).
d. Press F2 (DO-IT!) to perform the query and the answer to the question will be placed in the Answer table.
The range operators can be used with other field types for example to find all companies after S in the alphabet type >S in the Company Name field, or to find all orders after 1st January 1993 type >=1.1.93 in the Order Date field.
e. AND Operator
To ask multiple questions within one query either the AND or OR operator needs to be used. AND questions are easy, when you type examples in multiple fields on the first line of a query, Paradox assumes that you want all the fields to be matched at the same time. For example "Which Companies are in Essex and have a Credit Limit of more than #700", type Essex in the County field and >700 in the Credit Limit field.
If the Examples need to be in the same column split the two examples by a , eg. Credit Limit greater than 1000 and less than 2000, type >1000,<2000 in the Credit Limit column.
f. OR Operator
If you want to ask multiple questions that are unrelated to each other then use the OR operator. For example which companies have a Credit Limit greater than 1500 or are based in London, to execute this query type >1500 in Credit Limit column on the first line of the query and type London in the County column on the second line of the query. Make sure that every field in the query which is checkmarked is checkmarked on both lines.
(7) Other Operators
a. LIKE
In order to find entries when you are not sure of the exact spelling or name, you can use the LIKE operator. For example LIKE Stevens would find Stephens.
b. Wildcards
When you can't remember if an entry is upper or lower case or the correct spelling use the wildcard .. Eg. ..92 would find all dates in 1992, st.. will find all names starting with st, ..lton K.. would find Milton Keynes
c. NOT
The NOT operator will select records that do not fulfil a certain criteria. Eg. NOT London
d. BLANK
The BLANK operator will find records where a field has been left empty. Eg. To find records where no Credit Limit has been entered, type BLANK in the Credit Limit column.