What is Rushmore Technology and how do I use it in Access?
	What is a Union Query and why would I use it?
	Why do I get Reserved error (-1310) when running a query?
	What causes the "Query contains ambiguous outer joins." error?
	When can I update fields in a multi-table query?
	Why do I get "Out of Memory" or "Query too Complex"?
	Why do my column headings disappear in CrossTab query?
	How do I replace Nulls with Zeros in a CrossTab query?
	What are the best ways to optimize queries?
	How do I use a function as a parameter in a query?
	How do I use a subquery as criteria in a where clause?
	How do I pass a query parameters via code?
	How can I Import/Append data from an external database to an internal table?

What is Rushmore Technology and how do I use it in Access?
Keywords: Rushmore Query
Posted Nov 15, 1996

Rushmore is a query optimization technology that was borrowed from FoxPro. It increases the performance of queries which involve two criteria formed by what are called simple optimizable expressions. It involves the efficient use of indexes to quickly find a set of records. These expressions are a query criteria statement such as:

"indexed field" operator expression

Therefore, if a query contained something like:

[CustomerID]=5889

And, the [CustomerID] field was indexed, then it would be an optimizable expression.

Rushmore also allows you to combine two criteria with optimizable expressions using the logical AND or OR operator. An example follows:

[CustomerID]=5889 AND [OrderDate]<#10/1/96#

By using the AND operator, the resulting records from each of the indexes are intersected to find the records that match both criteria.

If we use the OR operator, then the resulting records from each of the indexes are then unioned together to find the records with either one criterion or the other.

What is a Union Query and why would I use it?
Keywords: Union Query
Posted Nov 15, 1996

A UNION is a specialized SQL operation that combines the results from two or more independent queries or tables into a single result.

Typically, you use a UNION when you need to combine information from tables that are not directly related or when a table is related to more than one other table required for the query. For example, suppose we want to know the balance owed for all of our customers in an order entry system. In order to get this information we need to know the total dollar amount ordered and the total number of payments made. I know that this sounds rather straightforward, but it actually isn't.

The problem lies in the fact that the order header table is the parent table to both line item details and payments. SQL prevents you from relating a parent table to multiple children within the same query. What happens is that the query result will return one row for every occurrence of the parent value in the child with the most occurrences.

This means, for instance, that if an order has three line items and a single payment for the entire order amount, the query result will show the payment amount three times. Based upon this result, you owe the customer money! The query itself looks like this:

SELECT oh_orderno,oh_custid,oh_date,SUM(ol_price*ol_qty) As InvoiceTotal, 0.00 As Payments From OrderHeaders,LineItems WHERE orderheaders.oh_orderno = lineitems.ol_orderno GROUP BY oh_custid,oh_orderno,oh_date UNION SELECT py_orderno,oh_custid,oh_date,0.00, SUM(py_amount) FROM orderheaders,payments WHERE orderheaders.oh_orderno = payments.py_orderno GROUP BY oh_custid,py_orderno,oh_date ORDER BY oh_date;

The purpose of a UNION is to take several dissimilar tables, or query results, and create a single unified result. To perform this step, SQL needs to know how you want the result table to look. You use the first SELECT fields list for this determination. Therefore, the fields list in the first SELECT must allow for all the fields that will be needed, including those fields that are not in the first statement but will appear later.

You accomplish this task through the use of placeholders. Note in our preceding example the alias fields type and amount. These names do not actually reference real fields. The rule is that each SELECT statement must contain the same number of fields, although the fields in subsequent queries need not be the same size or even data type.

In our example, however, I represented empty field positions with the same size and data type as I expect to store there. You can, of course, use anything at all to indicate that the field is empty for the current record.

The next point of interest with UNIONs is the concept of DISTINCT. When we issue a standard SELECT statement, the default is to include all the records from the involved tables. In fact, if we want to eliminate duplicate records we need to include the DISTINCT or DISTINCTROW clause.

Notice, however, that in the UNION we have explicitly issued the ALL clause. The reason is that UNIONs are assumed to have a high probability for duplicate records. Therefore, DISTINCT becomes the default and the ALL optional.

Remember that creating a DISTINCT query result can be time consuming. Based upon your knowledge of the data, if you are fairly sure you will have few to no duplicates, use the ALL option when declaring a UNION. Further, the output of a query that uses DISTINCT isn't updatable and doesn't reflect subsequent changes made by other users.

The last rule for unions deals with those SQL SELECT clauses that affect the entire result table. In the case of our UNION, the clause is the ORDER BY. Because ORDER BY sorts the entire result table, this clause can be issued only one time and only from the last SELECT in the UNION.

However, you can use a GROUP BY and/or HAVING clause in each of the UNION queries to group the returned data. These clauses act upon an intermediate table rather than the final table.

Finally, a UNION is one of the query types not directly accessible through the QBE. Instead, you must choose the SQL Specific option from the Query menu. This selection brings you into the SQL View window. In other words, you need to know how to write your own queries by hand to write a UNION.

You may want to test the individual queries and make sure that you are getting the required result before creating a UNION. Remember that you are allowed to combine the results of separate queries that already exist in the database, as well as write the UNION as a single query.

Why do I get Reserved error (-1310) when running a query?
Keywords: Error Query
Posted Nov 15, 1996

When you run a query that contains aggregate (totals) functions, you receive the error message:

Reserved error (-1310); there is no message for this error.

This problem occurs only with queries for which all of the following conditions are true:

• The query does not contain remote (ODBC) tables
• The query contains three or more tables and can be divided into two or more queries to obtain the same results
• The query does not have any criteria on the aggregate fields

The error is caused by the query optimizer. Any changes made to the query or its underlying tables that cause Microsoft Access to change its query optimization strategy can either result in or resolve this error. Changes include adding criteria to the query, adding indexes to or removing them from underlying tables, adding rows to underlying tables, and then resaving the query, and so on.

The best way to prevent the error is to divide the query into two or more separate queries that produce the same result.

Adding criteria to the query can also prevent the error from occurring. Add criteria that do not affect the outcome of the query, such as comparing a field to itself. For example, you could use the following criteria for a query that includes the Customers, Orders, and Order Details tables:

Customers.[Customer ID]=Customers.[Customer ID]

Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This problem no longer occurs in Microsoft Access for Windows 95. or with the Microsoft Jet database engine version 2.5

What causes the "Query contains ambiguous outer joins." error?
Keywords: Error Query
Posted Nov 15, 1996

This error message occurs when you try to run a query that contains certain combinations of joins. (The three types of joins in Microsoft Access are left outer joins, right outer joins, and equi-joins.) For example, running the following query results in the error message stated above:

Consultants ---> Active Consultants ---- Projects

The SQL statement for this query reads:

Consultants LEFT JOIN [Active Consultants] EQUI-JOIN Projects

The sample query above (Consultants LEFT JOIN [Active Consultants] EQUI-JOIN Projects) can be processed in two ways. The result of the left outer join between the Consultants and Active Consultants tables can be equi-joined to the Projects table:

(Consultants ---> Active Consultants) ---- Projects

Or, the Consultants table can be left outer joined to the result of the equi-join between the Active Consultants and Projects tables

Consultants ---> (Active Consultants ---- Projects)

You must specify which method should be used by changing one of the joins or by separating the query into two queries.

There are three rules that can be derived from this:

1. Using all equi-joins in a query is acceptable
2. Arrows pointing away from the middle table (pointing away from other joins) are acceptable
3. Arrows pointing toward the middle table require that the other join point in the same direction (if an arrow points toward another join, that join must point in the same direction)

When can I update fields in a multi-table query?
Keywords: Join Query
Posted Nov 15, 1996

An updatable query is one which every row retrieved, and every column in every row, is accessible and subject to change. Every row can be deleted, and new rows can be inserted. Any changes made to the result set created by this query would automatically be reflected in the underlying table.

Single-Table queries are normally always updatable. Exceptions would occur if the table was an attached non-native table such as a Paradox or Dbase. Such tables usually need a unique index applied to them before they can be updated.

In Regards to Multitable queries, some of the rules differ depending on the type of Join that exists in the query.

1. You must specify an explicit INNER or OUTER JOIN between tables. Joins created implicitly in the WHERE clause of the SELECT statement are not updatable
2. Summary (GROUP BY), UNION, DISTINCT, and crosstab queries are never updatable. Queries joined to one or more summary queries are not updatable, even if you do not attempt to modify fields from an otherwise updatable table
3. A query may be updatable if it refers to a summary query in a sub-SELECT statement, as in the following example:

   SELECT DISTINCTROW Order.*
   FROM Orders
   WHERE (((Orders.Freight)>
   	SELECT DISTINCTROW Avg(Orders.Freight) AS AvgOfFrieght
   	FROM Orders;)));

   In this case columns from the Orders table are updatable.

4. To be able to insert new rows into a table in any query, all primary key columns must be present

Why do I get "Out of Memory" or "Query too Complex"?
Keywords: Query
Posted Nov 15, 1996

Sometimes you receive an "Out of Memory" or "Query too Complex" error message when you run a query, a form, or a report based on a query.

This occurs because in Microsoft Access 1.x and 2.0, queries must compile within a 64 kilobyte (K) segment. If you generate a query that is greater than 64K, you may see either the "Query too Complex," or the "Out of Memory" error message. In Microsoft Access for Windows 95, the 64k limit is replaced by a dynamic limit which offers much more room; however, it is still possible to make a query too complex.

Reports create temporary queries for each section of the report, including the report header, page header, group header, detail section, group footer, page footer, and report footer. All of the temporary queries for each report are combined into a segmented virtual table (SVT). The final output must be compiled within the 64K segment limit.

Similar 64K limits are used to compile and store all of the expressions from page to page when the report is being processed or to store the unbound controls or label information. If any of these segments exceed the limit, controls on the report may start displaying the "#Name?" error message.

The following items suggest several ways to reduce the complexity of your queries or report:

• Shorten table names, column names, and control names. Reducing a 30-character name to the minimum length may help.
• Reduce the number of text fields on the report.
• Avoid extra overhead by removing any fields in the underlying query that are not used in the final output for the report.
• Reduce expressions in underlying queries. Reducing space used for expressions in the select list helps to avoid these errors. If possible, place the expressions directly in the report.
• Move complex expressions to a user-defined function that does all the evaluating.
• Avoid stacked query objects, such as situations in which Query1 is used to pull data from Table2 and Query2 filters the data. Pulling information together in one query is preferable to having multiple queries, each doing portions of the task.
• Avoid basing main reports and their subreports on the same queries. Look for stacked query objects and for tables that are unnecessary to the subset.
• Use subreports to break up a complex report into several less complicated reports.
• If the report is based on a query, build a temporary table from the query to base the report on instead of the query. Create a Make Table query that includes all the fields from the original query to build the temporary table.
• Shorten SQL statements by using the Alias property for the field list. From the View menu bar, select Properties. Click on any field list and change the Alias property to something shorter to shorten the SQL statement.

Why do my column headings disappear in CrossTab query?
Keywords: Crosstab Query
Posted Nov 15, 1996

By default, Access presents the summarized columns in alphabetical order from left to right. Missing values are returned as NULL. If no values at all exist, then the column is not created.

You can force Access to create columns in an order other than alphabetical and force the creation of columns even if there is no data for that column value, by using the optional IN clause with the PIVOT clause, i.e.:

TRANSFORM Avg (Employees.Salary) AS AvgOfSalary
SELECT Employees.Title
FROM Employees
GROUP BY Employees.Title
PIVOT Employees.Country In ("UK","Germany","France","USA")

How do I replace Nulls with Zeros in a CrossTab query?
Keywords: Crosstab Query
Posted Nov 15, 1996

When you run a crosstab query to summarize data in a row-and-column format, you may have blank (or null values) in some cells. You can replace the nulls with zeros by using the IIF() function within an expression in the Crosstab column of the QBE grid i.e.

IIF(Sum([Extended Price]) Is Null,0,Sum([Extended Price]))

In Microsoft Access for Windows 95, you can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null.

What are the best ways to optimize queries?
Keywords: Query Optimization
Posted Oct 3, 1996

Here are several rules to follow when designing queries:

1. Index fields used in query criteria.
   Access is optimized to use indexes effectively. While Access can optimize queries with a single expression, Rushmore query technology (borrowed from FoxPro) allows you to combine two criteria with optimizable expressions using logical AND or OR operator. Solving a query using Index Intersection involves scanning multiple indexes for records matching a criteria such as:

   [Cust_NO]=5 AND [Ship_Date]=#1/1/96#	

   This will work well as long as both fields are indexed. Another example of Rushmore is Index Unions. This involves scanning multiple indexes for records matching a criteria such as:

   [Cust_NO]=5 OR [Ship_Date]=#1/1/96#	

2. Index fields used in joins
   This will allow Access to use more sophisticated join strategies such as the index and index-merge joins.
3. Avoid calculated fields in nested queries.
   Access allows you to use a query as a table source for a query, this produces a nested queries. Calculated fields in the nested queries will slow performance considerably. Instead place them at the top level.
4. Never use "Select * from TableName".
   Always use the field names in your queries. When you use the "*" wildcard, Access must look up the field names from the system tables each time your query is run.
5. Always use stored queries.
   When creating a query, the Jet Engine does a lot of things in the background.
   • First, the parser/binder parses the SQL statement and binds the names referenced in the query to columns in the underlying tables. It checks the syntax and then stores the SQL string as a query object
   • Second, the folder moves expressions up the query tree to avoid having to be computed against records that may be discarded.
   • Third, the splitter splits conglomerate QueryDef objects into their discrete components and removes them from the query tree.
   • Fourth, the Flattener takes the output of the splitter and combines joins, order by clauses and where clauses.
   • Fifth, the optimizer uses current statistics to figure out the best way to execute the query. It is a cost based system that produces a query plan.
   • Sixth, the remote post-processor is used for ODBC datasources to determine how much of the query can be sent to the back end for processing.
   • Seventh, the post-processor takes the query graph in its current state and moves it to a new data-execution structure.
   • Lastly, the join processor executes the compiled query.

In summary, after they have been run stored queries are saved as compiled queries with a query plan. The next time the query is run, Jet does not have to parse and recompile it. An obvious caveat to this is to avoid using dynamic SQL. A more efficient way would be to create a parameter query and then pass the parameters to it via code.

How do I use a function as a parameter in a query?
Keywords: Query Function Parameter
Posted Oct 3, 1996

Commonly, users pass control references as parameters in queries. An example such as:

[Cust_ID]=Forms!MyFormName!MyControlName	

The only problem with this is that now the reference is hardcoded into a query. Another approach would be to use a function instead of an explicit reference. Using a function, allows you to use variables for the form and control name. Also, you could actually do calculations based on currently displayed data. An example follows:

Function RptPartsGrpWeek() As Long
''' Function: RptPartsGrpWeek() As Long
'''
''' Comments:   	passes a parameter from a form to the query
'''             	uses 2 global variables which pass the form and control name
'''             	This returns the right value for a between statement if
'''             	there was a hyphen in the field.
'''
''' Arguments:  	none
'''
''' Returns:    	variant
'''
''' Called by:  	Union queries for RptMotorGrpWeek report
'''
''' Keywords:   	PARAMETER QUERY
'''
''' Date        Developer                               Action
''' -------------------------------------------------------------------------
''' 8/09/96    Marty Wasznicky                         created
On Error Resume Next
    Dim varItem As Variant, varLeft As Variant, varRight As Variant
    Dim lngPos As Long, lngEndPos As Long
    Const gcHypen = "-"
    varItem = Forms(Trim$(gstrFormName))(Trim$(gstrCtlName))
        ''' Find starting position of "-"
        lngPos = InStr(1, varItem, gcHypen)
        If lngPos > 0 Then
            
            ''' Retrieve everything to the left
            varLeft = Left(varItem, lngPos - 1)
            
            ''' Retrieve everything to the right
            varRight = Mid(varItem, lngPos + 1, Len(varItem) - lngPos)
            
            ''' Build the Between statement
            varItem = varRight
            
        End If
        
        RptPartsGrpWeek = CLng(varItem)
End Function			

In Access 2.0, this worked OK. You could create global variables for the form and control and use the variables in a function. In Access 95 you can create a public function in the form module. This would allow you to use module level variables in your function as shown below:

Public Function RptParameter() As Variant
''' Function:   RptParameter() As Variant
'''
''' Comments:   	passes a second parameter from a form to the query
'''             	uses "Me" and a variable for the control.  Nz function is used
'''		to handle nulls.
'''
''' Arguments:  none
'''
''' Returns:    variant
'''
''' Called by:  queries for PO number for RptShopTraveler report
'''
''' Keywords:   PARAMETER QUERY
'''
''' Date        Developer                               Action
''' -------------------------------------------------------------------------
''' 8/09/96    Marty Wasznicky                         created
On Error Resume Next
    RptParameter = Nz(Me!txtPO_NO,0)
End Function
			

How do I use a subquery as criteria in a where clause?
Keywords: Query Sub
Posted Oct 3, 1996

A great way to restrict the output of a query is by using another query as your criteria. For example, if you want to bring back all the product names for a customer who has ordered quantities greater than 10 you could use the following:

SELECT ProductID, ProductName, ProductDesc, ProductPrice
FROM tblProducts
WHERE ProductID IN (Select ProductID FROM tblOrders WHERE Quantity>=10);			

Subqueries can also be used to compare a value against rows in another query. An example would be to find all wine prices higher than a Kenwood Sauvignon Blanc:

SELECT WineName, Vintage, Price
FROM tblWine
WHERE  Price > (SELECT Price FROM tblWine WHERE WineName=" Kenwood Sauvignon Blanc"); 
	

How do I pass a query parameters via code?
Keywords: Parameter Query
Posted Sept 30, 1996

Many times users must select values in forms and, depending on the values selected, do something. Many times a developer will write dynamic SQL in the module and pass in the data selected from the user this way.

There are many problems associated with this; the main one being maintainability!

Another way to accomplish the same task is to create and save a predefined parameter query, for example:

PARAMETERS Customer Long, Motor Long;
DELETE DISTINCTROW tblCUST_MOTOR.CUST_ID, tblCUST_MOTOR.MOTOR_ID
FROM tblCUST_MOTOR
WHERE (((tblCUST_MOTOR.CUST_ID)=[Customer]) AND ((tblCUST_MOTOR.MOTOR_ID)=[Motor]));			

It is then an easy thing to use this predefined query in a module, passing it the parameters it needs:

Sub RemoveMotorID()

''' Function: RemoveMotorID()
'''
''' Comments:   	This subroutine retrieves the values from form controls,
‘’’		creates a querydef object for an existing query, passes this
‘’’		querydef parameters and then executes the querydef
'''
''' Arguments: none
'''
''' Returns:    none
'''
''' Called by:
'''
''' Keywords:   MOTOR DELETE 
'''
''' Date        Developer                               Action
''' -------------------------------------------------------------------------
''' 8/09/96    Marty Wasznicky                         created

On Error GoTo RemoveMotorID_Err

    Dim intSelected As Integer
    Dim lngMotorID As Long, lngCustId As Long
    Dim qdfMotorDelete As QueryDef
    Dim errdata As gTypeErr
    Dim strProcName As String * 255
    strProcName = "RemoveMotorID"
    
    ''' Check if motor selection was made
    intSelected = lstMotorSelect.ItemsSelected.Count
    If intSelected < 1 Then
        MsgBox "You must first select a Motor."
        Exit Sub
    Else
        ''' If there is a selection, store it
        lngMotorID = lstMotorSelect.Column(0)
    End If
    
    ''' If there is a selection, store it
    lngCustId = txtId
   
    ''' Open querydef, passing it the CustID and MotorID as parameters
    Set qdfMotorDelete = CurrentDb.QueryDefs("qdelCustMotor")
    qdfMotorDelete.PARAMETERS!Customer = lngCustId
    qdfMotorDelete.PARAMETERS!Motor = lngMotorID
   
    ''' Execute querydef
    qdfMotorDelete.Execute
    
    ''' Requery listboxes
    Call RefreshLists
    
RemoveMotorID_Exit:
    Exit Sub

RemoveMotorID_Err:
    Call ErrSave(errdata)
    Call ErrorMsg(errdata, Trim$(strProcName), varIcon:=vbExclamation)
    Resume RemoveMotorID_Exit
End Sub

How can I Import/Append data from an external database to an internal table?
Keywords: Query Dynamic Import
Posted Sept 30, 1996

There are three ways to refer to data sources physically located outside an Access database in a SQL statement:

1. Use linked tables
2. Use the IN clause
3. Use direct references to the external tables

The most efficient and easiest way to reference external tables is to use linked tables. Sometimes though the linking must be done dynamically by the user because data sources are always changing. This situation arises when polling for data for summary or collection. Another situation prohibits altogether the dynamic linking or importing of tables. This arises when users are using an Access 2.0 database in an Access 95 environment. Unless the database is converted, users cannot create new objects, thus eliminating linking or importing tables.

A simple solution to this would be prompting the user for the name and location of the datasource via a common dialog or input box. Then execute dynamic SQL which would append the data to a preexisting table.

Since a dynamic SQL statement is being executed, no objects are created.

An example is listed below:

Sub GetOutsideData()

''' Function: GetOutsideData()
'''
''' Comments:   	This routine prompts the user for the location of the database 
‘’’		containing the data to import.  A dynamic SQL statement is constructed
‘’’		building the connect string into the SQL statement.  The statement is 
‘’’		then executed and the data appended to a preexisting table.  It is 
‘’’		assumed the user the table and field names of the outside table is 
‘’’		constant.
'''
''' Arguments: none
'''
''' Returns:    none
'''
''' Called by:
'''
''' Keywords:   APPEND QUERY 
'''
''' Date        Developer                               Action
''' -------------------------------------------------------------------------
''' 8/09/96    Marty Wasznicky                         created

On Error Resume Next

    Dim varReturn As Variant, db As Database
    Dim szdatasource As String, szSql As String, szMsg as string
    Dim szTitle as string, szAnswer as string

    szMsg = "Enter Path to database"
    szTitle = "Run Data Demo"
    szAnswer = InputBox$(szMsg, szTitle)

    Const gcErrDiskorNetwork = 3043

    If Len(szAnswer) < 1 Then Exit Sub

    Set db = CurrentDb()

    szSql = "INSERT INTO tblTest ( PMC ) SELECT DISTINCTROW LIMS_CUST.PMC FROM _
                  LIMS_CUST IN " & Chr$(34) & szAnswer & Chr$(34) & ";"

    db.Execute Trim$(szSql)

    ''' This traps the network error that occurs when the data file is
    ''' located on a network drive instead of the local hard disk.
    ''' This error only occurs in Access 7.  Also this only occurs using a Novell
    ''' network client and the extension to your data file is ".dat"!!!!!

    If Err = gcErrDiskorNetwork Then
        MsgBox "You place data file on local hard disk"
    End If

End Sub

Back to Top

Back to the FAQ Table of Contents

If nothing sticks to Teflon, how do they stick Teflon on the pan?