14.1 Advanced Queries in Microsoft Access

Types of Criteria

Listed are the different types of criteria that can be used in an Access query:

Simple criteria: This type of criteria involves specifying an exact value or expression to filter the results. For example, when building out the inventory database for our potential freestanding retail locations, you could use simple criteria to specify which exact type of inventory you want to isolate.

Comparison operator: Comparison operators are used to compare two values or expressions. Examples include =, <>, <, >, <=, and >=. The < operator can be helpful to find out what inventory has less than a specified quantity on hand.

Wildcard: Wildcards are used to represent unknown characters in a search string. The two most common wildcards in Access are the asterisk (*) and the question mark (?). For example, a wildcard can be used to find all customers whose last name starts with a particular letter. Table 14.1 shows the most common wildcard symbols and a description of how they can be used when you have incomplete information or unknown characters.

Range criteria: Range criteria are used to specify a range of values for a field. As an example, a vender that we buy from to resell in our new retail locations may want to know all of the orders we have sold of theirs within a specific date range that have been taken out of our inventory. Rather than waiting for our next order, our vender could receive critical information on how much we may be looking to order in the near future.

Null criteria: Null criteria are used to find records that have a null value in a particular field. For example, you could use null criteria to find any inventory labels that do not include product descriptions.

Logical operator: Logical operators are used to combine multiple criteria in a query. The two most common logical operators in Access are AND and OR. For example, you could use the AND operator to find all orders with a total greater than $100 and placed by a specific customer. Table 14.2 summarizes common logical operators.

By using these different types of criteria in an Access query, you can create powerful and flexible queries that can help you analyze and manipulate your data effectively.

Figure 14.3 displays a few sample criteria, shown in the highlighted box, and explains how they work with operators.

Figure 14.3 When delimiters are used to filter a database you will notice the criteria listed under the field column. (Used with permission from Microsoft)

At WorldCorp, you may need to isolate orders that took place during a certain time frame. For example, how did our marketing campaign do in sales over a particular time period for our retail locations? How many orders did we do over the past week? How many did we do over the past month? You might need this information to help schedule orders for our retail locations. This example returns items with yesterday’s date (Date()–1). Thus, if the current date is 5/12/2024, you will see only items for the previous day, 5/11/2024.

In addition to the Date() function, there is also a Now() function to consider in Access. Both Date() and Now() are used to retrieve the current date and time, but they have different purposes and applications. The Date() function returns only the current date, without any time component. It is useful for calculating dates and time intervals, such as the number of days between two dates, or for filtering data based on a specific date. On the other hand, the Now() function returns both the current date and time, including the hours, minutes, and seconds. It is useful for tracking events that occur at a specific date and time, such as recording the time that a delivery showed up in inventory at a retail location.

Some reasons why you might choose to use the Now() function instead of the Date() function in Access can include the need to record time stamps. If you need to keep track of when events occur, such as the time of day when items are most likely to sell, the Now() function can be used to record the time stamp accurately. In addition, if you want to filter records based on a specific time range, such as all transactions that occurred within the last hour, then you will make use of the Now() function. In designing tables, note that it is important to use the correct format for each field used in the table you are pulling from in the query so that the unique expressions can be applied to that field.

In the example shown in Figure 14.4, the field labeled Order has a criterion of less than 50, so you will see only amounts that are less than $50 from what our customers spent at WorldCorp. The amount found in this field is calculated by multiplying two fields (price and qty) and grouping the results by sum for a total of all unique Order ID numbers. Greater-than (>), less-than (<), equal-to (=), greater-than-equal-to (=>), less-than-equal-to (=<), and other mathematical expressions can be used to query all fields that are formatted as numbers, including currency, integers, autonumbers, and calculated fields.

Figure 14.4 A logical operator is used for order price to only display orders that are less than $50. (Used with permission from Microsoft)

In the example shown in Figure 14.5, the Notes field in a query is based on a table that stores notes from sales orders that are formatted as long-text. A note can contain any amount of text, ranging from no words to a few words or even multiple paragraphs, depending on the order and the customer’s requirements. This criterion isolates all notes that contain the string Express, signifying the customer’s urgent need for the product. The key word “Express” can be extracted from all of the text logged in the Notes field, regardless of the order or length of the note.

Figure 14.5 Notice the asterisk following the word “Express” as a wildcard to filter the data where the word in any form appears in the notes section. (Used with permission from Microsoft)

Depending on how you format the data, your next task is to select the appropriate expression to extract the data. Yes/No, OLE objects, hyperlinks, and attachments all have unique expressions to help optimize Access’s ability to match your desired outcome. Again, formatting the data within tables is key to developing successful criteria expressions.

Building a Query with Criteria

When you create or modify a query in Access, you will be identifying/creating fields of data to isolate. These fields will represent the data you want to include, omit data that is not of interest to you, sort the data, and even combine data from multiple tables, as Figure 14.6 shows.

Figure 14.6 Several layers of filtering can be used in one query to build a customized list to meet specific needs. (Used with permission from Microsoft)

The example shown in Figure 14.6 of a query at WorldCorp combines two tables and isolates five fields from the related tables. The tables are related by the OrderID field. When a new order comes in, a unique OrderID is created in the TCustomerOrder table. Then, the order details are created in the TCustomerOrder table. The two tables are related by OrderID. This means that every order can have multiple sales items on the order. In short, every order is uniquely related to a customer. However, every order can have multiple sales items on the order. Those items are stored as a list in a separate table. Every customer, every order, and every list of order details need their own tables.

This example’s five fields to review are: OrderID, Order Date, Notes, Price, and Qty. Because running this query would yield every order, it may not be useful unless you create criteria to isolate a particular question.

Within Query Design tab, choose the column of the field to which you want to apply a criterion. Click in the box below the field name in the Criteria row for that field. Type in your first expression or right-click in the Criteria box and choose “Build” to utilize the Criteria Wizard. Repeat these steps for each field for which you want criteria that help answer the question at hand question. For instance, if you are concerned about offering free next-day shipping on all orders because of the rising costs of shipping, you might want to look at low-dollar purchases to see how often customers are asking for express shipping. One option is to review only those orders from yesterday (criterion #1), that were less than $50 (criterion #2), and also had the string “Express” in the notes (criterion #3) on the receipt. The following criteria would be typed out or built through the wizard for each field highlighted, in order, as Figure 14.7 shows.

Figure 14.7 The Criteria Wizard can assist you in building a more complex query, or you can also type criteria directly into the query if you are familiar with the appropriate syntax needed. (Used with permission from Microsoft)

Understanding Operators and Expressions

The two most common logical operators used in building an expression for criteria in a query are AND and OR. These are assumed depending on where you place the expression in query design. Any expression placed in line with the Criteria line is defined as an AND operator. Our last example used all AND operators for the three expressions. See Figure 14.9 as an example.

Figure 14.9 By default, when criteria are added to more than one field, those criteria will be used simultaneously in the query using the “AND” operator. (Used with permission from Microsoft)

Placing all three criteria on the same line means that the query will display only those items that are from yesterday and are less than $50 and contain the string Express. If you want the query to look for any of the three criteria, that is yesterday, or less than $50, or containing the string Express each needs to be on a separate line. Date()-1 would be in the criteria row, <50 in the or row, and then place Like Express in the row below the or row. Rather than a dataset needing to match all three criteria or to match either of the two requirements, it can now match any of the criteria.

Any expression placed below the Criteria line is then defined as an OR logical operator. If we change our last example by rearranging the placement of the expressions, it operates very differently, as Figure 14.10 shows.

Figure 14.10 To query using the “OR” operator, the selected criteria should be moved one line below to the “or:” line. (Used with permission from Microsoft)

Placing the second and third expressions on the line below the first criterion means that the query will now yield those items that are from yesterday “or” are less than $50 “and” contain the string “Express.” Rather than a dataset needing to match all three criteria, it now needs to match either of the two requirements.

Common Operators

In both Excel and Access, the most common operators are arithmetic, comparison, and logical. Each has its own function and returns different results.

• An arithmetic operator is used to calculate a value from two or more numbers or to change the sign of a number from positive to negative or vice versa (Table 14.3).
   
  

  Operator	Description
  -	Subtraction (2-1=1)
  *	Multiplication (2*3=6)
  /	Division (12/4=3)
  \	Integer division (6\4=1)
  +	Addition (15+4=19)

  Table 14.3 Arithmetic Operators A mathematical operator could be used to combine data such as getting the total sales amount for two or more company purchases.
• A comparison operator is used to compare values and return a result that is true, false, or null (Table 14.4).
   
  

  Operator	Description
  <	Less than
  <=	Less than or equal to
  <>	Not equal to
  =	Equal to
  >=	Greater than or equal to
  >	Greater than
  Between “Value1” And “Value2”	Between two values, inclusive (for example, “Between 1 And 3” would return 1, 2, 3)

  Table 14.4 Comparison Operators To compare one customer’s sales to another, you can use comparison operators such as greater than or less than.
• A logical operator is used to combine two Boolean values and return a true, false, or null result. There are three main logical operators that are commonly used in Access:
  • AND operator: This is used to combine two or more expressions and returns TRUE only if all the expressions are true. For example, the expression ‘sizes > 8 AND Color = 'Blue'’ will return TRUE only if the size of a shirt is greater than 8 and the color is blue.
  • OR operator: This is used to combine two or more expressions and returns TRUE if any one of the expressions is true. For example, the expression “Size > 8 OR Color = 'Red'” will return TRUE if either the size is greater than 8 or the color of the shirt in inventory is red.
  • NOT operator: This is used to reverse the logical value of an expression. It returns TRUE if the expression is false and FALSE if the expression is true. For example, the expression “NOT (size > 8)” will return TRUE if the size of the shirt is less than or equal to 8.

These logical operators can be used in combination with comparison operators such as =, <, >, <=, >=, and <> to create complex expressions that evaluate to true or false (Table 14.5).

The next example, shown in Figure 14.11, is a query designed to help create a report—in this case, you need to generate an invoice after a customer places a new order with WorldCorp. Invoices for customer sales include items such as the customer information, the date, the products ordered, price for each product, and total for the entire invoice. Refer to the chapter on Advanced Document Preparation for examples of invoices. For the report to work properly, two different criteria must be used with the logical operator AND. The first expression is [Forms]![frmOutBound2]![Document], which uses identifiers to isolate which document number the report will generate. In essence, this expression uses identifiers to query only the exact document number that is in use on the frmOutBound2 form, which is used to place all new orders from customers. By identifying the form number, the invoice will generate the exact order and only the called-upon order.

Figure 14.11 Access not only lists the fields in text, but also provides a visual representation of how elements are connected in the database. (Used with permission from Microsoft)

The second criterion is a function expression that isolates the order date to today, and today only, ensuring that the query only creates new invoices that represent new orders from today (see Figure 14.12).

Figure 14.12 Notice how several fields are used to perform the query using logical operators. (Used with permission from Microsoft)

This example shows a query designed to help show which items a T-shirt design company has in stock. Each stock item has four fields that collectively describe the piece of clothing: style, size, primary color, and graphic design on the item. In this example, a customer wants to know what graphic designs are available on a hoodie that is size XL and has a base color of green or red. These criteria combine both constants and logical operators to help isolate the data requested by the customer.

Guidelines for Using Criteria

There are best practices to follow when using criteria:

• Keep it simple. Do not try to do too much with any one query to get an answer. A query can do a great deal, one strategy is to copy previously designed queries and rename before making small changes to each copied query for a very specific purpose.
• To stay organized, be consistent in how your queries are named. When you first learn the syntax used in creating criteria, it is tempting to try to use all of the syntax in a single query. When creating a query, however, it is more important to keep the main purpose of the query in mind. If you have several questions, don’t be afraid to create multiple queries—a separate one for each question or task.
• Be sure to create tables carefully, using the correct format for each field within each table. Using good naming practices is important: A key reason that queries won’t run is likely to be the formatting of the fields rather than incorrect syntax.
• Develop a list of expressions that you know and can use for future projects. Often, these can be repeated in various queries for various uses.

Building a Query that Aggregates

Aggregate query lets you carry out calculations on record groups rather than performing individual operations. An aggregate query considers the total, subset, or gross amount of records.

Aggregation is a powerful tool because of the nature of relational data. By looking across the connected tables and through the built-in relationships, these can be accounted for when performing complex data searches and totals among a vast area of applications.

Often, the first step in building an aggregate query is to turn on the Totals tool that is found within the Query Design tab, as seen in Figure 14.13.

Figure 14.13 The Totals tool is found in the Show/Hide command group when you are designing a query. (Used with permission from Microsoft)

After you select Total from the menu, a new row, labeled Total, appears just below the field names, with an option area in the Access query, as seen in Figure 14.14.

Figure 14.14 Although the word “Total” is used, you can choose to get values other than just the sum of the numbers such as the average or the maximum number in the field. (Used with permission from Microsoft)

The default option is Group By, which groups all identical data items in the field. For example, if you wanted to know which stock item in your company’s inventory sold the most units, you could use the StockID in two ways:

1. Create a field for StockID in the query, and list the Totals as Group By. This will provide a list of each type of item you have sold.
2. Create another field, also with the StockID, but this time, choose the Sum function of the Total to provide a complete sales count for each unique item. Both fields together provide a report on which items have sold the most and the least.

Examples of Aggregate Queries

Scenario #1: You have an online business that has customers from around the country, and you want to find out which states have the fewest customers. You decide to create an aggregate query to summarize the list of states and count the number of instances in which each state appears among your current customers. Selecting the Totals option creates the following query from the customer table, as Figure 14.15 shows: Group By StateName and Count StateName.

Figure 14.15 This query will give a summary of the total number of customers in each state using the “Count” operator. (Used with permission from Microsoft)

Scenario #2: You receive two free tickets to the Super Bowl. Unfortunately, you cannot attend this year’s game, so you want to give the tickets away as a thank-you gift to the customer with whom you have done business the longest. To determine the recipient, you decide to do an aggregate query on customer orders, using date ranges to identify the longest-standing customer. The following aggregate query (shown in Figure 14.16) is then created by grouping the customer ID, using DateOut as the minimum total (first order) and then using DateOut as a maximum total to establish the last sale the customer made (last order).

Figure 14.16 More complex queries can be constructed to compare dates or other fields using the Total tool. (Used with permission from Microsoft)

In this scenario, it would be best to build an additional query to calculate the length of time between FirstOrder and LastOrder, as determined in the original aggregate query. The query has to solve both totals before it can calculate the difference in time between the two fields using the Min and Max functions.