6. MICROSOFT ACCESS QUERIES – ADVANCED
6.3.2: 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:
We can eliminate such duplicates by specifying Unique Values = Yes as in:
Instead of 11 names this query would only list the 9 different author names.
Exercises
1) Consider the Library database.
- Which member is the oldest?
- Which book was the first one to be taken out on loan?
- Which books have been taken out on loan? Any book listed should be listed only once – no duplicates
2) Consider the University database.
- Create a query to list the department codes (with no duplicates) of departments that offer 6 credit hour courses.
- Modify your query to list the department names too.
3) Consider the Company database and its Employee table.
- 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?)
- Write a query to determine the name of the oldest employee.
- 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.
