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.

Information on SQL

Based on my research on the subject of SQL, here is the information of I have come up with to describe it. As an analogy, SQL is to databases what HTML, or XHTML, is to web design programs. It is the programming language behind the graphical interface that makes up a software program. Although it is not necessary to know
SQL in order to create a decent database, much the same as a knowledge of XHTML is not necessary to create a webpage, it is helpful to known at least a little bit about it in case you need to ever manually edit a project. However, to average user would probably have no need for it.

Here are the basic commands in SQL:

-The SELECT operation is the most common operation in SQL language. It is used to retrieve data from a specific table, or multiple related tables, in a database. An asterisk symbol can be used to indicate that all available columns of a table are to be retrieved.

-The FROM clause. This indicates the source table or tables from which the date for your query is to be retrieved from.

-The WHERE clause. This is used to restrict the number of rows used by a query.

-The GROUP BY clause. This is used to group together rows with related values.

-The HAVING clause. This is used to elmininate rows after the GROUP BY clause has been implemented.

-The ORDER BY. This is used to identify which columns are used to sort the resulting data and in which order they should be sorted.

Here is a simple example of an SQL query that I found on Expedia.com:

SELECT *
FROM Book
WHERE price > 100.00
ORDER BY title;


I don't really see the need for using SQL when such a user-friendly interface is available. But like I said, it is good to know at least a little bit about it in the case that manual editing of queries is necessary.