Computer Training

Microsoft Access - Queries


Using Wildcards


Wildcards are symbols that can represent any character or combination of characters. In Access the four wildcards available:
  1. the asterisk * which represents any collection of characters, (K*) represents the letter K* followed by any collection of characters after the letter K.
  2. the question mark? is interpreted as a single unknown character.
  3. the brackets []match any character contained within, B[ei]ll such as Bell or Bill.
    Note: placing the not sign ! within the brackets such as B[!ei]ll will substitute any character not in the brackets
  4. the hyphen – used to find a single character within a range you specify.
How to:
  1. Clear all fields in the existing query by clearing all fields. Click on the Edit menu and ClearGrid.
  2. Click on the first field “SupplierID” and then scroll down the list to the field Phone. Press and hold down the Shift Key and then click on the field Phone. Place the mouse over the selected fields and release the Shift Key. While holding down the left mouse button drag the fields to the first column and release the mouse button. All select fields will fill in the respective columns.
  3. Type E* under the field “Company Name” and then Run the query. The returned data is all company names beginning with the letter E.
  4. Using the same query place a question mark (?) and the letter c between the letter E and the asterisk (E?c*). Run the query. The criterion states the name must begin with the letter E, a question mark is used for the unknown character, c is the third letter, and the asterisk is used for all unknown characters.
  5. In this example replace criterion with E[!a-r!t-z]* and then run the query. In the criteria we are stating that the first letter begins with E and that the second letter cannot contain any characters between a to r and t to z.
  6. Close the query but do not save it.

Quick Expression using the Zoom Box to Create a Calculated Field


In this example you create a calculated field
  1. Create a new query using the “Orders Detail Table.” Click on the first field OrderID and then Shift + Click on the last field. Hold the left mouse button down and drag all the fields to the query grid.
  2. Right-click in an empty Field column to create a criterion expression. Click on the short-cut menu Zoom. Type the following expression in the box,
    Purchase Price: ([UnitPrice]*[Quantity]*((1-[Discount])/100)*100). Click on OK.
    Note:  The order of operations are working from the inside parenthesis outwards.
              a) 1 – Discount, b) Discount/100, c) UnitPrice*Quantity*100
  3. Right-click on the Expression and choose Properties from the short-cut menu. Change the Format to Currency in the Field Properties dialog box. Close the box and then Run the query.
    The calculated field produces the Purchase Price including discounts. You can use addition, subtraction, multiplication, and division signs to produce the desired results.

Using Criteria for a Field not included in the results

  1. Return to Design View and remove the check marks in the fields UnitPrice, Quantity, and Discount.
  2. Click on Run. The data returned displays only the fields that have the check mark present.
  3. Place check marks in all the fields, Save the data but do not close the query.

Using a Number in a Criteria


In this example the same query is used to display all orders that did not receive a discount.
  1. Return to Design View and remove the checkmark from the field ProductID.
  2. Type a zero ( 0) as the criteria in the field“Discount.” Do not use any dollar signs or commas. Run the query.
  3. Return to Design View and add the checkmark for the field ProductID. Leave the query open.

Using Comparison Operators


A comparison operator is used when you are looking to retrieve data other than an exact match. These operators include: the greater than (>) symbol, less than (<) symbol, greater than or equal.
  1. Under the field “Discount” remove the 0 and place the checkmark back in the show field.
  2. Type >=.10 under the same field Discount. Click on Run!
Only those accounts that receive a discount greater than or equal to 10% will return in the query.


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.
Search | Site Map | Ask Scranton | Choosing Scranton | My Scranton | Campus Contacts
Disclaimer: The University of Scranton does not endorse views or opinions found on pages directly or indirectly accessed from our Web site.
© 2006 The University of Scranton Web site Powered by ActiveCampus™ Software by LiquidMatrix