Computer Training

Microsoft Access - Queries

Sorting Data in a Query

1. Using the same query right-click in the table area and choose ShowTable from the shortcut menu.
2. Add the table “Orders” and then the field “ShipCity” to the query. Below the field is the Sort, click on the drop down arrow and change the sort order to Ascending.

Sorting Multiple Fields

In this example the data field “Discount” and “ShipCity” are sorted in ascending order.

1. Return to the Design View window.
2. Change the sort order to Ascending under the field “Discount” and then Run the query.
3. Return to the Design View and change the “Discount” field’s sort order to Descending.
4. Run the query. Close the query and do not save the changes.

Calculating Statistics in a Query

Built-in statistics include: Count, Sum, Avg., Max (largest value), Min (smallest value), STDEV (standard deviation), VAR (variance), FIRST and LAST. To use any of these, a Total row must be added. Right-click the grid and click Totals on the shortcut menu. For example we will calculate the number of clients billed, the average bill for all clients, and the total amount clients were billed.

1. Create a “New Query in Design View.”
2. Click on the tab Queries and then select and add the Order Details Extended Query.
3. Add the field Extended Price to the grid three times.
4. Click on the Greek symbol for Total on the Formatting Bar. In the Total row of the first field change the “Group By” to Count. Repeat the same step for the following fields changing the “Group By” to Average and then Sum.
5. Run the query to count the number of existing Extended Prices, to find the average ExtendedPrice, and to sum all Extended Prices that were given.
6. Do not close the query!
   
   

Using Criteria to Calculate Statistics

Sometimes it is necessary to calculate statistics for only certain records. To accomplish this you must first select “Where” as the entry in the “Total Row” and then enter the criterion in the Criteria Row. In this query we need to calculate the average Extended Price for the product “Chang.”

1. In Design View and add the field “ProductName” to the grid.
2. Under that field click in the Total Row and select “Where” as the Group By.
3. Type Chang as the criteria. Leave only the checkmark to show the Average Extended Price, remove the rest, and then Run the query.
4. Close and do not save the query.

Create a Duplicate Query

The Duplicate query is used to find duplicate information in your database. In this example you will use this query to find out if deliveries are made to one or more customers in the same city or region.

1. Click on the button “New” and then choose “Find Duplicates Query.”
2. Add the Customer Table for this query.
3. Transfer the fields City and Region and then click on Next.
4. Transfer the fields “ CustomerID,” “ CompanyName,” and “Country.” Click on Next.
5. The results show that there are multiple customers in three different regions.

• IntroductionUsing WildcardsSorting Data in a Query

Search | Site Map | Ask Scranton | Choosing Scranton | My Scranton | Campus Contacts