(continued from page 1)
In addition to the most common query in Access, the "Select Query,"
there are several special queries that you can use to delete records,
add records, create new tables, and update existing records. Here
are the types of queries supported by Access, and a brief definition
- Select Query
- This is the default query created by the Access Query View
and Query Wizard. With the Select Query, you are simply
"selecting" records from the data sources.
- Update Query
- An Update Query is used when you want to update records in
your Access tables based on specific criteria. For
example, "update the State field to 'NY' for all records in the
Customer table where the City is New York." You can begin
by creating a normal Select Query, then choosing the "Update"
icon in the Access Ribbon. You can then enter a field name
or specific value in the "Update" row of the query:
- Delete Query
- A Delete Query allows you to delete records that match
specific criteria. Once you have created your Select Query
and specified the criteria you want to use to delete records,
you can test out your query by running it. If you know the
query will select only the records you want to delete, you can
change the query type to "Delete" in the Ribbon and select run
- Append Query
- An Append Query will add records to a table you choose.
For example, if you have imported records from an Excel file
into a temporary staging table, you can then add them to your
actual "live" tables using an Append Query. First, create
your Select Query with any necessary filter criteria. Then
choose the "Append" Ribbon icon. You will be prompted to
indicate what table you want to add records to. Once you
choose the table, you can match the fields
between the source table and destination table using the "Append
To:" row highlighted in the screenshot below. Notice that
the names of the fields are different between the two tables,
which is why Access needs you to tell it how to map the fields
to each other.
- Make Table Query
- A Make Table query creates a brand new table based on a
query. For example, perhaps you want to create a summary
table users can view, but you don't want to let them update the
actual source data. You could create a new table from
several other tables in our query. When you create your
Select Query, choose the "Make Table" option in the Ribbon.
You will be prompted to give your new table a name. When
you click the run button, your new table will be created!
- Crosstab Query
- Crosstab Queries are definitely underappreciated. They essentially transform columnar data into a more familiar pivot form. To create these queries, we definitely suggest using the Query Wizard to walk you through the process.
As shown below, a crosstab query can help you see your data
multi-dimensionally in much the same way a pivot table in Excel
- Union Queries
- Union Queries are a special type of query where you are combining two very similar sets of data into a single view.
For example, perhaps you have two queries with customer names.
You'd like to consolidate them into one large list to export or
view in a report. You can create a Union Query which will
essentially display the records from both tables in a single
large list. Unfortunately Access doesn't include a Query
Design view for Union Queries, so you have to know the SQL
statements needed. This
Microsoft article on union queries is quite helpful.
- SQL Server Query Types
- The last query types you will see in Access are specific to SQL Server (pass-through and data definition). In the interest of brevity, we won't cover them here.
A common instinct Access users have is to create fields to store
calculated data in Access tables. The more efficient way
is to use queries to calculate the values "on-the-fly."
This ensures if the underlying data changes (e.g., Quantity), the calculated
field (e.g., total cost) is not inaccurate without being
The answer is to create queries that
perform the calculation at runtime. In essence, it is
calculations in Access are much like they are in Excel, except
you refer to fields in tables instead of cells in a workbook.
To begin with, let's look at a very simple example:
In the example above, we've added a new column that will be
named "Line Total." Note the convention for the query column
starts with the name, then a colon. This tells Access where
the column name ends and the calculation begins. Next we add
the calculation itself. Note that each field name is
surrounded by square brackets . This is very important.
Access uses the  brackets to delineate one field from another.
Otherwise it wouldn't know if "Order Start Date" was one, two, or
three separate fields. Finally, you'll see we are using
standard math symbols to calculate the line's total price.
Quantity * Unit Price * (1 - Discount). When you run the
query, it will, for each row, calculate the line total as described.
Right-click the column and select "Properties" to set the
formatting. As you can see in the screenshot above, we're
setting the field to be Currency with no decimals. The output
looks like this when you put the query in Query View:
Notice above that we have two rows for Order ID 1 and A. Datum
Corporation. That is because we are calculating individual
lines in an order, and this order has several lines. We'll
cover how to calculate the order total in the Aggregate Queries
section that follows.
One last note on calculated columns in
a query: You will find instances where one field is empty or
zero, and you receive an #Error message when the query is run.
To handle this, use the IIF() expression. For example:
example above, if the TotalRevenue field is more than zero, use the
calculation [TotalsRevenue]/[TotalRevenue]. But if the
TotalRevenue field is blank or zero, you would normally receive a
"cannot divide by zero" error. By using this IIF() expression,
the column will be set to zero if TotalRevenue is zero.
Access can help you to group and sum, average, count, or perform
other advanced operations with Aggregate Queries. Create a
normal Select Query, then choose the "Totals" icon in the ribbon.
This turns your query into an Aggregate Query. You will notice
a "Total:" row appear in the query editor:
If you set the "Total:" to "Group By" Access will show you records
grouped by that field. You can also perform these operations:
Sum, StDev (standard deviation), Count, Max (maximum value), Min
(minimum value), Avg (average). Note that in the example
above, we've take the calculated query we created earlier and now
want to get the sum of all the individual lines in each order to get
a total for each order. When we run the query, it appears as
Say we wanted to show the total revenue for each customer, but not
by order? Simply remove the "OrderID" field from the query and
you'll get the total line item sum by customer instead of customer
The grouping in your query will really be controlled by how many
fields you include with the "Total:" set to "Group By." Think
of it as a way to control the granularity of what you see.
Grouping by City, State, Zip will give you much more detail than
simply by State. While we haven't explored it fully here, the
same principles apply for the other types of aggregate operations.
Getting the most recent order for each customer using the "Max"
operation on the Order Date field, for example. Aggregate
queries are a truly powerful mechanism in Access to summarize data
neatly. Shameless product plug:
are two other products available to very nicely
summarize and bring your Access data to life.