|
|
10.0.0 Query-by-form allows you to conduct a simple search of a specific group of records by filling out a data entry form with examples of the data you wish to find located in the related fields on the screen.
The Query option is found on the menu bars located at the bottom of the screen when in View or Browse mode. To run a search for records using the Query-by-form method, select the "Query" option located at the bottom of any View or Browse screen.
When the Query form is activated you will notice that the Query form is identical to the data entry form. Simply enter data into the relevant fields to create a list of records that match that data.
EXAMPLE:
Service Number : Date of Birth : <=24/11/96 Blood group : O+
The above example would return a list of all members who were born before the 24/11/1996 and who had a blood group of O+.
It is important to remember that when the query list is compiled it will be displayed in the current index order , so before running the Query you should select the index order first then run the Query. You may edit any records contained in the Query list but any changes you make do not update the Query list until you run it again. For example if you run a Query to find all records that are over $200.00 and you change one of the records that is returned in the Query to $199.50 then that record remains in the list until you run the exact Query again or refresh it..
Complex searches can be run using a combination of operators as shown below:
| OPERATOR | DESCRIPTION | EXAMPLE |
 (NO
OPERATOR) |
Match | Jones |
| ; |
OR | Jones;Stevens |
| &
|
AND | >100 & <200 |
| ? |
Single Wild Card | Jo?es |
| * |
Many Character wild card | *wrench* |
| ~ |
Sounds like | ~Jones |
| !
|
Field is blank or empty | ! |
| ..
|
range of values | 150..180 Andrews..Jones |
| <>
or # |
Not equal to | <>Cancelled #Cancelled |
| <=
|
Less than or equal to | <=04/02/63 |
| >=
|
Greater than or equal to | >=2000 |
| < |
Strictly less than | <100 |
| >
|
Strictly greater than | >Andrews |
| ==
|
Exactly equal to (case sensitive) | ==Jones |
10.0.2 Explanation of Operators
1. Note (no operator) Find a match Brown - Finds all occurrences of the word "Brown" or "brown" (not case sensitive)
2. ; Logical OR Brown;Stewart - . Finds all records that have the word "Brown" OR the word "Stewart" in the same field
3. & Logical AND >150 & <200 - . This example uses a combination of operators. In this case all records would be found where the amount was greater (>) than 150 AND (&) less (<) than 200 .
4. ? Single character wild card b?ne - The "?" can be used as a wild card and in this example would return all records that begin with "b" and end in "ne". i.e.: Brisbane, Browne, Borne.
5. * Many character wild card *water* - The * * symbols are best used to find words or phrases contained in a field. For example your barracks repairs register application may have one record where the repair field contains the sentence. "laundry on top floor has broken water pipe under sink". In this case you are doing a search on the word *water*. Placing the "*" to the front of the word or phrase and to the end will find all occurrences of any sentence which contain the word "water". Searching for a string of characters is the same as using the same sentence you can narrow down the search list by using a combination of operators i.e.:*water*&*under sink* This would return only those records where the word "water"AND the phrase "under sink" are contained in the same sentence.
6. ~ Sounds like ~brown - The sounds like character ( ~ ) can be used at the front of a word to find all matches for words that sound the same.
7. ! Field is blank or contains no information - he "!" (exclamation mark) placed in any field will return all records where there is no data entered at all in that field. For example you may wish to do some house keeping on records where your personnel database has a date of birth field which has some blank entries. Placing a "!" in the date of birth field will return a list of those records that have no information placed in that field allowing you a simple list to work with rather than having to hunt through all records in the database.
8. .. Range of values 205..450 BR..ST - In this example you can use two dots ".." between two values to return all occurrences that are between the two values (including the values themselves). The example above would find all records that have an amount that is between 205 and 450 (including 205and 450) or it can be used with characters to find all occurrences that are alphabetically between BR and ST or possibly all records between two dates. The ".." range of values operator works in a similar fashion to using the greater than or equal to (>=) and the less than or equal to (<=) operators.
9. <> # Not equal to <>61875 #61875 - Two character symbols can be used for "Not equal to". You can use "<>" or the "#" symbol. Both have the same function. For example you may want to return a list of personnel which do not include, say, the rank of Capt , Simply enter #Capt or <>Capt into the rank field and press F10 to return a list of all ranks less Capt. If you wanted to exclude multiple ranks you would use a combination of operators such as #Capt&#Maj This example would exclude both Captains and Majors.
10. <= Less than or equal to <=11/10/96 - .Less than or equal to (<=) use this operator to find for example all records in the amount field that are less than or equal to an amount or less than or equal to a specific date. i.e.: <=150 , would return all records less than or equal to 150 or <= 11/10/96 would return all records equal to 11/10/96 or prior or <= C would return all records less than or equal to the letter C (any records starting with A, B or C).
11. >= Greater than or equal to >= Stewart - Greater than or equal to (>=) use this operator to find all records that are greater than or equal to a value. Works the same way as the above example except in reverse.
12. < Strictly less than < 150 - less than (<) use this operator to find all records that are strictly less than the value you specify, meaning that it does not include the value you enter but all records less than it.
13. > Strictly greater than > Brown - Greater than (>) use this operator to find all records that are strictly greater than the value you specify, meaning that it does not include the value you enter but all records greater than it.
14. == Exactly equal to (case sensitive) Stewart - Exactly equal to (==) , two equal signs placed before the value will return any exact match for the value you specify. This function is case sensitive so if you enter, "brown", it will not return any occurrences of "Brown" or "broWn". Only identical exact matches are found.
When the Query form is active the cursor will be flashing at the first field on the form.
You can use the [Enter] or Up/Down arrow keys to move to each field or alternatively, if you have a mouse driver loaded, click on the field with your mouse.
When the Query form is active the cursor will be flashing at the first field on the form.
[F2] Operators
Brings up the list of operators available with their descriptions and examples
[F3] Choices
This option will only be displayed if the particular field the cursor is in has a "Pop up" list available.
Pressing the [F4] sort option allows you to create a sort order for your Query results. The menu options at the bottom of the screen will change to:
[F2] Sort direction
This will allow you to specify the sort order for your results in Ascending or Descending order. For example you may want your results sorted alphabetically by name in ascending order and within name, sorted by date of birth in descending order.
In each field you want sorted, a sort direction and priority order will be shown as, NAME: 1A DATE OF BIRTH:2D Meaning ; Sort firstly by name in ascending order and secondly ,by Date of birth in descending order. This will then be how the records are ordered in the list of matches found by your query. [F4] Query - changes the screen back to allow you to continue putting in the details for the query
[F5] Options
Shows the options for the sorting of records as:
Case sensitive Yes No Yes- Meaning, do you only want records matching exactly as they appear in the database. No -Meaning, show all matching records regardless of whether the information is typed in upper or lower case letters
Show: All Deleted Not deleted
All - Select from all records in the database Deleted - Only choose from records that have been marked as deleted Not deleted - Choose from only the records that are not marked as deleted.
Create order using: All Unique keys only
All - If the list has the name Jones multiple times, display them all. Unique keys only- If the list has multiple entries of "Jones", only display the first occurrence.
Display sort expression Brings up the expression window which shows how the program uses its functions to find matches for your Query. This screen in nearly all cases will be of no meaning or use unless you understand the principals of database query language and is only provided for experienced users who want to fine tune the expressions
Clear Query criteria
Clears all information on the screen and allows you to start again.
[F6] Next Adds additional Query screens . To be selected, a record must match ALL conditions on any ONE of the Query screens that you fill in. You may want to use this just to select specific records to appear in the Query list, such as typing a regimental number on one screen, pressing [F6] and typing another and so on so that the end result is only those records you selected on each screen.
[F8] Window on Displays the program expression or sorting expression as defined by the program This screen in nearly all cases will be of no meaning or use unless you understand the principles of database query language and is only provided for experienced users who want to fine tune the expressions.
[F10] Continue
After all your Query information and sort orders are defined press [F10] to start the search for all the matching records as defined by your Query.
NOTES:
After a Query is run it becomes available in the list of indexes to choose from and is displayed as "Search/Sort List" in the selection box
When you are in browse or View mode you can select "i" (Index) to pop up a list of index choices. What this does is re-sort all the records in the database according to the index you have chosen.
The option "Search/Sort List" at the bottom of the index choices is not actually an index as such but is a list of all the records that were found as a result of your Query.
Unlike indexes, Search/Sort Lists are not automatically maintained as new records are added to the database or current records are changed.
If you change records or add new ones the Search/Sort list that was created by your Query will become out dated and is annotated to the left of the words "Search/sort List" with the "¨ " symbol to indicate it is out of date.
If you want the results of you Query to be accurate you should run it again or refresh the Search/Sort List
This can be done by selecting the index choice box (Press "i" in either view or browse mode) and pressing [F5] which will re-run the active Search/Sort List.
Whilst the index choice box is active you can also press [F4] to see the Search/Sort List history which shows a list of the most recent run Query's on the database
You can choose from any one of the searches from the history list and refresh them. There is also an [F3] option which will show you any saved Search/Sort Lists to choose from (If any have been included at release time of this application).
