A query is used to isolate records which contain data you want to view, for example, the details of a single CD, all CDs on a particular label or all CDs issued before 1965.
You should realise that a query on half a dozen records reveals very little but a query on hundreds or thousands of records is very useful - it may isolate (or ‘filter out’) a single piece of valuable information from many thousands. Modern businesses and other organisations use databases extensively and they have become the cornerstone of information systems and the 'information society'.
To set up a query select the Query tab from the Database control panel. Double click the Create query in Design view option..
Add the table from the list on which you want to base the query and then click Close as there are no more tables to choose from.
This opens the Query Definition box:
In this design box you define the fields which will be shown in your query (you may not want them all to be shown) and you specify which records will be displayed by defining search criteria.
To get field names from the upper part of the screen to the lower part do one of the following:
Enter the search criteria in the Criteria box. In the first query above the Criteria are simple - just the title Help. When you want to find a record based on equality you just enter the name or value in the box, there is no need for '='.
To exclude an item from a query list you would enter, for example <>Help, which would include all teams in the output except Help. The <> operator means 'not equal to'.
If you were to put 'Pet Sounds' in the row marked 'or:' under Title the query would find the two records for the clubs specified, Help and Pet Sounds. This creates an OR query which finds records meeting either condition.
To display the results of a query click the Datasheet view icon in the toolbar:
In this example a second field has been added and the search criteria are defined on that field. The > symbol is used to find values greater than the value entered, here a date after 01/06/1967. The < symbol is used to find values less than the value specified.
Notice that the date was entered as '1/6/65' but Access has formatted it to '01/06/1965' and has also added '#' symbols around it, the delimiters for a date type.
In the next query a third field has been added and two criteria are now in use. The first condition is that the date should be before (less than) 1/6/1966, while the second says that the Label should be equal to 'CBS'. Putting criteria in separate fields creates an AND query as both conditions must be satisfied for a record to be included in the output.
To move fields from the list in the upper panel point and double click the field names or ‘drag’ them from there to the empty field holders underneath.
In the next query we want to find all artists whose name begins with 'The'. To this we use the * (wildcard) character which stands for any group of characters: The* will find all artists whose names begin with the word 'The'.
To begin with we add the Artist field to the query design:
This is not in the place we would like it so we select it by clicking in the narrow grey band above the field names and then drag it with the same band so it lies between Title and Date Released:
Access has changed the 'The*' entry into 'Like "The"' - you could have typed this is in. Clicking the Datasheet view icon reveals the following list:
Two other operators for queries are BETWEEN and IN. For a numeric value you could use, for example:
BETWEEN #1/1/65# AND #1/1/67#
This is the same as '>=#1/1/65# AND <=#1/1/67#'.
For text values you could use, for example:
IN ("Elektra", "Island")
which is the same as 'Elektra OR Island' (putting the words on different Criteria rows).