6.3: Query Properties

In the upper-right area of a query in Query Design View you will see a button labeled Property Sheet. Click this and you will see properties for a field in the Grid, or, for the query itself, depending on where the cursor is located. Click the mouse in an open area in the Relationships Diagram and you will see properties for the query. Two query properties we discuss are Top Values and Unique Values.

Figure \(\PageIndex{1}\): Query properties.

Top Values

You can change the selection for Top Values. The default is ALL which results in all rows displayed when the query is run, but you can use this property to limit the rows displayed. As indicated below you can type an explicit number of rows such as 5, or a specific percentage of rows to be displayed.

Figure \(\PageIndex{2}\): Setting the Top Values property.

Example

Consider the Library database where the Member table has one row per member. Sample are shown below:

Table \(\PageIndex{1}\): Sample library members.

id	firstName	lastName	gender	birthDate
1	John	Smith	male	15/05/1999
2	David	Martin	male	06/08/2000
3	Betty	Freeman	female	18/09/1997
4	John	Martin	male	11/09/2000

Suppose we wanted to know who is the youngest member. One way to find out is to sort the members by birthdate and then pick either the first or last row according to how you ordered them (descending or ascending). Consider the following where the members are sorted in descending order by birthdate and then we list the first row by specifying Top Values = 1:

Figure \(\PageIndex{3}\): The youngest member using Top Values=1.

For the sample rows the query produces the result:

Table \(\PageIndex{2}\): Query returns one result row.

firstName	lastName	birthDate
John	Martin	11/09/2000

Unique Values

If the Unique Values property is set to Yes then MS Access will eliminate duplicates rows from the result.

Example

Suppose a librarian wants a list of authors from the Library database. If we use a query to list the authors but we do not set Unique Values to Yes then the result could show an author several times, once for each of his/her books. The following result set shows Jeo Celko listed 3 times:

Table \(\PageIndex{3}\): Query with duplicates.

author
Donald Matthew
Eileen Power
Frederick Whitehead
Joe Celko
Joe Celko
Zigurd R Mednieks
T A Halpin
David Hay
Joe Celko
Lynne Elliott
Charles H Talbot

We can eliminate such duplicates by specifying Unique Values = Yes as in:

Figure \(\PageIndex{4}\): Setting Unique Value to yes.

Instead of 11 names this query would only list the 9 different author names.

Exercises

Exercise \(\PageIndex{1}\)

Consider the Library database.

1. Which member is the oldest?
2. Which book was the first one to be taken out on loan?
3. Which books have been taken out on loan? Any book listed should be listed only once – no duplicates

Exercise \(\PageIndex{2}\)

Consider the University database.

1. Create a query to list the department codes (with no duplicates) of departments that offer 6 credit hour courses.
2. Modify your query to list the department names too.

Exercise \(\PageIndex{3}\)

Consider the Company database and its Employee table.

1. The empId field is assigned values sequentially starting at 1. What is the last empId value that was used? (What is the empId for the last employee added to the table?)
2. Write a query to determine the name of the oldest employee.
3. Write a query to list all of the employee last names. If at least two employees have the same last name then this list will be shorter that a list of employees.
4. Suppose there is a field hireDate which holds the date when an employee was hired. Write a query to determine the name of the employee who was most recently hired?