The first step in creating a delete query is to create what other type of query?

Example: Select vs. Crosstab Query Results

Suppose you have a family called Pumps, which stores data on Pump Location, Pump Manufacturer, and Pump Failures. If you queried the family and included the location, manufacturer, and failures fields:

• A Select query would display the results as shown in the following table.

  Pump Location	Pump Manufacturer	Pump Failures
  Zone 1	ACME	3
  Zone 1	SUPER	5
  Zone 2	ACME	4

  ...where each field appears as a separate column of information.

• A Crosstab query would display the results as shown in the following table.

  Zone	ACME	SUPER
  Zone 1	3	5
  Zone 2	4	NULL

  ...where locations appear as rows, and manufacturers appear as columns.

In this example, you can see that ACME is the manufacturer of multiple pumps. The manufacturer, therefore, represents the category by which you want to display the remaining data (pump location). Each column in the results grid represents a separate value within the same category. So, in this example, ACME and SUPER are different types of manufacturers within the manufacturer category.

Example: Crosstab Query

When management personnel request that work be performed on a piece of equipment, the work results in some amount of downtime for the piece of equipment. Some work activities result in longer amounts of downtime than others. In your company, work requests are recorded in Work Request records, which contain the following fields:

• Work Request ID Identifies the work request with a unique value.
• Work Activity Indicates the type of work that should be performed (e.g., repair).
• Downtime Indicates the total amount of time that the equipment was out of service while work was being performed on it.

If you were to create a Select query to view information about work requests that have been completed, the results might look something like those shown in the following image.

In these results, you can see each work request ID, the corresponding work activity, and the total amount of downtime per request. The format of these results, however, does not display the work requests grouped by activity type. While this result set is small, which allows you to visually determine how many work requests fall into each activity type, more typical query results will contain enough rows of data that it will be difficult to divide it into categories by visually comparing the data. This is especially true when the results span multiple pages.

To group the results such that you can see at a glance how many work requests fall into each activity type, you could decide to make the query an aggregate query. If you use the COUNT function on the Work Request ID field and the SUM function on the Downtime field, the query results would look similar to those shown in the following image.

In these results, you can see that three work requests asked for an adjustment, three requests asked for something to be cleaned, and four work requests asked for a repair. You can also see that the total amount of downtime for all adjustments was four days, the total amount of downtime for all cleaning tasks was six days, and the total amount of downtime for all repairs was 20 hours.

While the stored data is interesting when viewed in this format, you might be more interested in determining which work requests resulted in a downtime over a certain number of days. For instance, suppose that you expect repairs to take over seven days, but cleaning tasks that take more than seven days are unacceptable to management personnel. You might want to construct the query such that it groups the raw data into two categories: downtime and work activity. You then want to determine how many work requests in each type of activity resulted in downtime between one and seven days, and how many resulted in downtime over seven days.

The results of a Select query cannot present the data in this format. To format the data such that it provides the desired information, you must create a Crosstab query, where you can:

• Convert the stored downtime values into categories: 1 to 7 Days and Over 7 Days.
• Determine the total amount of downtime per work activity.
• Divide the total amount of downtime per work activity into the predefined categories of 1 to 7 Days and Over 7 Days.

The Crosstab query will contain the same fields as the Select query: Work Request ID, Work Activity, and Downtime.

To convert the stored downtime values into categories, however, you will need to add another column that includes a DECODE statement that uses the SIGN function.

The DECODE statement would look like this:

                    Decode(SIGN(([Work Request].[Downtime] - 7)), -1, '1-7 Days', 0, '1-7 Days', 'Over 7 Days')
                

This statement indicates that:

• First, the value seven should be subtracted from the actual downtime values. Because the Downtime field has a unit of measure of Days, and there are seven days in a week, each downtime value will be greater than or equal to zero and less than or equal to seven. After subtracting the value seven from these stored downtime values, the calculated result will be either a negative number, zero, or a positive number.
• After subtracting seven from the actual downtime value:
  • If the value is negative or zero, the record should be grouped into the category 1 to 7 Days. This means that any work request with the following downtime values will be grouped into the 1 to 7 Days category: 0, 1, 2, 3, 4, 5, 6.
  • If the value is anything other than zero or a negative number, the record should be grouped into the category Over 7 Days. This means that any work request with a downtime value greater than or equal to eight will be grouped into the category Over 7 Days.

In the grid in the Conditions section, the Work Activity field will be the row heading, and the column with the DECODE statement will be the column heading. The Work Request ID field will be the intersecting field, or the Value, and a COUNT function will be defined in the Total cell for the Work Request ID field. This means that the intersecting cell in the results will contain a number instead of a Work Request ID. The number will indicate the number of work requests that fall into the category defined by the intersection of the row and the column (e.g., the number of repair work requests that resulted in a downtime of over seven days).

In addition, the SUM function will be defined in the Total cell for the Downtime field. This will ensure that the results contain only one row representing each work activity instead of multiple rows containing the same work activity. For example, if there are three repair requests, because the SUM function is defined on the Downtime field, the results will contain only one row representing the repair work type (displaying the total number of work requests of that type) instead of three rows representing the repair work type (displaying only one work request of that type for each row).

The results will be grouped as shown in the following table.

What is a query write the steps to create a DELETE query?

What is the first step to DELETE a record from a table?

Which of the following is the first step to create an update query?

What is the first step to DELETE a record from a table quizlet?