How do I create a Numbered Query in Access?

SQL is a very powerful query language that is built into Microsoft Access. But one thing SQL does not do very well is refer to a previous row to calculate values. As a result, some things that are simple to do in a spreadsheet are difficult in a query. On of those is creating a numbered sequence for your query. However, difficult does not mean impossible. This article will show you how to create a numbered query.

Numbered Query

A numbered query is a query where each record is numbered sequentially. There may be a variety of reasons to do this. You might have a test question database where you want each question to be automatically numbered. Or you might want to export a customer list with sequential numbering to an external source like Excel. While this is simple to do in an Access report, it requires some advanced techniques to do in a query.
In an Access report, all you need to do is add an unbound text box in the Detail section, put =1 in the control source, and set the Running Sum property to Over All. See Figure 1.

Figure 1: Shows the property dialog box for a text box on a report that will create a numbered sequence.
But suppose you don't want to do it in a report. Suppose you want to do it directly in a query. There are two different ways to accomplish this. The first uses the Domain Aggregate Function DCount and the second uses a Correlated Subquery.
Both of these methods require a unique column in the table to create the sequence on. This could be the Primary Key field or any field that has a Unique Index. In the following example, the Customers table has two such columns, CustID (Customer ID), which is the primary key, and CustName (Customer Name), which has a unique index.
I’ve already discussed the Domain Function method in a previous article here: Domain Function Example: Numbered Query With DCount, so in this article, I’ll address the Sub Query method.
Correlated Sub Query Method

Subqueries can be used in multiple places in an SQL statement. Most of the time they’re used in the FROM clause or the WHERE clause.  But they can also be used in the Field List, which is where I’ll use it here. 
When used in either the FROM clause or Field List, the query must be correlated to the main query. What does that mean? Well, as the name implies, correlated means co-related, that is the records in the subquery must be related to a single record in the main query. In this way, the subquery can be executed for each record in the main query.
Let me take this in small steps.  Suppose I have a Customer table that looks like this:

The Main Query

The table is sorted on CustID, but that field is not sequential since there are gaps in the numbering.  I can see from the Record Navigator at the bottom of the query that CustID of 7 is actually the 5th record. 
So first, I’ll create a placeholder in my query:

SELECT Null AS Sequence,
    Customers.CustID,
    Customers.CustName,
    Customers.CustPhone
FROM Customers
ORDER BY Customers.CustID;
It is very important that the main query should be sorted on the field the sequence will be created on.  In this case CustID.

The Sub Query

Now I just need a way to replace the Null with the sequential value.  I can find the value of any individual record (say CustID=7) with a query like this:

or in SQL
SELECT Count(1) AS Rec
FROM Customers
WHERE Customers.CustID<=7
The result of which will be:

Adding that to my main query, I could try something like this:

SELECT     (Select Count(1) FROM Customers
        WHERE Customers.CustID <=7) AS Sequence,
    Customers.CustName,
    Customers.CustPhone,
    Customers.CustID
FROM Customers
ORDER BY Customers.CustID;

but this will create a couple of problems. 

The first is a syntax issue.  I can’t directly reference the same table in two separate FROM clauses in a single SQL statement.  I need to Alias the table name in the subquery

Select Count(1) FROM Customers as A WHERE A.CustID <=7

so I’d get:

SELECT     (Select Count(1) FROM Customers as A
        WHERE A.CustID <=7) AS Sequence,
    Customers.CustName,
    Customers.CustPhone,
    Customers.CustID
FROM Customers
ORDER BY Customers.CustID;
The second problem is that this will return a 5 for every record.

Pulling Them Together

The last piece is to reference the CustID in the main query rather than the hard-coded 7.

SELECT     (Select Count(1) FROM Customers as A
        WHERE A.CustID <=Customers.CustID) AS Sequence,
    Customers.CustName,
    Customers.CustPhone,
    Customers.CustID
FROM Customers
ORDER BY Customers.CustID;
Which gives me this:

Here's how it works.

For each record in the main query, Access runs the subquery. The subquery returns the number of records where the CustID in the subquery is less than or equal to the CustID in that record of the main query.

So in the first record, the CustID is 1. So the subquery opens the Customers table again and sees that there is only 1 record whose CustID is less than or equal to 1. So it returns 1.

Then it processes the second record. The CustID of that record is 3, and the subquery sees that there are only 2 records which have an CustID whose value is less than or equal to 3. So it returns 2.

And so on.

Other Fields

You don't need to use a number field as your Order By field. You can sort on text fields and number the query as well.

If you wanted to sort on the Customer Name field (CustName), you would change the subquery to the following:

SELECT     (Select Count(1) FROM Customers as A
        WHERE A.CustName <=Customers.CustName) AS Sequence,
    Customers.CustName,
    Customers.CustPhone,
    Customers.CustID
FROM Customers
ORDER BY Customers.CustName;

 The output would look like this:

Domain Aggregate Function Method
As I said, this can also be done with a DCount Function, which I discussed in a previous article: Domain Function Example: Numbered Query With DCount
However, you can find both methods on my website in this sample: NumberedQuery.
.

How do I create a Running Sum Query?

SQL is a very powerful query language that is built into Microsoft Access. But one thing SQL does not do very well is refer to a previous row to calculate values. As a result, some things that are simple to do in a spreadsheet are difficult in a query. One of those is creating a running sum.
A running sum adds the value of a field in a record to the value of the same field in the previous record.
Running Sums are fairly easy in an Access Report, but more difficult in a query.  Difficult, but not impossible. This article will show how to create a running sum query.

Running Sum in a Report

Create a running sum in a report with an unbound textbox in the Detail section. This time, however, in the control source, put the name of the field you want the running sum on. And just like the number query, set the Running Sum property to Over All.
A running sum adds the value of a field in a record to the value of the same field in the previous record.
Figure 1: Shows the property dialog box for a text box on a report that will create a running sum.

Running Sum in Query

But suppose I don't want to do it in a report. Suppose I want to do it directly in a query. There are two different ways to accomplish this. The first uses the Domain Aggregate Function DCount and the second uses a Correlated Subquery.
Both of these methods require a unique column in the table to create the sequence on. This could be the Primary Key field or any field that has a Unique Index. In the following example, the Customers table has two such columns, CustID (Customer ID), which is the primary key, and CustName (Customer Name), which has a unique index.
I’ve already discussed the Domain Function method in a previous article here: Domain Function Example: Running Sum with DSum, so in this article, I’ll address the Sub Query method.

Two Types: OverAll and OverGroup

There are two common types of running sums. The first is a running sum over the entire resultset. see Figure 2 for RunningSumOverAll 

Figure 2 illustrates the running sum of the Price field in an Order Details table.
The second is a running sum over a group. See Figure 3 RunningSumOverGroup

Figure 3 shows a running sum over a group.
In this case, the group is the OrderID field, and you'll see that the query starts the summing over as the group number value changes.

Correlated Sub Query Method

When used in either the FROM clause or Field List, a subquery must be correlated to the main query. What does that mean? Well, as the name implies, correlated means co-related, that is the records in the subquery must be related to a single record in the main query. In this way, the subquery can be executed for each record in the main query.
Let me take this in small steps.  Suppose I have an Order Details table that looks like this:

The Main Query

The table is sorted on OrderDetailID, so the first thing is to create a placeholder in the query:

SELECT tblOrderDetails.OrderDetailID,
     tblOrderDetails.OrderID,
     tblOrderDetails.ProductID,
     tblOrderDetails.Price,
     Null AS RunningSumFROM tblOrderDetails;
It is very important that the main query should be sorted on the field the sequence will be created on.  In this case OrderDetailID.

The Sub Query

Now I just need to replace the NULL with the running sum value.  But how? In Excel, I’d reference the cell to the left (Price) and add it to the cell above (RunningSum).  So cell B5 would have a formula of =A5+B4.

Figure 5: Method that WILL NOT work in Access.
But tempting as this is, Access can’t reference individual cells.  I need a different method.
Since addition is cumulative, I can sum A1 through A4, in other words, B4 = SUM(A1:A4)

Figure 6:This method CAN be translated to Access
This method I can use. 

Running Sum Over All

I can find the value of any individual record (say OrderDetailID=4) with a query like this:

Figure 7
or in SQL
SELECT Sum([Price])
FROM tblOrderDetails
WHERE tblOrderDetails.OrderDetailID <= 4
The result of which will be:

Figure 8:
Adding that to my main query, I could try something like this:
SELECT tblOrderDetails.OrderDetailID,
     tblOrderDetails.OrderID,
     tblOrderDetails.ProductID,
     tblOrderDetails.Price,
     (SELECT Sum(Price) FROM tblOrderDetails
        WHERE OrderDetailID <= 4) AS RunningSumFROM tblOrderDetails;

but this will create a couple of problems. 

The first is a syntax issue.  I can’t directly reference the same table in two separate FROM clauses in a single SQL statement.  I need to Alias the table name in the subquery

Select Sum(Price) FROM tblOrderDetails as OD WHERE OD.OrderDetailID <=4

so I’d get:

SELECT tblOrderDetails.OrderDetailID,
     tblOrderDetails.OrderID,
     tblOrderDetails.ProductID,
     tblOrderDetails.Price,
     (SELECT Sum(Price) FROM tblOrderDetails as OD
        WHERE OD.OrderDetailID <= 4) AS RunningSum
FROM tblOrderDetails; The second problem is that this will return a $12.00 for every record.

Figure 9:

So I need to link the subquery with a particular record in the main query.

Pulling Them Together

The last piece is to reference the OrderDetailID in the main query rather than the hard-coded 4.

SELECT tblOrderDetails.OrderDetailID,
     tblOrderDetails.OrderID,
     tblOrderDetails.ProductID,
     tblOrderDetails.Price,
    (SELECT Sum(Price) FROM tblOrderDetails as OD
        WHERE OD.OrderDetailID <= tblOrderDetails.OrderDetailID)
           AS RunningSum
FROM tblOrderDetails; Which gives me this:

Figure 10: Results of the completed Running Sum Over All query.

Here's how it works.

For each record in the main query, Access runs the subquery. The subquery returns the number of records where the OrderDetailID in the subquery is less than or equal to the OrderDetailID in that record of the main query.

So in the first record, the OrderDetailID is 1. So the subquery opens the OrderDetails table again and sees that there is only 1 record whose OrderDetailID is less than or equal to 1. So it sums the value of that one field, which simply returns that value ($5.00).

Then it processes the second record. The OrderDetailID of that record is 2, and the subquery sees that there are only 2 records which have an OrderDetailID whose value is less than or equal to 2. So it sums those records ie. $5.00 + (-$2.00) = $3.00.

And so on.

Running Sum Over Group
Instead of creating a running sum over the entire resultset as we did above, you might want to create a running sum for a grouping of records. In the case of the Order Details table above, it might make more sense to give a running sum for each OrderID group.
Creating a running sum over a group of records rather than the whole resultset is a little more complicated, but not much. Again, I can use subquery. But I need to add an additional criterion to the Where condition to include the value that we want to group on.
And again, the only addition to the subquery method is to sum only those values that have the same OrderID. Thus the subquery looks like this:
SELECT Sum(Price) FROM tblOrderDetails as OD
        WHERE OD.OrderDetailID <= tblOrderDetails.OrderDetailID AND
             OD.OrderID=tblOrderDetails.OrderID
And the whole query, like this:
SELECT tblOrderDetails.OrderDetailID,
     tblOrderDetails.OrderID,
     tblOrderDetails.ProductID,
     tblOrderDetails.Price,
    (SELECT Sum(Price) FROM tblOrderDetails as OD
        WHERE OD.OrderDetailID <= tblOrderDetails.OrderDetailID
           AND OD.OrderID=tblOrderDetails.OrderID) AS RunningSum
FROM tblOrderDetails; The result looks like Figure 11.

Figure 11: Result of the Running Sum query over a group. Notice how the running sum resets when the OrderID changes from 1234 to 1235.
You can find a sample called

RunningSumInQuery

which illustrates both Over All and Over Group as well as the corresponding DSum Method (domain aggregate function).

Running Action Queries in VBA

One of the powerful features of Microsoft Access is its ability to run queries in Visual Basic for Applications (VBA) code. However, there are a couple of problems that plague developers when they attempt to do this. One is the problem of confirmation messages when running an Action query.

There are a variety of circumstances under which you might want to run a query in VBA code. You may want to just display the results of a Select query to the screen at the push of a button. You may want to run an Action Query in a code module as part of another process. You may even want to open a virtual recordset to do some data manipulation that can't be done directly in SQL. Access provides you several ways to accomplish this, depending on what you are trying to do.

There are two broad categories of queries in Access: Select queries and Action queries. Select queries simply return and display records. Action queries, on the other hand, actually modify the data in your tables. Append queries, Update queries, and Make Table queries are all action queries.

Confirmation Messages

The simplest way to run either a Select query or Action query is with the OpenQuery method of the DoCmd statement. Like this:

DoCmd.OpenQuery "Query1"

This statement will run the either type of query exactly as if you had run it from the Query Window. If the query is a Select query, it will simply return the query results to the screen, as in Figure 1.

Figure 1: Results of a Select query displayed to the screen using Docmd.OpenQuery.

However, since Action queries modify data in your tables, they don't return anything to the screen. So Access displays a couple of confirmation dialog boxes to warn you that you are about to change your data. For instance, running an Update query will make the confirmation dialog box in Figure 2 appear.

Figure 2: Dialog box asking for confirmation of the Update query.

Followed by a second dialog box confirming the action. Like Figure 3.

Figure 3: Second confirmation dialog box for Update Query

While these messages are generally a good thing when using the Access Graphical User Interface (GUI), if you running an action query as part of an automated process, these confirmation boxes can be annoying. There are several ways to turn off these messages.

Set Options

First of all, you could turn off all confirmation boxes for Action queries by going to Tools menu, click Options, then click the Edit/Find tab. Under Confirm, clear the Action queries check box. On the whole, however, this is generally not a good idea. This option will affect all action queries in all databases. It would be better to target just those queries that you are certain you want to run without confirmation.

Set Warnings

Another way to keep these messages from appearing is to turn off the warning messages programmatically. The SetWarnings method of the DoCmd statement will turn off the warning messages until you turn them on again. To do this, you surround the query you want to run with one command to turn them off and another to turn them back on.

DoCmd.SetWarnings False

DoCmd.OpenQuery "ActionQuery1"

DoCmd.SetWarnings True

It is important to use these statements in pairs because all dialog boxes will be turned off (even the ones you want) until the database is shut down and restarted.

The problem with this method is that in addition to turning off the confirmation boxes, it will also turn off all error messages. So if there is an error when running the query, it will fail silently, leaving you no indication it had failed. This is rarely optimal.

Execute Method

The best solution is to turn off the confirmation messages while allowing the real error messages to display.

The best way to run queries in code is through the Data Access Object model or DAO. DAO gives you programmatic access to the entire database. Through DAO, you can change the structure of the database, modify the data in the database, and create or modify the database objects like forms, reports and queries. You can also use DAO to execute action queries.

To use DAO, you must create and initialize a database object variable. Like this:

Dim db As DAO.Database

Set db = CurrentDb()

Once you have created a database variable, you can use the Execute method to run the action query.

db.Execute "Query2", dbFailOnError

The Execute method assumes you know what you're doing, so it does not display confirmation messages. The optional parameter, dbFailOnError, will display any error messages.

Technically, you wouldn't have to create and initialize a database variable to run this query. Access provides a shortcut.

CurrentDb.Execute "Query2", dbFailOnError

The CurrentDb object will give you direct access to the database. It creates a temporary instance of the database that persists only until that line is executed. It can only be used for that one command. However, there are times when you want the database object to persist because you want to do multiple things with it, which leads me to opening parameter queries in code.

Sample Database

You can find a companion sample which illustrates how to suppress confirmation messages when running an Action query in VBA code, and is a perfect companion to the Action Queries Series.

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

Ambiguous Outer Joins

The Outer Join can be a powerful tool for querying data in Microsoft Access. When you have only two tables, there is usually no problem. When there are more than two tables, however, using an Outer Join becomes more complicated. Sometimes Access allows it, and sometimes it gives you the not-very-descriptive "Ambiguous Outer Join" error.

Why? Well, first we'll look at what an Ambiguous Outer Join is, and then see how to correct it.

Microsoft Access has three types of joins: the Inner Join, the Right Join and the Left Join. Both the Right and Left joins are known as Outer Joins. An Inner Join shows only those records that exist in both tables. However, an Outer Join (both Right and Left) shows all of the records from one table (the Base Table) and just the matching records from the other (Secondary Table).

When Access processes a multiple table query, it needs to determine the order in which joins should be made. Should it join Table1 to Table2 first and then join Table3? Or should it do it in some other order? This is part of the Rushmore technology of the Jet engine. It tries to determine the most efficient way to process the query.

In the case of standard Inner Joins, the result set will be the same, regardless of the order in which they are joined. However, this is not the case with Outer Joins. There are times, when using an Outer Join, that the result of the query will be different depending on the order in which joins are created. In this case, Access cannot determine the order to join the tables. This is an Ambiguous Outer Join.

So how do you know when an Outer Join will result in an error? The easiest way to understand it is in terms of what you see in the Query Builder grid.

A table which participates in an Outer join as a Secondary Table (that is, the arrow is pointing *towards* it) cannot participate in either an Inner Join, or as a Secondary Table in another Outer Join. Figure 1 shows two types of queries that will result in an Ambiguous Outer Join error.

Figure 1: Two illegal Outer Join Queries

However, the table participating in the Outer Join as a Secondary Table can participate in another Outer Join if it is the Base table of the other Outer Join (that is, the arrow points *away* from it). Figure 2 shows a query that will not result in an Ambiguous Outer Join error.

Figure 2: A legal Outer Join Query

So what do you do if you need to create a query like case 1 or 2? You have to split the query into a stacked query, that is, two queries, the second of which uses the first. This is exactly what the Ambiguous Outer Join error message suggests.

Create a query joining the first two tables with an Outer Join and save it as a named query (i.e. Query1). Then, in a second query, join the first query to the third table.
Figure 3 shows how to build a stacked query.

 

Figure 3: Shows how to split the query into two queries to avoid an Ambiguous Outer Join.

So the Ambiguous Outer Join error is not really all that confusing. It simply means that the database wants you to decide which join it should create first. In Access, you do this by spitting the query into a stacked query.

Featured Sample: ActionQueriesInCode.mdb

Action Queries in Code.mdb

by Roger Carlson

There are a variety of circumstances under which you might want to run a query in VBA code. You may want to just display the results of a Select query to the screen at the push of a button. You may want to run an Action Query in a code module as part of another process.

This sample illustrates how to suppress confirmation messages when running an Action query in VBA code, and is a perfect companion to the Action Queries Series.

Full documentation included.

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

.

Action Queries: Make-Table Query

Action Queries

Action Queries, also known as Data Manipulation Language (DML) statements, do not return a dataset like Select Queries, but makes changes to the data in the base tables. There are four types of action queries: Delete Queries, Update Queries, Append Queries and Make-Table Queries. In previous posts, I discussed the Delete Query, the Update Query, and the Append Query. In this last post of the series, I'll look at the Make-Table Query.

Make-Table Queries

A Make-Table Query is a shortcut method to create a table based on values in another table. In SQL, it's called a SELECT...INTO statement. The basic syntax looks like this:
SELECT ISBN, Title, PubID, Price INTO BooksTemp
FROM Books;
 In the Query Builder:

In properly normalized databases, there aren't a lot of uses for the Make-Table Query. It's most important use is for creating temporary tables. Sometimes, complex queries can be simplified or their performance improved by creating a smaller, temporary table. The Make-Table is ideal for this application.
However, make-table queries do have some disadvantages. First of all, they tend to bloat the database. Every time you make a temporary table, your database grows. When you delete it or over-write it, the database doesn't automatically shrink. This will require that you periodically compact your database.
Secondly, you don't have a lot of control over the structure of a table made with a make-table query. These tables automatically inherit the field datatypes and sizes from the parent table, but it does not inherit any other field properties (like validation rule or defaults), nor does it inherit any indexes.

Running a Make-Table Query

There are two buttons for running Action Queries: The View button displays the results of the query, that is, which records will be updated. The Run button actually appends the records to the target recordsource.
.

Action Queries: Append Query

Action Queries

Action Queries, also known as Data Manipulation Language (DML) statements, do not return a dataset like Select Queries, but makes changes to the data in the base tables. There are four types of action queries: Delete Queries, Update Queries, Append Queries and Make-Table Queries. In previous posts, I discussed the Delete Query, and the Update Query. This time, I'll look at the Append Query.

Append Queries

An Append Query, also called an INSERT INTO in SQL, adds records to a record source (table or query). It can either append individual record values or a dataset queried from another record source. To append values, use the VALUES keyword:

INSERT INTO Books(ISBN, Title, PubID, Price)
VALUES ("0-103-45678-9", "Iliad", 1, 23);

Unfortunately, this type of Append query cannot be made in the Query Builder. In fact, if you type it into the SQL View of a query and switch to the Design View, the query will be converted to this:

INSERT INTO Books (ISBN, Title, PubID, Price)
SELECT "0-103-45678-9" AS Expr1, "Iliad" AS Expr2, 1 AS Expr3, 23 AS Expr4;

This alternate syntax will still work in Access, but it's not standard SQL syntax. This type of Append query is most useful when used in embedded SQL, that is, SQL statements that are executed in a VBA module. One common use is with unbound forms. For example, suppose I have a form with unbound controls, like so:

Behind the Append Values button, I can have the following code:

Private Sub cmdAppendValues_Click()
Dim strSQL As String
strSQL = "INSERT INTO Books(ISBN, Title, PubID, Price)" & _
" VALUES ('" & Me.ISBN & "', '" & Me.Title & "', " & _
Me.PubID & "," & Me.Price & ");"
CurrentDb.Execute strSQL, dbFailOnError
End Sub

Clicking the button will execute an SQL statement that appends the values from the unbound controls (i.e. Me.ISBN, etc.) in the form into a new record of the table.

For a working example of an unbound form using SQL statements, see my sample: UnboundSQL.mdb.

The second type of append query queries a dataset from one record source and appends it to another. This is most useful when importing data from an external source (say an Excel spreadsheet or a CSV file) to be added to an existing table. Suppose I have a table of new books (called AppendList) to be added to the Books table. I can use the following SQL statement:

INSERT INTO Books (ISBN, Title, PubID, Price)
SELECT ISBN, Title, PubID, Price
FROM AppendList;

Or as seen in the QBE grid:

Running an Append Query

There are two buttons for running Action Queries: The View button displays the results of the query, that is, which records will be updated. The Run button actually appends the records to the target recordsource.

Next up, the Make-Table Query.

Action Queries: Update Query

Action Queries

Action Queries, also known as Data Manipulation Language (DML) statements, do not return a dataset like Select Queries, but make changes to the data in the base tables. There are four types of action queries: Delete Queries, Update Queries, Append Queries and Make-Table Queries. Last time, I discussed the Delete Query. This time, I'll look at the Update Query.

Update Queries

An Update Query will make bulk changes to records in the record source. . The record source must be updateable. (See
This Recordset Is Not Updateable. Why? For details about updateability.) At its simplest, an Update Query will make changes to every record. For instance, suppose I want to raise the Price of every book in the Books table by 10%. I could create a SQL statement like this:

UPDATE BOOKS SET Price = Price*1.1;

In the Query Builder, it looks like this:

But it's not very often you want to update every record. Usually, you want to update just certain records. To do that, you add a Where condition just as you would in a Select Query (see: Select Queries Part 2: Restricting Rows - the Where Clause). Suppose I wanted to change every book whose price was $25 to $24.95. My SQL statement would be as follows:

UPDATE Books SET Price = 24.95
WHERE Price=25;

Which looks like this in the Query Builder:

Of course, you could also raise the price by 10% for a particular publisher:

UPDATE BOOKS SET Price = Price*1.1
WHERE PubID=2;

Updating from a List

But what if, as I did with the Delete query, you are given a list of ISBN numbers and associated new prices? How could you update your Books table from this list? You SHOULD be able to use a subquery. Something like this:

UPDATE Books SET Books.Price =
(SELECT price FROM UpdateList
WHERE Books.ISBN = UpdateList.ISBN)
WHERE ISBN
In (SELECT ISBN FROM UpdateList
WHERE Books.ISBN = UpdateList.ISBN);

Unfortunately, while you could do this in almost any other SQL implementation (SQL Server or Oracle, for instance) you cannot do this in Access SQL. It will give you the "Operation Must Use an Updateable Query" error.

Fortunately, there are a couple of workarounds. One is to use a Join and the other is to use a Domain Aggregate function.

Workaround 1 - the Join

This work around requires you to Join the Books table with the UpdateList table, like this:

UPDATE Books INNER JOIN UpdateList
ON Books.ISBN = UpdateList.ISBN
SET Books.Price = [Updatelist].[price];

In the QBE:

One caveat here is that the Join MUST be an updateable dataset, so there must be a unique index on the Join field (in this case ISBN).

Workaround 2 - DLookup

This work around uses a subquery in the Where clause, but uses a DLookup function in the Set clause:

UPDATE Books SET Books.Price =
DLookUp("Price","UpdateList","ISBN ='" & [ISBN] & "'")
WHERE Books.ISBN
In (SELECT ISBN FROM UpdateList
WHERE Books.ISBN = updateList.ISBN);

This solution does not require a unique index, but it is much slower than the Join solution.

Running an Update Query

There are two buttons for running Action Queries The View button displays the results of the query, that is, which records will be updated. The Run button actually updates the records.

Next up, the Append Query.

Action Queries: Delete Query

Action Queries

Action Queries, also known as Data Manipulation Language (DML) statements, do not return a dataset like Select Queries, but makes changes to the data in the base tables. There are four types of action queries: Delete Queries, Update Queries, Append Queries and Make-Table Queries. I'll start with Delete Queries and discuss the rest in subsequent posts.

Delete Queries

As the name suggests, Delete Queries delete records from a record source, that is, a table, a join, or another query. The record source must be updateable. (See
This Recordset Is Not Updateable. Why? For details about updateability.)

At its simplest, a delete query will delete all records from the table:

DELETE * FROM Books;

But in most cases, you probably want to delete some subset of all the records. For that, you restrict your records with a Where clause, just as with a Select Query(see: Select Queries Part 2: Restricting Rows - the Where Clause). Suppose I wanted to delete all the records from a particular publisher:

DELETE * FROM Books WHERE PubID=1;

In the Query Builder, I generally suggest creating a Select Query for the records you want to delete:

And then convert it to a Delete Query using the Ribbon (or Toolbar in Access 200X):

Now, suppose you are given a list of ISBN numbers that you need to delete from your table. If that list is in a table (DeleteList), you can delete the records from your Books table with a subquery using the IN operator, like this:

DELETE ISBN FROM Books
WHERE ISBN In (Select ISBN from DeleteList);

In the query builder, it looks like this:

The Select subquery will create a list for the IN operator to supply ISBN numbers to the Delete query.

Running an Action Query

Running an Action Query is a little different than running a Select Query. With a select query, the View and Run buttons do exactly the same thing: display the results of the query. With Action Queries, however, the two buttons do different things. The View button displays the results of the query, that is, which records will be deleted, updated, inserted, or appended. The Run button actually executes the action.

Next time, I'll discuss the Update Query.

.

What Is A Join Part 5: Outer Joins

Outer Join (Left Join or Right Join)

So far in this series (What Is A Join: Part1, Part2, Part3, and Part4), I've concentrated on the Inner Join, which is the most common type of join. However, the Inner Join has a limitation. It will only match field values that exist in both tables. Going back to our Order and OrderDetails tables:

Orders
OrderID	OrderNumber
1	111
2	222
3	333
4	444

You can see that there is an OrderID "4" in the Order table.

OrderDetails
OrderDetailID	OrderID	Quantity
1	1	1
2	2	2
5	3	3
6	1	2
7	1	1
8	3	2
9	3	1

However, there are no records for OrderID "4" in the OrderDetails table.

Query1
OrderNumber	OrderID	Quantity
111	1	1
111	1	2
111	1	1
222	2	2
333	3	3
333	3	2
333	3	1

Thus, OrderID "4" does not appear in the resulting Inner Join. But there times when you want to show all of the records of one table and the associated records in another, whether or not all of the records in the first table have a match.

This is called an Outer Join. In Access, there are two types of Outer Joins, Left Join and Right Join. The Left and Right refer to which side of the equal sign the table is on in the JOIN clause. I'll get back to that in a minute.

To create an Outer Join in the Query Builder, start with a standard Inner Join. Then right click on the Join line, and select Join Properties You'll get a pop-up box with three choices:

Selecting either Option 2 or 3, will create an Outer Join. If you select 2, you'll be creating a Left Join. If you select 3, you'll be creating a Right Join. The result will look like this:

And the resultset will look like this:

LeftJoin
OrderNumber	OrderID	Quantity
111	1	1
111	1	2
111	1	1
222	2	2
333	3	3
333	3	2
333	3	1
444

The SQL for this query looks as follows:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity
FROM Orders LEFT JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID;

The "Left" and "Right" handedness doesn't really have anything to do with the table positions in the Query Builder. It has more to do with the positions of the tables with respect to the JOIN keyword.

An Inner Join will look like this:

FROM Order JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID

Since it is joining only matching records, it doesn't matter what order the tables appear in the clause. But to create an Outer Join, you have to indicate which table will show all records. In Access, you do that with LEFT JOIN and RIGHT JOIN.

FROM Order LEFT JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID

In a Left Join, the table on the left of the JOIN keyword (Order) will show all records.

FROM Order RIGHT JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID

In a Right Join, the table on the right of the JOIN keyword (OrderDetails) will show all records.

Multiple Joins with Outer Joins

An outer join (left or right) can participate in a multiple table join only under circumstances. If the query also involves an equi-join and certain outer joins, it will result in the Ambiguous Outer Join error. For more information about this error, see my post: What is an Ambiguous Outer Join?

One last thing about Outer Joins: Earlier, I discussed creating an Equi-Join in the Where clause, but as far as I know, in Access you can't create an Outer Join that way.

Next time, I'll look at a specific application of the Outer Join: the Unmatched Query.

What is a Join: Part 4 (Equi-Joins in the WHERE Clause)

Equi-Joins in the WHERE Clause

In my last post in this series on the JOIN (What Is A Join: Part 3 (Cartesian Joins)), I discussed the Cartesian Join, which has very few uses and in most cases should be avoided at all costs. However, there is one practical use for a Cartesian join as long as you restrict its output with a WHERE clause.

So far, I've created the Joins in the FROM clause of the SQL statement. This is standard SQL, but it's also possible to create an Equi-Join in the WHERE clause. As a general rule, it's better to create your join in the JOIN clause, but there are circumstances under which is it useful to do it in the WHERE, which I'll show in just a bit.

To create a Join in the WHERE clause, you create a Cartesian join and add a WHERE clause equating the common fields of both tables. In the Query Builder, add both tables to the table window, but you DON'T create a Join line between them. If the Query Builder adds one automatically, delete it. In the WHERE clause you make the join field of one table equal to the join field of the other.

For instance, if I wanted to create this query:

in the WHERE clause instead, I would do it like so:

Notice there is no Join line between the tables and the OrderID of the OrderDetails table is equal to the OrderID field of the Orders table. The SQL statement looks like this:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity
FROM Orders, OrderDetails
WHERE OrderDetails.OrderID)=[Orders].[OrderID];

Earlier, I showed that without the Where clause, I would get 28 records. However, with the Where clause, I get the same result as I did when I created the join in the FROM clause.

Where Clause Join
OrderNumber	OrderID	Quantity
111	1	1
111	1	2
111	1	1
222	2	2
333	3	3
333	3	2
333	3	1

In general, this is not as efficient as creating the Join in the FROM clause.

Joining fields of different data types

Suppose the join fields in my two tables are different datatypes. Suppose OrderID in the Order table it is numeric and in OrderDetails it is text. This can happen when dealing with external data sources over which you have no control. You can't create a join on fields of different datatypes, so you have to use a conversion function to convert one of the fields. Since OrderID in the Orders table is a Long Integer, I can use the CLng function to convert OrderID in the OrderDetails table.

I can change my original Join clause:

SELECT Orders.OrderNumber, OrderDetails.OrderID,
OrderDetails.Quantity
FROM Orders INNER JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID;

As follows:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity
FROM Orders INNER JOIN CLng(OrderDetails)
ON Orders.OrderID = OrderDetails.OrderID;

This will work. However this cannot be represented in the Design View of the query. If you ever open the query in Design View, it will give you an error and remove the Join line altogether. You must close the query without saving or your query will be ruined.

But if you create the Join in the WHERE clause:

SELECT Orders.OrderNumber, OrderDetails.OrderID,
OrderDetails.Quantity
FROM Orders, OrderDetails
WHERE OrderDetails.OrderID = CLng([Orders].[OrderID]);

This can be represented in the Design View.

Join Date/Time fields that have times

I have also seen cases where a DateTime field in one table held just the date value, but held date and time in the other table. I needed to use the DateValue function to remove the time from the DateTime in the one table. Again, it was external data over which I had no control.

Creating the Join in the WHERE clause solved the problem nicely. Something like this:

SELECT Patient.MRN, Patient.DischDate,
Billing.Charge
FROM Patient, Billing
WHERE DateValue(Patient.DischDate) = Billing.DischDate
AND Patient.MRN = Billing.MRN;

In my next post, I'll look at Outer Joins.

 

[top]