Top Query Revealed: Aggregate Values and Top Values By Group

Top Query Revealed: Aggregate Values and Top Values By Group

Last time, in Top Query Revealed: Simple Top Query, I talked about the simple top query. A simple top query returns the top values based on individual field values. I showed how this query:

SELECT TOP 10 Account, OrderNum,
[Pairs Shipped], [Total Price]
FROM Invoices
ORDER BY [Total Price] DESC;

Returned the following results:

Figure2: Results of simple Top query.

Top Query based on Aggregate Values

As useful the simple top query is, what if I wanted to find the top 5 Accounts by total sales rather than individual invoices? The solution is to use a Totals (or Aggregate) query.

SELECT TOP 5 Account,
Sum([Total Price]) AS [SumOfTotal Price]
FROM Invoices
GROUP BY Account
ORDER BY Sum([Total Price]) DESC;

Notice the addition of the Group By clause and the Sum function. The result of this query can be seen in Figure 3.

Figure 3: Result of top 5 accounts by total sales.

Another useful thing to do with a Top query is to return the top values for each of a group of data. For instance, suppose instead of the top 3 values for a particular Account, I wanted the top 3 values for all Accounts.

Top Query By Group

To do this, I need a correlated subquery. A subquery is a SELECT statement nested inside a SELECT, SELECT...INTO, INSERT...INTO, DELETE, or UPDATE statement or inside another subquery. A correlated subquery opens a separate instance of the table for each record in the main query, allowing you to compare the results of the subquery to results from the main query.

In this case, my subquery looks like this:

SELECT TOP 3 [Total Price]
FROM Invoices I2
WHERE Invoices.[Account] = I2.[Account]
ORDER BY I2.[Total Price] DESC

The key here is the From clause:

FROM Invoices I2

This creates an "alias" for the table, renaming it I2. This is important because when we embed this query within another query also based on the Invoice table, it needs to know which instance of Invoices we are asking for.

If we run the subquery as is, it will ask us for the value of Invoices.[Account] in a parameter prompt. If we give it an account number, say 391002, it will return the top 3 values for that account.

But when we embed this query in another query, instead of prompting us for the account number, it will take the account number from the each record of the main query, match it to the subquery, and return the top values for that record. The complete query looks like this:

SELECT Account, OrderNum, [Total Price]
FROM Invoices
WHERE [Total Price] In
(SELECT TOP 3 [Total Price]
FROM Invoices I2
WHERE Invoices.[Account] = I2.[Account]
ORDER BY I2.[Total Price] DESC)
ORDER BY Account, [Total Price] DESC;

And returns the values in Figure 4.

Figure 4: Shows the result of the query to show the top 3 invoices for each Account.

Notice, however that account 237001 has 4 values. Why is that? Because two records have the same value, Access cannot decide which to display, so it displays both. Duplicates can happen in both simple and group top queries, but the solution is different for each. I'll address that next in Top Query Problem: Ties.

A free sample illustrating the Top Query can be found here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=233.

.

Top Query Revealed: Simple Top Query

Top Query Revealed: Simple Top Query

Introduction

SQL, the query language of Microsoft Access, has many powerful, yet little known features. One of these is the Top Query. A Top query allows you to limit the results of a query to a certain number (or percentage) of records.

The term "top" is deceiving, because it implies "largest" values. However, this is not strictly the case. You can choose the top largest values or the top smallest values. As I'll show later, you can even choose random records.

Simple Top Query

Suppose I want to create a query which shows me the top 10 invoices (based on price) in my Invoices table.

To create a TOP query in SQL, add the TOP predicate immediately following the SELECT keyword followed by the number of records you want to see:

SELECT TOP 10 * FROM Invoices

But that's not the end of it. To define what is "top", you must sort on one or more fields, using use DESC for largest numbers and ASC for smallest numbers. (Technically, you don't need to add ASC if you wanted the smallest numbers because ascending order is default.) Adding that, the whole query looks like this:

SELECT TOP 10 *
FROM Invoices
ORDER BY [Total Price] DESC;

This will return 10 records with the largest values in the Total Price field. But you can also choose to see the top (or bottom) percent. So if I wanted to see the top 5% best sellers, I could do this:

SELECT TOP 5 PERCENT *
FROM Invoices
ORDER BY [Total Price] DESC;

Although I used the asterisk (*) in the above queries, I did so only for simplicity. Like any query, you can also include a field list in your query, like so:

SELECT TOP 10 Account, OrderNum,
[Pairs Shipped], [Total Price]
FROM Invoices
ORDER BY [Total Price] DESC;

You are not limited to the SQL view of a query to create a Top query, however. To create a TOP query in the Query Builder, open the Query Properties and look for the TOP property. See Figure 1.

Figure1: Shows the Query Builder window to create a Top query.

The drop down box gives you suggestions, but you are not limited to the values in the list. Choosing All, removes the Top predicate from the SQL statement and returns the query to a normal Select query.

The results of this query, whether created in the Query Builder or directly in SQL can be seen in Figure 2.

Figure2: Results of simple Top query.

Simple Top Query With Where Clause

I'm not limited to showing the top values for all invoices. I can also narrow the scope of the query with a Where clause just as you can with any Select query. For instance, if I wanted to see the top 3 invoices of a single Account (say, 237001), my query would look like this.

SELECT TOP 3 Account, OrderNum, [Total Price]
FROM Invoices
WHERE Account=237001
ORDER BY [Total Price] DESC;

Result of top 3 invoices for Account 237001.

As useful as this is, what if I wanted to find the top 10 Accounts in terms of total sales rather than individual invoices? Or suppose instead of the top 10 values for a particular Account, I wanted the top 10 values of each Account. I'll talk about that next time in: Top Query Revealed: Aggregate Values and Top Values By Group.

.

This Recordset Is Not Updateable. Why?

This Recordset Is Not Updateable. Why?
by Roger Carlson

Introduction

Non-updateable recordsets are a problem that may have many causes and may produce many different error messages. Some of those include:

"This recordset is not updateable."
"Operation must use an updateable query."
"Recordset is not updateable" (seen in the status bar of a query, form, or datasheet view of a table.)

What does this mean? Well, sometimes you can edit data in the Datasheet View of a query to change the information in the underlying table. Other times, you can't. When you can't, the query is "non-updateable". When you try to create a recordset object based on a non-updateable query, the recordset becomes non-updateable.

The Microsoft Office Access Help system has a fairly extensive list that details when queries are updateable and non-updateable. However, this list is difficult to find. It is also in different places depending on the Access version you're using. You would think that typing "updateable recordset" would find the information, but it doesn't.

In Access 2003, you can find this information if you type: "When can I update data from a query?"
In Access 2007, type: "edit data in a query"

I thought it would be useful to list the information in a place that's a little easier to find.

When Recordsets Are Always Updateable

A recordset is always updateable when:

1. It is based on a single table.
2. It is based on a query based on a single table.
3. It is based on a query based on tables with a one-to-one relationship.

When Recordsets Are Never Updateable

A recordset is never updateable when:

1. It is based on a Crosstab query.
2. It is based on a Union Query.
3. It is an Aggregate Query that calculates a sum, average, count or other type of total on the values in a field.
4. It is an Update Query that references a field in the Update To row from either a crosstab query, select query, or subquery that contains totals or aggregate functions
   Note: By using a domain aggregate function in the Update To row of an update query, you can reference fields from either a crosstab query, select query, or subquery that contains totals or aggregate functions.
5. It is based on a Query that includes a linked ODBC table with no unique index.
6. The database was opened as read-only or is located on a read-only drive.
7. It is a SQL pass-through query.
8. It is a query whose UniqueValues property is set to Yes. (That is, it is a query with a DISTINCT predicate.)
9. Cartesian Joins (that is, a query that includes more than one table or query, and the tables or queries aren't joined by a join line in Design view.)
10. Query based on three or more tables in which there is a many-to-one-to-many relationship.
    Note: Though you can't update the data in the query directly, you can update the data in a form or data access page based on the query if the form's RecordsetType property is set to Dynaset (Inconsistent Updates).
11. Calculated fields. Even if the query itself is updateable, if a column in a query is based on a formula, the field cannot be updated. However, if the other fields in the formula are updated, the calculated field will automatically update.

Recordsets Are Updateable Under Certain Conditions

Some queries, especially those involved in a Join, will not be updateable under some conditions, but will be under others. In other queries, even if the query itself is updateable, some of the fields will not be. The following are cases of query problems and their corresponding solutions.

1. Query based on a Join of tables with no Relationship.

• Problem: If a query is based on two or more tables that DO NOT have a relationship established (with Referential Integrity enabled), the query will be non-updateable.
• Solution: Create a Primary Key or Unique Index on ALL of the fields used in the Join on the "one-side" table. To be clear, this means ONE primary key or unique index based on all of the fields, not separate indexes on each field.

In a query based on a Join of tables with a one-to-many relationship (1:M), you might not be able to edit the data in one or more fields. As the following examples show :

2. Join field from the "one" side

• Problem: If you have a 1:M relationship created between two tables, you cannot change the primary key field (used in the Join) of the table on the "one" side of the relationship.
• Solution: Enable cascading updates between the two tables.

3. New records, if the "many" side join field doesn't appear in the datasheet

• Problem: In a query based on a 1:M relationship, you can create a new record and fill in the fields that come from the "one" side table, but if the join field from the "many" side table is not visible in the query (that is, the foreign key), you cannot add data to the "many" side fields.
• Solution: Add the join field from the "many" side table (ie, foreign key) to your query to allow adding new records.

4. New records on the "one" side that are duplicates of other "one" side records.

• Problem: When adding a new record, if you try to type into the "one" side fields, you will be attempting to create a new record. Even if you use the same primary key values, it will give you an error.
• Solution: Add a value to the "many" side join field (foreign key) that matches the "one" side join field (primary key) of an already existing record. The "one" side values will simply appear.

5. Join field from the "many" side, after you've updated data on the "one" side

• Problem: If you are currently editing fields from the "one" side of the relationship, you cannot change the "many" side join field (foreign key).
• Solution: Save the record; then you'll be able to make changes to the "many" side join field.

6. New records, if entire unique key of ODBC table isn't output

• Problem: This is different than #5 under Never Updateable. In this case, the primary key of the linked ODBC table exists, but is not added to the query.
• Solution: Select all primary key fields of ODBC tables to allow inserts into them.

7. Query does not have Update Data permissions

• Problem: Query (or underlying table) for which Update Data permission isn't granted.
• Solution: To modify data, permissions must be assigned.

8. Query does not have Delete Data Permissions

• Problem: Query (or underlying table) for which Delete Data permission isn't granted
• Solution: To delete data, permissions must be assigned.

Conclusion

The causes of non-updateable recordsets are many and varied. Some have solutions and others don't. Hopefully, this list will help you know the difference.

Addendum (January 28, 2010):
This is in response to a comment below.  I thought it was important enough to add to the main article.

Linked Excel Sheets Not Updateable
The question is why are my linked sheets from an Excel Workbook non-updateable?  The answer is not technical but legal.  Several years ago, Microsoft lost a patent infringement lawsuit that involved the ability to update records in Excel from Access.  The upshot is that in Access 2003 and later versions, by design, you can no longer update data in Excel spreadsheets.

.

New Sample: Report_SpellCheck

Report_SpellCheck

by A.D. Tejpal

This sample db demonstrates spell check on a report prior to its opening. Two alternatives are covered:

(a) Record source is updateable.
(b) Record source is non-updateable.

Version: Access 2000 file format.
References: DAO 3.6

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=523

.

New Sample: AppendValuesToExcel.mdb

AppendValuesToExcel.mdb

by Roger Carlson

This sample demonstrates how to append data from a database to an Excel spreadsheet using Office Automation to find the next empty row in the spreadsheet.

You can fiind the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=522

.

New Sample: MovePriority.mdb

MovePriority.mdb

by Dave Mason

This sample allows the user to arbitrarily move records up or down in a form's Continuous Forms view.

You can find this sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=521

.