Thinking About Queries

Chapter Six outlines several different types of queries and how to use them. Here is a listing of the queries I learned about and an example of how each can be used.

Parameter Query: This query prompts the user for the value of a particular field. An example would be if I had a table listing restaurants in four different cities I could use this query to list only the restaurants in one of the cities, and each time I opened the query I would be prompted for which city I want to display information on.

Find Duplicate Query: This query is used for finding duplicate values in particular fields. When creating a find duplicate query you can specify which field you want to find duplicate entries in, such as a last name field where some of the last names are listed more than once.

Find Unmatched Records Query: This query is the opposite of the previous one, only displaying entries in a chosen field that are not repeated.

New Table Query: Queries are not tables, although Access allows you to work with the data in queries as if they were tables. This query saves the result of your query as a table. This would be great if you wanted to create a similar table to one that already exists but don’t want to retype all of the values in.

Add Records Query: This is also known as an append query. It can be used to add records to a table when the query you are using shares a common name with the table you want to add to.

Delete Query: This query is used to remove records from a table based on your chosen criteria. For example, if you want to delete all records in an employee table that contains an employees name, this would be the query to use.

Update Query: Let’s say for example that you have a huge orders list table and you need to increase the unit price of one of the products. Rather than scanning the entire table to add a dollar amount to every entry, you can let this query do the work for you

Crosstab Query: This query allows you to summarize the contents of fields that contain numeric values, such as date or numbers fields. You can choose between average, sum, maximum, minimum, and count.