5  Queries

OBJECTIVES

  • Types of queries and what you can do with them..
  • Creating a simple select query using the wizard.
  • Conditions in a query.
  • Grouping and calculations in a query.
  • Parameter query, update query, make-table query, and crosstab query.

Retrieving information from a database is the most common action from end-users. To deliver the required information queries are necessary. Simple queries can be created by the end-user themselves, for more complex queries help of a professional is usually desired.

5.1 About creating queries

The most familiar form of a query is the select query, which is a sort of question to the database to a particular set of data. However, a query can produce more than a list of records. So there may be functions in a query that perform calculations (sum, average,…) on the data. And there are various types of queries. In this course are discussed the types:

Select query

Retrieves data from one or more tables and displays the results in a datasheet view. You can use this query to execute records and calculations groups such as sum, average, count,… This is the most common type of query.

Parameter query

The user should first give a value for one or more fields. Thereafter, the value is used to carry out a select query. An example is a list of orders from a certain date.

Update query

Hereby it is possible to make one or more changes to records that meet certain conditions with one action. An example is a 10% price increase for a series of products.

To create a query, it is necessary that you first specify well the information needs. After this, you can start to create the query.

5.2 Customers and orders

INFORMATION NEEDS

Create a sorted list of all customers with their last name, first name, and their order codes.

ANALYSIS

The last name and first name of a customer are in table Customers. The order codes of a customer are in table Orders. Because this is a simple straightforward select query, you can use the Query Wizard.

Task 5.1 File: candy365.accdb

  1. Open the database.

  2. Choose tab Create > Query Wizard (group Queries). The dialog box New Query is displayed. Here you can select the query type.

  3. Select Simple Query Wizard and click OK. In the following screen, you can select the fields you want in your query.

  4. Select in Tables/Queries through the list box Table: Customers. The fields of table Customers are displayed in the box Available Fields.

Figure 5.1: Selection Query with table Customers.

  1. Select field LastName and click . Field LastName has been moved to box Selected Fields.

  2. Add in the same way field FirstName.

  3. Select Table: Orders in box Tables/Queries. The fields of table Orders are displayed in box Available Fields.

  4. Add field OrderCode.

Figure 5.2: Wizard selection query with selected fields.

  1. click Next. In the displayed screen you can specify if you like a detail or summary query.

  2. Select Detail (shows every field of every record) and click Next.

  3. Name the query Customername+Ordercodes, select Modify the query design, and click Finish. The design of the query is displayed.

  4. Click in box Sort of column LastName and choose Ascending.

Figure 5.3: Design query customername and ordercodes.
  1. Switch to Datasheet View.
Figure 5.4: Design query customername and ordercodes.
  1. Close the query and answer the question to save the changes withYes.

5.3 Criteria in queries

Explanation of conditions in a query, the Like operator, and wild cards.

In a query, you can define conditions, so that only those records which meet these conditions are displayed. In the design grid, the row Criteria is available for this purpose.

A criterion is similar to a formula. It is a string that may consist of field references, operators, and constants (values that are always the same). Query criteria are also called expressions. What may be formulated in a criterion depends on the data type of the field (text, numeric, date/time, yes/no).

Table 5.1: Operators in criteria
Category Operators
Arithmetic +, -, *, /, \, ^, Mod
Comparison =, >, >=, <, <=, <>
Logical And, Or, Not, Xor, Eqv
Aggregation &, +
Special Is Null, Is Not Null, Like, Between, In

You can make simple criteria with this, but also very complicated.

A special role is for the Like operator. This compares a value with a certain pattern. That pattern can be the literal string to compare with, such as Like “North”. But the pattern may also contain wild cards, such as Like “He*”. This allows the use of the operator Like very powerful.

Date values must be surrounded by the symbol #. Some examples of date criteria: #12/5/2010#, >#9/1/2010#, >#9/1/2010# and <#9/15/2010#

Wild cards are placeholders for other characters, which you use when you don’t know the entire search pattern but only a part. The three most common wild cards are:

  • *: For any number of characters. Examples: "A*", "*dam"

  • ?: For any single character. Example: "b?k"

  • #: For any single digit. Example "1#5"

External article: Examples of query criteria

5.4 Orders from Utrecht customers

Add manually fields and criteria to an existing and save the query under a new name.

To perform this task, it is necessary that you have performed Section 5.2.

INFORMATION NEEDS

Make a sorted list by last name, first name, and order codes of all customers who live in the province Utrecht and with an order date in December 2009.

ANALYSIS

All the required information will be provided by the query you created in Section 5.2. However, there are two additional conditions (criteria):

  • The field Province (in table Customers) should have the value UT.
  • The field OrderDate (in table Orders) should have a value in the range12-1-2009 until 12-31-2009.

Task 5.2 File: candy365.accdb

  1. Open the database.

  2. Open query Customername+Ordercodes inDesign View.

Figure 5.5: Design query Customername+Ordercodes.
  1. Drag field Province from table Customers to the column next to OrderCode. Drag field OrderDate from table Orders to the next column.
Figure 5.6: Fields in the design grid.

  1. Enter "UT" under Province in box Criteria.

  2. Enter Like "12\*2009" under OrderDate in box Criteria.

Figure 5.7: OrderDate with Like operator.

  • The asterisk * is called a wild card and means that in this place may be arbitrary text. So the string “12*2009” should start with 12 (= month December) end with 2009.

  • On computers where a Dutch date format (day-month-year) is set, you should change this in the American date format (month-day-year).

  1. Switch to Datasheet View and check that all customers are from the province of Utrecht and that are all order data in December 2009.

  2. Switch to Design View and uncheckShow for the columns Province and OrderDate.

  3. Switch to Datasheet View.

  4. Save the query under a different name through File > Save Object as and specify as name Utrecht and orderdate dec 2009.

  5. click OK and close the query.

5.5 Customers with box CHER

A select query with 3 tables.

INFORMATION NEEDS

In the last month of the calendar year, it is established that the sell-by date of the CHER boxes is insight. Therefore the sales department wants to organize a direct mail campaign to the customers who once bought CHER boxes. Make a list of all customers with their last name, first name, and full address whoever bought at least one box CHER.

Create a list of all customers with their last name, first name, and full address who bought at least one box CHER.

ANALYSIS

All customer information (first name, last name, address, postcode, city ) is in the Customers table. The ordered boxes can be found in the field BoxCode in table OrderDetails. In order to link an order to a customer you also need the table Orders. The table Orders is the link between the tables Customers and OrderDetails. And as condition, you need to specify the value CHER for field BoxCode.

When using the Wizard to create the query, it is sufficient to add only the needed fields from the Customers and OrderDetails tables. The Wizard then automatically ensures that the Orders table is added because it is the link between the tables Customers and Order Details. When you create the query manually from scratch, you should be self-aware to add the Orders table. That is why preference is given to the use of the Wizard in this task.

It’s not necessary to include a criterion that at least one box is ordered, because this condition is automatically set for linked tables.

Task 5.3 File: candy365.accdb

  1. Open the database.

  2. Choose tab Create > Query Wizard (group Queries). The dialog box New Query is displayed. Here you can select the query type.

  3. Select Simple Query Wizard and click OK. In the following screen, you can select the fields you want in your query.

  4. Select in Tables/Queries through the list box Table: Customers. The fields of table Customers are displayed in the box Available Fields, see Figure 5.1.

  5. Add the fields: FirstName, LastName, Address, ZipCode, City.

Select the field, and click ]{.uicontrol}. You can also double click on a field to add or remove it.

  1. Select table Table: OrderDetails in box Tables/Queries. The fields of table [OrderDetails{.varname} are displayed in box Available Fields.

  2. Add field BoxCode.

Figure 5.8: Simple Query Wizard with selected fields.

  1. click Next. Now you can specify if you like a detail or summary query.

  2. Select Detail (shows every field of every record) and click Next.

  3. Name the query Customers and CHER, select Modify the query design and click Finish. The query is saved and then appears in the design view.

Figure 5.9: Design query Customers and CHER.
  1. Enter CHER in column BoxCode and row Criteria and don’t let this field show.
Figure 5.10: Selection criteria in query design for CHER boxes.

  1. Switch to Datasheet View.

  2. Close the query and answer the question to save the changes with Yes.

5.6 Summarizing and Calculations

The simple select queries work with individual records. When you select certain customers from the Customers table with a query, then you will see a record for each customer that meets the requirements. It is also possible to group your records and then perform calculations on the subgroups. That can be compared with the calculation of totals and subtotals.

When a query is displayed in design view you see on the ribbon tab Design > Totals (group Show/Hide) the button Button totals

With this button, you can show or hide a row Total in the design grid. Access adds a Total box for each field, just under the table box.

Figure 5.11: Query design with a visible row Total.

For each added box you can choose an option from the drop-down list. This option determines whether the field is used in a calculation or used for grouping or filtering. The available options can be divided into three categories:

Grouping

The choice is then: Group By. The field is used to get smaller groups on which you can perform calculations. This choice is also the default option.

Filtering

The choice is then: Where. The checkmark in the Show box is also cleared and should remain so. In the box Criteria, you can specify the value or expression for filtering.

Calculations

The options are: Sum, Avg, Min, Max, Count, StDev, Var, First, Last, Expression. The selected calculation is then performed for the field.

Table 5.2: Options for summarizing.
Choice in Total box Description
Group By Subgroups records based on the values in this field.
Sum Adds together the values in this field.
Avg Averages the values in this field.
Min Retains the smallest value in this field.
Max Retains the largest value in this field.
Count Counts the number of records (no matter which field you use).
StDev Calculates the standard deviation of the values in this field.
Var Calculates the variance of the values in this field.
First Retains the first value n this field.
Last Retains the last value in this field.
Expression Calculates a user-defined expression for the values in this field.
Where For filtering only on values in this field.

Calculated Field

A calculated field takes data from one or more fields and performs some arithmetic to produce new information. You can perform simple arithmetic, like addition and multiplication, or use Access’s built-in functions, such as Sum and Avg. You can only use fields that are added to the query. To create a calculated field

  1. Click in an empty column in the field row.
  2. Enter a name for the calculation (result) followed by a colon (:).
  3. Enter the expression for the calculation.

  • You can use field names in the expression. Field names must be surrounded by square brackets. If the field name contains no spaces, then Access puts the square brackets in for you after entering the name. If a field name contains spaces, you have to type in the square brackets yourself.

  • When using one of the calculation options for summarizing, it is recommended to also add a new name in front of the field name, otherwise Access generates a name for the result in the datasheet view. This new name must also be followed by a colon.

Here are some examples. Study them well. Create and experiment with them.

Example 5.1 Average praline costs per chocolate type

In this example the field ChocolateType is used for grouping, creating a group for each chocolate type. The field PralineCosts is used for the calculation of the average praline costs for each group. The result is a record for each chocolate type containing the average price.

Figure 5.12: Design query average praline costs per chocolate type.
Figure 5.13: Result query average praline costs per chocolate type.

Because the column for the averages didn’t get a new name, Access generates as name AvgOfPralineCosts.

Example 5.2 Box price statistics

In this example, field BoxPrice is used 4 times with different calculations. Each column was given a new name. The result of the query is a single record containing the 4 results of the calculations.

Figure 5.14: Design query box price statistics.
Figure 5.15: Result query box price statistics.

Example 5.3 Average box price for boxes of more than 200 grams

Calculate the average price of boxes of more than 200 grams. The field Weight is used for filtering on boxes of more than 200 grams.

Figure 5.16: Design query average box price.
Figure 5.17: Result query average box price.

5.7 Customers per province

Purpose: Using a select query with grouping and a calculation with function COUNT.

INFORMATION NEEDS

Make a list of the number of customers per province.

ANALYSIS

All necessary data is in table Customers. Of course, you need the field Province. A customer is uniquely identified by the CustomerCode, so that you need to count the number of CustomerCodes in each province. For this, there must be grouped by Province.

Task 5.4 File: candy365.accdb

  1. Open the database.

  2. Choose tab Create > Query Design (group Queries). Access creates a new blank query window and displays the dialog box Show Table.

Figure 5.18: Dialog box Show Table.

  1. Select table Customers, click Add and then Close. The table Customers has now been added to the query window.

  2. Add successively the fields Province and CustomerCode to the design grid by double-clicking on the field.

Figure 5.19: Design query customers per province
  1. Click tab Design > Totals (group Show/Hide).
Figure 5.20: Design expanded with row for total.
  1. Click in the box Total under column CustomerCode and select Count.
Figure 5.21: Grouping with count.
  1. Switch to Datasheet View.
Figure 5.22: Result query customers per province.

  1. Close the query and answer the question to save the changes with Yes. The dialog box Save as is displayed.

  2. Name the query Number of customers per province and click OK.

5.8 Column heading modification

How to change the column heading in a query.

To perform this task, it is necessary that you have performed Section 5.7.

By default, Access uses the field names as column headings in the datasheet view. And for summarized data, a name is generated. It is recommended to use clearer names.

Task 5.5 File: candy365.accdb

  1. Open the database.

  2. Open query Number of customers per province in Design View.

  3. Put the cursor in the field box CustomerCode, at the beginning of the field name and type Total customers:.

Figure 5.23: Column heading modified.
  1. Switch to Datasheet View.
Figure 5.24: Result with new column heading.
  1. Close the query and answer the question to save the changes with Yes.

5.9 Calculate order amounts

INFORMATION NEEDS

Create a sorted list of order rows, showing per order: order code, box code, number of boxes, box price, and the amount for each row ( = each box).

ANALYSIS

For each order, you can find the OrderCode, BoxCode, and the number of boxes (Quantity) in table OrderDetails. The BoxPrice is in the table Boxes. The row amount is not present in any table, because this amount can be calculated from other data: Amount = Quantity * BoxPrice.

Task 5.6 File: candy365.accdb

  1. Open the database.

  2. Choose tab Create > Query Design (group Queries). Access creates a new blank query window and displays the dialog box Show Table (see Figure 5.18 if necessary).

  3. Add successively the tables OrderDetails and Boxes to the query window and then click Close.

  4. Add successively the fields OrderCode, BoxCode, Quantity (from table OrderDetails), and BoxPrice (from table Boxes) to the grid by double-clicking on the field.

Figure 5.25: Design with tables and fields.
  1. Set the sorting order for fields OrderCode and BoxCode on Ascending. Click in the field row of the first empty column and enter Amount: Quantity\*BoxPrice. Access surrounds the fieldnamess with square brackets.
Figure 5.26: Design expanded with calculated field.
  1. Switch toDatasheet View.
Figure 5.27: Result without formatting currencies.
  1. Switch to Design View.

All objects in Access have properties. These properties determine, among other things, the appearance of the object. The setting of the properties is possible in the Property Sheet. You can switch the visibility of the Property Sheet on and off with tab Design > Property Sheet (group Show/Hide). Faster is to using the keyboard shortcut F4.

To format the amounts as currencies, you need to change the property Format of field Amount.

  1. Make sure that the Property Sheet is visible. Click somewhere in field Amount. Click in the box Format and choose with the list box the setting Currency
Figure 5.28: Properties of field Amount.
  1. Switch toDatasheet View.
Figure 5.29: Result with amounts formatted as currency.

  1. Close the query and answer the question to save the changes with Yes.

  2. Name the query OrderRowAmount and click OK.

5.10 First order per customer

INFORMATION NEEDS

Create a list of the first order per customer. Show for each customer who has placed one or more orders: customer code, customer name (last and first name), and the date of the first order.

ANALYSIS

The required data is in the tables Customers and Orders. There you need to create a query showing the customer data and order data. Finding the first order can be realized by changing Group By with Min in row Total.

Task 5.7 File: candy365.accdb

  1. Open the database.

  2. Choose tab Create > Query Design (group Queries).

  3. Add successively the tables Customers and Orders to the query window and then click Close.

  4. Add successively the fields CustomerCode, LastName and FirstName (from Customers), and OrderDate (from Orders) to the grid by double-clicking on the field.

  5. click tab Design > Totals (group Show/Hide).

  6. Change column title for OrderDate by entering in front of the field name First order date:.

Figure 5.30: Tables and fields for query First order date.

  1. Click in the box Total under column OrderDate and select Min.

  2. Set the sort order for LastName and FirstName on Ascending.

Figure 5.31: Design query first order per customer.
  1. Switch toDatasheet View.
Figure 5.32: Result query first order date.

  1. Close the query and answer the question to save the changes with Yes.

  2. Name the query First order dates and click OK.

5.11 Parameter query

A parameter query is a query that displays a dialog box that prompts the user to provide additional information during the execution, such as criteria for retrieving records or a value that you want to insert into a field. You can design the query so that multiple data is requested, for example, a start and end date. Then all the records can be retrieved with dates between them.

Parameter queries are also easy as a basis for forms and reports. Based on a parameter query, you can for example create a monthly revenue report. When printing the report you are prompted by a dialog box which month you want to print, you enter the month, and then the correct report will be printed.

INFORMATION NEEDS

In company Snoopy one gets regular customer questions over the phone about a particular order. You want to quickly be able to answer such a question. The goal now is to get the data from a particular order quickly on the screen. That is possible with a parameter query, asking for the order code during the execution of the query.

ANALYSIS

The necessary information about a particular order is in the tables Orders and OrderDetails. Questions about the order code can be controlled via a criterion.

Task 5.8 File: candy365.accdb

  1. Open the database.

  2. Choose tab Create > Query Design (group Queries).

  3. Add tables Orders and OrderDetails to the query window and then click Close.

  4. Add successively the fields OrderCode, CustomerCode, OrderDate (from Orders), and BoxCode en Quantity (from OrderDetails) to the grid by double-clicking on the field.

Figure 5.33: Tables and fields for query.
  1. Click in the box Criteria under column OrderCode and type [Enter order code].
Figure 5.34: Criterion added to the query.

  1. Switch to Datasheet View. The dialog box Enter Parameter Value appears and asks you to enter the order code.

  2. Enter a value, e.g. 30, and click OK.

Figure 5.35: Results for order with order code 30.

  1. Close the query and answer the question to save the changes with Yes.

  2. Name the query Information specific order and click OK.

5.12 Action Queries

The majority of queries are select queries, which are used to search, collect, and display data, but not to change this data. But Access has also another category of queries with which you can change deleting, updating, or adding records, known as action queries. The big advantage of an action query is that it can change a large number of records without having programming knowledge. The way you make these queries and the way of working is almost the same: first, create a select query and then change the query type.

Access has four types of action queries:

Make Table

Selects one or more records and then create a new table for them. This new table can be placed in the opened database, but can also as a new table to be made in a different database. You can use a Make Table query for example to copy outdated data to an archive database.

Append

Selects one or more records and then adds them to another existing table. For example, suppose that you acquire some new customers and the information has been stored in a separate table. With an append query, you can move the records to your customers table.

Delete

Deletes one or more records. You specify a set of filter conditions and then deletes the matching records. For example, you can remove products that are discontinued.

Update

Change values in one or more records. The existing values in a field are replaced by new values, a type of search and replace. You can’t undo the changes and therefore it is advisable to first backup (copy) of the database or the table before you run the update query.

Because these queries change data in the database, these queries could be a security risk. To provide protection, a number of checks are carried out in Access and the Trust Center. The trust center can disable the content. When opening such a database, Access displays a message with a security warning.

Figure 5.36: Message with security warning.

If you want to enable the content, click Enable Content > Options and choose the desired option in the dialog box that appears. The database is opened again with full functionality.

It is also advisable to make a backup of the tables that are changed. That ’s easy to do with copy and paste.

Example 5.4 Creating a copy of a table

  1. In the navigation pane, right-click on the name of the table and select from the shortcut menu Copy.
  2. Right-click again and choose now Paste and give the new table a different name.

To recover a table after a change, proceed as follows:

  1. In the navigation pane, right-click on the name of the changed table and select from the shortcut menu Cut.
  2. Right-click on the name op the copy and choose Rename and give the table the original name.

5.13 Update query

An example of a simple update query that changes the values in a field for all records which satisfy a certain condition.

INFORMATION NEEDS

The costs of all pralines with chocolate type white should be increased by 10%.

ANALYSIS

All necessary data is in table Pralines. You need the fields ChocolateType and PralineCosts. Selecting the white chocolate types can be done by adding a criterion to the query. You can increase the PralineCosts by 10% by multiplying the current value with 1.1.

Make a backup (copy) of the table, so you can restore the original situation. Another possibility is to backup (copy) the whole database.

Task 5.9 File: candy365.accdb

  1. Open the database.

  2. Choose tab Create > Query Design (group Queries).

  3. Add table Pralines to the query window and then click Close.

  4. Add successively the fields ChocolateType and PralineCosts to the grid by double-clicking on the field.

  5. Change the query type with tab Design > Update (group Query Type). The Sort and Show rows disappear and there is a new row Update To.

Figure 5.37: Tables and fields for update query.

  1. Click in the box Criteria under column ChocolateType and enter White.

  2. Click in box Update To under column PralineCosts and type [PralineCosts]*1.1.

Figure 5.38: Design update query.

Access surrounds the text with double-quotes. Field names must be surrounded by square brackets.

  1. Save the query with button Save () on the Quick Access Toolbar and name the query Increase costs white chocolates with 10%.

  2. click tab Design > Run (group Results). A dialog appears asking for confirmation.

  3. click Yes.

  4. Close the query.

5.14 Make Table query

Purpose: Creating a new table with the Make Table query.

INFORMATION NEEDS

The marketing department has a special offer for all customers in Friesland. For this, they need a table with only the customer data for customers in the province Friesland.

ANALYSIS

All necessary data is in table Customers. Selecting for Friesland can be done with a criterion in the query. First, you need to create this select query and then change the query type to Make Table.

Task 5.10 File: candy365.accdb

Create select query

  1. Open the database.

  2. Choose tab Create > Query Design (group Queries).

  3. Add table Customers to the query window and then click Close.

  4. Add all fields by double-clicking on the asterisk (*) and then add separate again the field Province.

  5. Enter as criterion "FR" for the field Province and don’t show this extra field, because it is in fact already shown by Customers.

Figure 5.39: Design Make Table query.

  1. Check the output of the query with the Datasheet View.

  2. Save the query with the name Customers Friesland.

Change the query type

  1. Open the query Customers Friesland in Design View.

  2. click Design > Make Table (group Query Type). The dialog box Make Table opens.

  3. Name the new table Friesland customers and select that this table should come in the current database.

Figure 5.40: Specify name and location of the new table and the database.

  1. click OK.

  2. click tab Design > Run (group Results). A dialog appears asking for confirmation.

  3. click Yes. The new table is created.

  4. Close the query. A dialog box asking whether the changes should be saved.

  5. click Yes.

This is because the query type has been changed. This is also seen in the navigation pane under Queries.

The query icon in front of the query name indicates that it’s an action query:

5.15 Crosstab query

A crosstab query calculates a sum, average, or other aggregate function, and then groups the results in rows and columns. A cross table is similar to an Excel PivotTable report. A crosstab query is often easier to read than a regular select query with the same data. The overview is more compact by the horizontal and vertical alignment.

When you create a crosstab query, you specify which fields contain row headings, which field contains column headings, and which field contains values to summarize. For the row headings you can use multiple fields (maximum 3), but for the column heading values and the values to summarize you can use only one field. It’s also possible to use an expression for the row heading(s), the column heading(s), and to summarize values.

The easiest and fastest way to create a crosstab query is using the Crosstab Query Wizard. For more complex queries, you can often start with this Wizard, and then do the fine-tuning in the Design View.

INFORMATION NEEDS

Calculate the number of customers by province and by region and show the result in a cross table.

ANALYSIS

All the necessary data can be found in table Customers.

Task 5.11 File: candy365.accdb

  1. Open the database.

  2. Choose tab Create > Query Wizard (group Queries) > Crosstab Query Wizard > OK.
    In the screen that is displayed now, you can select the table or query that contains the fields for the crosstab.

  3. Select table Customers and click Next. Now you can select the field(s) for the row heading values.

  4. Add field Province.

Figure 5.41: Selection of field for row heading.

  1. click Next. Now you have to select the field for the column heading values.

  2. Select field Region.

Figure 5.42: Selection of field for column heading.

  1. click Next. Now you can select the field whose values you want to aggregate and the aggregate function.

  2. Select field CustomerCode and function Count. Also, deselect the option to include row sums.

Figure 5.43: Selection of field and function for summarizing.

  1. click Next.

  2. Name the query Number of customers by province by region. Select View the query, and click Finish.

Figure 5.44: Crosstabb result.

5.16 Exercises

Exercise 5.1 Customers from Friesland and Groningen (quer001)

Create a query to select customers in the provinces of Friesland and Groningen. Show LastName, FirstName, and City. Name the query quer001.

The field Province may have two values here FR or GR. You can get done this by entering one value in the row Criteria and the other value in the row or.

Another possibility is using an expression with the Or operator in row Criteria.

Result query: 40 customers

Exercise 5.2 Direct mail campaign box MARZ (quer002)

Suppose it is the end of December 2010. The expiration date of the box MARZ is in sight and there is still plenty in stock. That’s why do you want to start a direct mail campaign to the customers who have ordered at least one box MARZ in the period August-November 2010. Show the name and address of the customers. Name the query quer002.

Result query: 4 customers

Exercise 5.3 Praline boxes with low price (quer003)

Create a list of boxes whose price is at most $ 17.50. Show the fields BoxCode, BoxName, and BoxPrice. Name the query quer003.

Result query: 4 boxes

Exercise 5.4 Pralines milk and dark with low price (quer004)

Create a list of pralines with chocolate type milk or pure and whose costs are at most 30 cents. Show the fields PralineCode, PralineName, ChocolateType, and PralineCosts. Name the query quer004.

Result query: 18 pralines

Exercise 5.5 Customers from Enschede, Hengelo or Almelo (quer005)

Create a list of all the customers (name and address) who live in Enschede, Hengelo, and Almelo. Name the query quer005.

Result query: 12 customers

Exercise 5.6 Customers from Amsterdam with zip code 20* (quer006)

Create a list with customers from Amsterdam (name and address) whose zip code begins with 20. Name the query quer006.

Result query: 3 customers

Exercise 5.7 Pralines without filling (quer007)

Create a list of all pralines without filling. Name the query quer007.

Try to find first what value the field Type has for pralines without filling.

Result query: 12 pralines

Exercise 5.8 Customers outside Amsterdam (quer008)

Create a list of all customers who do not live in Amsterdam. Name the query quer008.

First find out what operator to use in a test for not equal to.

Result query: 293 customers

Exercise 5.9 Number of pralines per box (quer009)

Create a list of all box names with their number of pralines per box. The column with numbers should have an appropriate title. The list should be sorted in ascending order of box name. Name the query quer009.

Exercise 5.10 Number of praline types per box (quer010)

Create a list of box names and their number of praline types per box. The column with numbers should have an appropriate title. The list should be sorted in ascending order of box name. Name the query quer010.

Exercise 5.11 Number of orders per region (quer011)

Create a list of the number of orders per region. The column with numbers should have an appropriate title. Name the query quer011.

Exercise 5.12 Total sales per customer (quer012)

Create a list with the total sales per customer. Show customer code, customer name, and the total sales. The column with sales should have an appropriate title and formatting. Sort on descending sales. Name the query quer012.

Exercise 5.13 Customers with orders (quer013)

Create a list of customers (code and name) who have ever bought something. Name the query quer013.

Result query: 264 customers

Exercise 5.14 Customers with last name Jansen or Janssen in region North (quer014)

Create a list of customers with last name Jansen or Janssen in the region North. Name the query quer014.

Exercise 5.15 Pralines with filling and without nut (quer015)

Create a list of pralines with filling and without nuts. Name the query quer015.

Result query: 22 pralines

Exercise 5.16 Boxes heavier than 150 grams with a maximum price of $35 (quer016)

Create a list of boxes heavier than 150 grams with a maximum price of $35. Show only BoxCode, BoxName, and BoxPrice. Sort the boxes by ascending price. Name the query quer016.

Exercise 5.17 Boxes with a price between $17 and $25 and stock of at least 400 (quer017)

Create a list of boxes with a price between $17 and $25 and of which there are at least 400 in stock. Show the code, name, price, and stock of the boxes. Name the query quer017.

Exercise 5.18 Total sales per province (quer018)

Create a list of total sales per province. The column with sales should have an appropriate title and formatting. Sort descending on sales. Name the query quer018.

Exercise 5.19 Customers with orders the week before Christmas (quer019)

Create a list of customers who ordered the last week before Christmas 2010 (12/20/2010 until 12/24/2010). Show code, name, and address of these customers. Each customer may occur only one time in the overview. Name the query quer019.

The same customer may exist on the list more than one time. To avoid this, the value of the query property Unique Values should be Yes.

Exercise 5.20 Boxes with low sales (quer020)

Create a list of boxes with low sales, less than $2000. Show box code and sales. Sort ascending on sales. The column with sales should have an appropriate title and formatting. Name the query quer020.

Exercise 5.21 Customers with zip code 22 and total sales less than $50 (quer021)

Create a list of customers whose zip code is starting with 22 and with a total sales of at most $50. Name the query quer021.

Exercise 5.22 Customers for Sweet and Bitter (quer023)

Create an alphabetic list of customers who bought in 2009 at least 1 box “Sweet and Bitter”. Avoid double records in the result. Name the query quer023.

The same customer may exist on the list more than one time. To avoid this, the value of the query property Unique Values should be Yes.

Result query: 96 customers

Exercise 5.23 quer024 - Cities with at least 2 customers (quer024)

Create an alphabetic list of cities with at least 2 customers. Name the query quer024.

Exercise 5.24 Parameter chocolate type pralines (quer025)

There are several types of chocolate types for the pralines: Butterscotch, Milk, Dark, Toffee, and White. Create a parameter query to show the pralines whose type is requested during the execution of the query. Show ChocolateType, PralineName, PralineCosts and PralineDescription. Name the query quer025.

Exercise 5.25 Update query price reduction (quer026)

Due to a decline in the price of chocolate in the world market, the prices of all boxes can be reduced by 15%. Name the query quer026.

  1. Start with making a backup or copy of the whole database.
  2. Design an update query to implement this reduction. Name the query quer026 and run the query.
  3. Restore the original situation from the backup.
  • Making a backup is important because you cannot revert the execution of the query.
  • Be aware that every time you run the query, the prices are again reduced by 15%.

Exercise 5.26 Crosstab boxes per weight (quer027)

Create a crosstab query with vertical the box names, horizontal the weight of the boxes, and in the table the stock. Name the quer027.

Exercise 5.27 Update query price increase (quer028)

Due to an increase in the chocolate price on the world market, prices of all boxes must be increased by 10%.

  1. Start by making a backup or copy of the table or of the entire database.
  2. Design an update query to implement the price increase. Name the query quer028 and execute it.
  3. Restore the original situation.
  • Making a backup is important because you cannot revert the execution of the query.
  • Be aware that each time you execute the query, the prices are further increased by 10%.

Exercise 5.28 Crosstab sales per customer per month (quer029)

Create with a crosstab query an overview of the total sales per customer per month in 2010. To do that, create first a selection query Sales per customer per month in 2010 and use this query as source for the crosstab query. Display customers with the customer number and the months with the month numbers. Display also the total sales for the customer in 2010. Name the crosstab query quer029.