MS Access Query Design (continued)
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 of each:
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 again. 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 works.
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 recalculated.
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.
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:
In the 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 follows:
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 and order:
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: Dashboard Builder and Report Builder are two other products available to very nicely summarize and bring your Access data to life.