8.6 Queries
A query allows you to gather together information from a selection of tables and predefine searches and sorts
across the data.
-
8.6.1 Add a query
-
8.6.2 Add query view
-
8.6.3 Set view
-
8.6.4 Display a query view
-
8.6.5 Edit a view
-
8.6.6 Edit a query
-
8.6.7 Standard actions on a query
-
8.6.8 Search on a query
-
8.6.9 Sort a query
-
8.6.10 Delete a query
-
8.6.11 Remove Tables from a Query
8.6.1 Add a query
A new query can be added to the database by selecting Add | Query from the database view. This will create a new empty query,
indicated by a  icon.
The query function can be set up by selecting View/Change | Query Details for the query. This takes the user to the "Query Details" page,
where the tables which will contribute data to the query are assigned in a two-stage process:
-
Select the Start Table from the list box and click on "Add Start Table".
-
Select the relations from this table to other tables which will contribute to the query and click on
"Add Table".
Note that only tables which are connected by relations can be used in a given query. When all the tables
which will contribute to the query have been selected, it will not be possible to add any more tables to the
query.
To view any information in the query, at least one Query View must be added. A query can have any number of
views, each presenting different aspects of the same information. In addition, templates can be added to
these views, to give them an individual look and feel.
A completed query, containing two views (one of which has associated view templates), is shown in Figure 8.6-1. The following sections explain how to complete a query.
Figure 8.6-1: A Query
8.6.2 Add a query view
To add a new view to a query, the user should select Add | View for the appropriate query.
The view should be given a name in the usual way. Clicking on the
view's name or on the  icon will take the user into this view. Before the view can be used, the information which it
will present must be decided. This is done by setting the view.
8.6.3 Set view
Until the fields to be displayed in the query view have been specified (i.e. the view has been set), the
query cannot present any information. To specify the fields to be displayed, select Add | Add View .
Fields are added to the view in the same way as they are added to a table (see section 8.3.1 - Add a Field). However, the user may only choose from the
existing fields in the database tables. These are presented in a list box for each field in the query view.
Note Ensure that the field names are selected and not simply the table names, otherwise the
information presented in the query view will not be as expected.
Fields can be displayed in a different order in the query from the order which is used in a table and
fields from different tables can be mixed together. In addition, the number of records to display on a
single page can also be chosen (the default is 30); this can be decreased for views displaying large
amounts of data, and increased for sparse views. Note that entering the value 0 will display all records.
Once the number of records per page to display and all the fields which are to be displayed have been
selected, click on "Apply Changes" to store this selection.
8.6.4 Display a query view
Queries within Business Collaborator are generally viewed by simply clicking on a View. A
View of a Query is shown in Figure 8.6-2.
It is possible to reduce the amount of data to be displayed by searching for a specified item within any of
the fields in the tables which have been added to the query. Note that these are not necessarily fields
which are displayed in any of the views of the query. The default is to search in all available fields. The
search can match for a number of criteria:
-
contain (phrase)
-
will match any records containing the phrase specified (case insensitive).
-
contains all of
-
will match any records containing all the words you specify (i.e. an AND operation)
-
contains one of
-
will match any records containing at least one of the words you specify (i.e. and OR operation)
-
do not contain
-
will match any records not containing the phrase specified (case insensitive).
-
are (not) equal to
-
will match any records that are (not) equal to the specified entry; the entry can be a string
(case sensitive), a number or a date (specified dd/mm/yyyy); Business Collaborator will interpret
the entry according to the type of field being searched.
-
are (not) less (greater) than
-
will match any records that are (not) less (greater) than the specified entry, which should be a number or
a date, although a text entry will be interpreted.
To search for ranges (i.e. 'greater than 10/05/00' and 'less than 1/6/00', use queries, where many searches can be used simultaneously. Note: date searching only occurs when the field being searched is of type 'Date' (or 'Automatic Date').
The number of records found which match all the search criteria is displayed at the top of the
query page.
Figure 8.6-2: A Query View
8.6.5 Edit a view
To change how a view is presented within a query, select View/Change | Edit View
be added to the query view. Fields can be removed from the query view by selecting them in the first column
of the "Edit Query" page and then clicking "Apply Changes". Also, the number of records to display on a single page can be specified. The default is to display 30 records. Entering the value 0 will display all records.
For advanced users familiar with HTML, View Templates can be created via the [Add HTML Template] link. These
are detailed in section 8.7.9 - HTML View Templates.
Figure 8.6-3: Edit a Query View
8.6.6 Edit a query
The attributes of the whole query can be edited by selecting the required action from the "Query
Details" page. This page is
accessed by clicking on the query name, the "Query" icon or the View/Change | Query Details for the appropriate
query.
8.6.7 Standard actions on a query
A relational database query can be renamed or its description edited in the same way that a document can be
modified. As a query may contain a number of views, the query may be expanded in the database view to
display the views it contains. Selecting  | Contents will allow the display of the query's contents.
8.6.8 Search on a query
Selecting View/Change | Set Query Search for the query the user to prescribe a set
of search criteria which applies to all of the views of the query. Any number of search criteria can be set
on a query (one such is indicated in Figure 8.6-4). The first one specified will take precedence. A new
search criterion is added in the same way as a field is added to a table (see section 8.3.1 - Add a Field) by selecting the number of searches to be
specified and then clicking on "Go". The available search fields are those belonging to the
tables which have been added to the query. Note that these are not necessarily fields which are displayed
in any of the views of the query. The default is to search in all available fields.
The search can match specified entries as detailed in section 8.6.4 - Display
a query view above. Note that in the case of date fields, a number of special keywords useful for 'dynamically
updating' queries are also available:
-
date_today Today's date.
-
date_weekstart The date at the start of the current week (Sunday).
-
date_nextweekstart The date at the start of the next week (Sunday).
-
date_twoweekstart The date at the start of the week after next (Sunday).
-
date_monthstart The date at the start of the current month.
-
date_nextmonthstart The date at the start of next month.
-
date_firstsundayinmonth The date of the first Sunday in the current month.
-
date_firstmondayinmonth The date of the first Monday in the current month.
These have been added with a view to timesheet/facilities management-type applications in the future. At present, these have to be typed into the search/query input box by hand, since there is no obvious way of making them visible to the user (they could perhaps be described in the Help pages). This functionality will obviously not be used by the day-to-day user, only by database application designers.
Figure 8.6-4: Search on a Query
8.6.9 Sort a query
Selecting View/Change | Set Query Sort for the query allows the user to prescribe a set
of sorting criteria which applies to all of the views of the query. Any number of sorting criteria can be
set on a query. The first one specified will take precedence. A new sorting criterion is added in the same
way as a field is added to a table (see section 8.3.1 - Add a Field) by
selecting the number of sorts to be specified and then clicking on "Go". The available sort
fields are those belonging to the tables which have been added to the query. Note that these are not
necessarily being displayed in any of the views of the query. The sort can be specified to be ordered
forwards or backwards alphabetically.
8.6.10 Delete a query
To delete a query, select the query in the database view and choose Selected | Delete . The query will be moved to the waste bin from where it can be undeleted.
8.6.11 Remove a table from a query
To remove a table from a query, select View/Change | Query Details for the query in question. This page will enable you to remove a table if:
- The table's fields are not used by any view on the query
- The table does not link two tables together within the query, only the "end" (or "leaf") tables can be removed.
The interface will only allow you to remove tables that can actually be removed and will advise you of why you cannot remove tables.
Figure 8.6-5: Removing Tables from a Query
|
