Select Queries Part 4: PARAMETERS, TOP, DISTINCT, and TRANSFORM...PIVOT

This is my final post in this series about Select Queries.

In Select Queries Part 1: Simple Queries, I discussed the SELECT and FROM clauses. In Select Queries Part 2: Restricting Rows - the Where Clause, I talked about the WHERE clause. Select Queries Part 3: Sorting and Grouping (ORDER BY, GROUP BY) showed the Order By and Group By clauses.

 

This time I'm going to discuss a few lesser known but powerful clauses: Top, Distinct, Transform...Pivot, and Parameters.

PREDICATE (optional)

Another less well known section of a SQL statement is the Predicate. The Predicate follows the SELECT keyword and precedes the Field List. There are two important predicates: TOP and DISCTINCT.

Top Predicate

The Top Predicate allows you to display the top(x) values from a query in terms of either a number or percentage. I can't really talk about the TOP predicate without discussing the ORDER BY clause because the rows displayed are determined by the ORDER BY clause.

Examples:

SELECT TOP 10 ProductName
FROM Products
ORDER BY Cost

This will display ten rows of ProductNames whose costs are the LOWEST.

SELECT TOP 25% ProductName
FROM Products
ORDER BY Cost DESC

This will display 25% of the total number of rows of the Product table whose costs are HIGHEST.

In other words, the query creates a standard Select Query, applies the sort order in the ORDER BY clause, then displays just the top X values from the query.

To add a TOP predicate in the Query Builder, go to the Properties of the Query and look for the Top Values property:

A word about duplicates: If there are duplicates in Top rows, they will all be displayed. So, sometimes Top 10 could return 11 or even more rows. It is possible to remove these duplicates with a subquery. To do that and a lot of interesting things with the TOP predicate, download a free database sample from my website called: TopQuery which goes into more detail including:

• Removing Duplicates.
  
• Top values with Aggregates (Totals Query)
  
• Top values per Group
  
• Returning Random X records from your table.
  
• User input TOP value (parameter).

DISTINCT Predicate

The DISTINCT predicate will remove duplicates from your result set based on the fields in your Field List. For instance, if I had a Products table that looked like this:

ProductName Cost
----------- ----
Ax           $3
Hammer       $15
Hammer       $10
Wrench       $5
Wrench       $4

SELECT DISTINCT ProductName
FROM Products

Would return:

ProductName
-----------
Ax
Hammer
Wrench

Removing the duplicated rows. The effect is the same as using GROUP BY clause without an aggregate function:

SELECT ProductName
FROM Products
GROUP BY ProductName;

DISTINCTROW

The DISTINCTROW predicate is unique to Access. It will return unique records across the entire record, not just the fields in the Field List.

SELECT DISTINCTROW ProductName
FROM Products

returns:

ProductName
-----------
Ax
Hammer
Hammer
Wrench
Wrench

Honestly, though, I've never found a good use for DISTINCTROW because I always have a Primary Key in all my tables, so all of the rows are already unique.

To create either the DISTINCT predicate in the Query Builder, set the Unique Values property to Yes, to create the DISTINCTROW predicate set the Unique Rows value to Yes.

TRANSFORM...PIVOT (Crosstab Query)

The Crosstab Query is an Access Specific query that allows you to display recordset results in a much more compact form. For instance, if I had an OrderDetail table that looked like this:

I could use a Crosstab Query to show the data like this:

The Crosstab Query used two clauses, the TRANSFORM, which comes before the SELECT statement, and the PIVOT, which follows the GROUP BY. In SQL View, it looks like this:

TRANSFORM Sum(Quantity) AS SumOfQuantity
SELECT CustomerNum, OrderID
FROM OrderDetail
GROUP BY CustomerNum, OrderID
PIVOT ProductName;

In the Query Builder, it looks like this:

The easiest way to learn Crosstab Queries is to use the Crosstab Wizard that's available when you create a New Query.

PARAMETERS Clause (Optional)

Queries can also accept user input. This input is called a Parameter. Parameters are placed in the WHERE clause of a SQL statement and takes the form of a Prompt in square brackets. For example:

SELECT * FROM Customers
WHERE CustomerNum [Enter Customer Number]

When the query is run, a parameter input box appears:

When the user inputs a valid customer number, the query returns the row(s) applicable to the customer entered.

A parameter can also reference a control on a form. Suppose I have a Customer form and I want to see all of the orders associated with a particular customer. I could have a query that looks at the CustomerNum in the current form and return all of the order numbers for that customer.

So if I had a query that looked like this:

SELECT CustomerNum, OrderNumber
FROM Customer INNER JOIN Orders
ON Customer.CustomerID = Orders.CustomerID
WHERE CustomerNum=[forms]![frmCustomer]![CustomerNum]

It would look at the value in the CustomerNum text box on the frmCustomer form and use that as the parameter value to return all the orders for customer 100.

Access does a pretty good job of figuring out what the data type of a parameter is. However, there are times when it gets confused. This is particularly true when trying to use parameters with a Crosstab query. In these cases, you can define what the data type of the parameter will be with the PARAMETERS Clause.

The PARAMETERS clause goes before the SELECT statement in a Select query:

PARAMETERS [Enter Customer Num] Text(255);
SELECT Customer.*
FROM Customer
WHERE CustomerNum=[Enter Customer Number];

Notice the semi-colon at the end.

In a Crosstab query, it goes before the TRANSFORM statement:

PARAMETERS [Enter CustomerNum] Text(255);
TRANSFORM Sum(Quantity) AS SumOfQuantity
SELECT CustomerNum, OrderID
FROM OrderDetail
WHERE CustomerNum=[Enter CustomerNum]))
GROUP BY CustomerNum, OrderID
PIVOT ProductName;

To create parameters in the Design View, choose Parameters from the Design Ribbon (A2007) or from the Query>Parameters Menu (A2003 and before). You will get a dialog box that looks something like this:

.