A parameter is a piece of information which is passed to a procedure or routine for processing. The queries we saw earlier had their data criteria (e.g. "The Beatles") embedded in their definition; a query with a parameter allows the user to enter the criteria when the query is run. Thus the user could enter 'The Beatles' when the query is run but also 'Dr Dre' or 'The Carpenters', and so on. Note that you cannot use wild cards in the criteria you enter.
Setting up a field as a parameter is quite easy. For example, to find all CDs by a particular artist you would enter a command to the user in the Criteria box instead of a name:
[Enter the name of the artist]
When the user runs a query with a parameter the following dialogue box opens, into which the user puts their search criteria:
It is possible to set more than one field to a parameter so in the above example one might add a second criterion such as the title or a date ([Enter name of CD] or [Enter date]). It is also possible to set ranges for fields such as the date by adding a second copy of the field and setting a second criterion. For dates this might be something like:
[Enter the start date] - for the first Date field
[Enter the finish date] - for the second Date field
This arrangement needs a little tweaking in the SQL panel. Initially the SQL reads like this:
SELECT CDs.artist, CDs.CDTitle, CDs.Date
FROM cds
WHERE (((CDs.Date)=[Enter start date]) AND ((CDs.Date)=[Enter finish date]));
The last line of this version of the code will not work properly. If the user puts in '1/1/1965' and '1/1/1970' the WHERE clause will find records where these two dates are equal, clearly something that is impossible. With a slight change however the code will do what we want:
WHERE (((CDs.Date)>=[Enter date]) AND ((CDs.Date)<=[Enter finish date]));
With the addition of the > and < operators the code will now produce dates in the required range. For more details see the section on SQL.
Sometimes we want to include a statistical function in the output of a query. The most common need is probably to add the values in a field to produce the total value, for example the total orders over a year. A simple starting point is to find the total number of tracks on the CDs in the CDs table.
To do this we start a new query in Design View, add the OrderItems table, click Close and then add the Price and OrderItem fields. Next, we click on the Sum (Σ) button and choose Sum for the Price field and Count for the OrderItem field.
A new row now appears in the grid labelled 'Total:'. The default value in the Total row is 'Group By' which we replace with one of the twelve functions available from the drop-down list. In this case we choose Sum which adds together the values of all the transfers we have recorded.
(Wrong pictures but you get the idea!)
A query can add a new field to its output which is derived from a calculation on one or more existing fields - this cannot be done in a table. This raises the issue of whether derived fields should be included in a database table. For example, VAT has to be added to the price of an item but the calculation cannot be made in the table, only in a query. The VAT could be calculated outside the database and entered afterwards but this raises practical difficulties. Access provides a mechanism to calculate a new field and generate a new table or update a field in a table - more about this later.
To calculate, for example, the amount of VAT payable on each track purchased we proceed as follows:
This will produce the following result:
These features can be used to perform a wide range of calculations within queries. There are similar facilities in Reports.