• You may have typed incorrect syntax in SQL view (SQL view: A window that displays the SQL statement for the current query or that is used to create an SQL-specific query (union, pass-through, or data definition). When you create a query in Design view, Access constructs the SQL equivalent in SQL view.).
• You created your query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) under a different ANSI SQL query mode (ANSI SQL query mode: One of two types of SQL syntax: ANSI-89 SQL (also called Microsoft Jet SQL and ANSI SQL), which is the traditional Jet SQL syntax; and ANSI-92 SQL, which has new and different reserved words, syntax rules, and wildcard characters.) than the current mode of your Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.).

  Why you should avoid mixing queries created under different ANSI SQL query modes

  The two ANSI SQL query modes (ANSI SQL query mode: One of two types of SQL syntax: ANSI-89 SQL (also called Microsoft Jet SQL and ANSI SQL), which is the traditional Jet SQL syntax; and ANSI-92 SQL, which has new and different reserved words, syntax rules, and wildcard characters.), ANSI-89 and ANSI-92, are not compatible. When you create a Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.), you need to decide which query mode you are going to use, because mixing queries (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) created in both query modes could produce runtime errors or unexpected results. The range of data types, reserved words, and wildcard characters are different in each query mode.

  Example of a query using wildcard characters

  A query that uses wildcard characters in a criteria expression can produce different results under each query mode. For example, if you run:

  • An ANSI-89 SQL query in a database set to ANSI-92 query mode, such as:

    SELECT * FROM Customers WHERE Country Like 'U*'
    								

    
    

    It returns all customers from a country/region named "U*", not all countries/regions beginning with the letter "U", because the asterisk (*) is not a wildcard character in ANSI-92 SQL.

  • An ANSI-92 SQL query in a database set to ANSI-89 query mode, such as:

    SELECT * FROM Customers WHERE Country Like 'U%'
    								

    
    

    It returns all customers from a country/region named "U%", not all countries/regions beginning with the letter "U", because the percent sign (%) is not a wildcard character in ANSI-89 SQL.

  Example of a query with a duplicate field and alias name

  If a query uses an alias that is the same as a base column name and you create a calculated field using the ambiguous name, the query will produce different results under each query mode. For example, if you run the following query:

  SELECT Orders.OrderID AS Freight, Orders.Freight, [Freight]*1.1 AS [New Freight] FROM Orders;

  Under ANSI-92 SQL, Access calculates the New Freight value using the Freight field, and therefore increases each Freight by 10%. Under ANSI-89 SQL, Access calculates the New Freight value using the Freight alias, and therefore increases each OrderID by 10%.

  How to avoid problems caused by mixing queries under different ANSI SQL query modes in the same database

  In general, avoid doing the following to prevent problems caused by mixing queries created under different ANSI SQL query modes:

  • Converting an Access database set to ANSI-92 SQL query mode from 2002 - 2003 file format to 2000 or 97 file format. ANSI-92 SQL query mode is only supported in the 2002 - 2003 file format.
  • Changing the ANSI SQL query mode for the current database after you've created one or more queries. If you must do this, retest the existing queries to ensure that they still run or produce expected results, and rewrite the queries if necessary.
  • Importing queries created under one ANSI SQL query mode into an Access database set to another mode, or exporting queries created under one ANSI SQL query mode to an Access database set to another mode.
  • Changing the ANSI SQL query mode in an existing application. This could break your application and require rewriting your application.

    In Access 2000, you can only programmatically change the ANSI SQL query mode and any queries created under ANSI-92 mode were hidden in the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.). In Access 2002 or later, you or a user can change ANSI SQL query mode through the user interface and ANSI-92 queries are no longer hidden in the Database window, so you should prevent accidental or intentional changes to the ANSI SQL query mode of your application by helping protect your code and preventing the changing of the query mode through the application's user interface.

  • Using ambiguous aliases and column names. To avoid confusion, ensure that aliases and column names are always unique in an SQL statement.

You may encounter this message if you didn't explicitly declare in the Query Parameters dialog box the parameters used for a crosstab query (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.) or a query that a crosstab query or chart is based on. To resolve this, do the following:

1. In the query that contains the parameter, specify the parameter and its data type in the Query Parameters dialog box.

   How?

   1. On the Query menu, click Parameters.

   2. In the first Parameter cell, type the first prompt you entered in the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.).

   3. In the Data Type cell to the right, click the appropriate data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) according to the following guidelines:

      If the parameter field data type is	Click this data type in the Data Type cell
      Currency, Date/Time, Memo, OLE Object, Text, and Yes/No	Currency, Date/Time, Memo, OLE Object, Text, and Yes/No
      Number	Byte, Single, Double, Integer, Long Integer, and Replication ID. These data types correspond to the five FieldSize property settings of the Number data type.
      Unknown	Value. This is a generic data type that accepts any type of data.
      Binary	Binary. Used with parameter queries directed to linked tables (linked table: A table stored in a file outside the open database from which Access can access records. You can add, delete, and edit records in a linked table, but you cannot change its structure.) that do recognize this data type (Microsoft Access does not recognize this data type).

   4. Repeat Steps 2 and 3 for each parameter you want to specify a data type for.

2. In the same query, set the ColumnHeadings property.

   How?

   You can change the order or limit the column headings in a crosstab query (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.). For example, in column headings containing the months of the year, you can display the months chronologically rather than alphabetically. Or, you can limit the columns to just January through June.

   1. Open the crosstab query in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).
   2. Click the background of query Design view, outside the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) and the field lists (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.).
   3. On the toolbar, click Properties to display the query's property sheet.
   4. In the ColumnHeadings property box, enter the column headings you want to display, in the order in which you want to display them. Between the column headings, type a comma or the list separator for your country/region. (To find the list separator for your country/region, see the regional settings in Microsoft Windows Control Panel.)

      The column headings you enter must exactly match the column headings in the query datasheet. For example, if a column heading in the datasheet is "USA," you must enter a column heading of "USA"— not "US." (After you press ENTER or move the pointer to a different location, Microsoft Access places quotation marks around each heading.)

   5. To view the query's results, click View on the toolbar.

   Notes

   • If you run a crosstab query often, or if you use it as the basis for a form, report, or data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.), you can speed up the query by using the preceding procedure to specify fixed column headings.
   • If you frequently use the same column headings in different queries, consider creating a table with one Text (Text data type: In a Microsoft Access database, this is a field data type. Text fields can contain up to 255 characters or the number of characters specified by the FieldSize property, whichever is less.) field to store the column headings. Then open the table and copy the headings into the ColumnHeadings property box as needed.

A key violation occurs if:

• You attempt to run a query that appends, deletes, or updates records in a way that violates the rules of referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you enter or delete records.) for related tables.
• Your query attempts to append or update records that contain primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) values that already exist in the destination table.

If you run such a query, it will not modify the records that cause the key violation.

To modify records in a way that will violate referential integrity, you can break the relationship between the affected tables or turn referential integrity off, and then modify the affected tables one at a time. However, the resulting data in the two tables may then conflict.

A validation rule (validation rule: A property that defines valid input values for a field or record in a table, or a control on a form. Access displays the message specified in the ValidationText property when the rule is violated.) violation occurs if you attempt to update or append records that violate the validation rules for a field or record.

When you see the following message:

"The SQL statement couldn't be executed, because it contains ambiguous outer joins."

You tried to execute an SQL statement that contains multiple joins (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.). There are three types of joins in Microsoft Access: inner joins (inner join: A join where records in two tables are combined in a query's results only if values in the joined fields meet a specified condition. In a query, the default join is an inner join that selects records only if values in the joined fields match.) (also called equi-joins), left outer joins (outer join: A join in which each matching record from two tables is combined into one record in the query's results, and one table contributes all of its records, even if the values in the joined field don't match those in the other table.), and right outer joins. In some multiple joins, the results of the query can differ depending on the type of join and the order in which the joins are performed.

Consultants LEFT OUTER JOIN Active Consultants INNER JOIN Projects
								

• Verify that the criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) you specified is for the same data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) as the data in the underlying table or query. For example, the field ReorderLevel has a Number data type. Therefore, if you type the criteria "50", you'll get an error because Microsoft Access interprets values in quotation marks as text, not numbers.

  Other situations that cause a data type conflict include:

  • You're specifying criteria for a Lookup field (Lookup field: A field, used on a form or report in an Access database, that either displays a list of values retrieved from a table or query, or stores a static set of values.) and the criteria uses the values that display on the lookup list instead of their associated foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) values. Because the foreign key values are the values actually stored in the underlying table, you should use them when you specify criteria for a field.
  • You typed the dollar sign ($) in criteria you specified for a Currency field. Remove the dollar sign, and then view the results.
• Verify that the data type of each pair of joined (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) fields in the query is the same. If not, change the data type of one of the joined fields to match the data type of the other.

You may encounter this message if a parameter query (parameter query: A query in which a user interactively specifies one or more criteria values. A parameter query is not a separate kind of query; rather, it extends the flexibility of a query.) exported to the IDC (IDC/HTX files: Microsoft Internet Information Server uses an IDC file and an HTX file to retrieve data from an ODBC data source and format it as an HTML document.) file type doesn't have data types specified in the Query Parameters dialog box.

1. In the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.), click Queries under Objects.
2. Click the name of the parameter query you want to modify, and click Design on the Database window toolbar.
3. Specify a data type for the parameter.

   How?

   1. On the Query menu, click Parameters.

   2. In the first Parameter cell, type the first prompt you entered in the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.).

   3. In the Data Type cell to the right, click the appropriate data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) according to the following guidelines:

      If the parameter field data type is	Click this data type in the Data Type cell
      Currency, Date/Time, Memo, OLE Object, Text, and Yes/No	Currency, Date/Time, Memo, OLE Object, Text, and Yes/No
      Number	Byte, Single, Double, Integer, Long Integer, and Replication ID. These data types correspond to the five FieldSize property settings of the Number data type.
      Unknown	Value. This is a generic data type that accepts any type of data.
      Binary	Binary. Used with parameter queries directed to linked tables (linked table: A table stored in a file outside the open database from which Access can access records. You can add, delete, and edit records in a linked table, but you cannot change its structure.) that do recognize this data type (Microsoft Access does not recognize this data type).

   4. Repeat Steps 2 and 3 for each parameter you want to specify a data type for.

4. Save and run the query.

This error occurs when the current query's Update To row includes a field from either a crosstab query or select query in which an aggregate (aggregate function: A function, such as Sum, Count, Avg, or Var, that you use to calculate totals.) (total) was calculated for the field (using either the Totals row or a domain function (domain aggregate function: A function, such as DAvg or DMax, that is used to calculate statistics over a set of records (a domain).) in the Field row). To update a field using the aggregate of another field, calculate the aggregate in the update query itself, not a different query.

1. In query Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.) for the current query, remove the field you added from the crosstab or select query (where an aggregate was calculated for the field).
2. In the Update To cell of the field you want to update, enter a domain function that calculates the same aggregate as is calculated for that field in the select or crosstab query.

   For example, if you had a calculated field (calculated field: A field, defined in a query, that displays the result of an expression rather than displaying stored data. The value is recalculated each time a value in the expression changes.) named Sales So Far in your Products table, you could update it with the product of the Quantity and UnitPrice fields in the Order Details table by adding it to the design grid in the update query and then entering the following domain function in its Update To cell.

   DSum("[Quantity] * [UnitPrice]", "Order Details", "[ProductID]=" & [ProductID])
   								

   
   
3. To run the query, click Run on the toolbar.
4. To see the results, open the table by clicking Tables under Objects in the Database window and clicking Open on the Database window toolbar.

You use wildcard characters as placeholders for other characters when you are specifying a value you want to find and you:

• Know only part of the value.
• Want to find values that start with a specific letter or match a certain pattern.

Wildcard characters are meant to be used with fields that have the Text data type. You can sometimes use them successfully with other data types, such as dates, if you don't use the Microsoft Windows Control Panel to change the regional settings for these data types.

If you are using Microsoft Jet database engine SQL in a Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.), you can use the following wildcard characters in queries (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) and expressions (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) to find such things as field values, records, or file names. You can also use these characters in the Find and Replace dialog boxes in an Access database or a Microsoft Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects such as tables and views.).

To find such things as field values, records, or file names in a Microsoft Access project, or in a Microsoft Access database that uses Microsoft SQL Server-compatible syntax, use the following ANSI-92 wildcards.

Note  If you are using the Find and Replace dialog boxes in an Access project or in an Access database that uses Microsoft SQL Server-compatible syntax, you must use the Microsoft Jet SQL wildcards.

Notes

• When using wildcard characters to search for other wildcard characters such as an asterisk (*), question mark (?), number sign (#), opening bracket ([), or hyphen (-), you must enclose the item you're searching for in brackets. If you're searching for an exclamation point (!) or a closing bracket (]), you don't need to enclose it in brackets.

  For example, to search for a question mark, type [?] in the Find dialog box. If you're searching for a hyphen and other characters simultaneously, place the hyphen before or after all the other characters inside the brackets. (However, if you have an exclamation point (!) after the opening bracket, place the hyphen after the exclamation point.)

• You can't search for the opening and closing brackets ([ ]) together because Microsoft Access interprets this combination as a zero-length string (zero-length string: A string that contains no characters. You can use a zero-length string to indicate that you know no value exists for a field. You enter a zero-length string by typing two double quotation marks with no space between them (" ").). You must enclose the opening and closing brackets in brackets ([[ ]]).

• If tables or queries in a query aren't joined (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) to one another, either directly or indirectly, Microsoft Access doesn't know which records are associated with which, so it displays every combination of records (called a "cross-product" or "Cartesian product") between the two tables. Therefore, if each table had 10 records in it, the query's results will contain 100 records (10 X 10). It also means the query might take a long time to run and ultimately might produce meaningless results. To prevent this, create a join between the tables or queries.

• If tables or queries are already joined, check the join properties of each join line to see what type of join is in effect. Join properties determine whether one table or query contributes all of its records or only those records that match records in the other table.

• Add criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) to limit the records that the query retrieves. Only records that meet the criteria you define are retrieved.
• If you already specified criteria, you may want to specify additional criteria, either for the same field using the And operator, or for other fields. Also, verify that existing criteria is entered correctly. Check for misspellings, unnecessary spaces, or extra characters. Verify that you set the criteria for the appropriate field and that it's the criteria you want the records to meet. If you're using alternative criteria in the Or row in the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) or in criteria expressions (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) that use the Or operator, you may want to remove it.
• Check the data you're trying to find. If the data contains extra spaces or other special characters, the criteria must account for this. You can use wildcard characters in the criteria if you aren't sure of or don't want to specify the exact field values. If you're already using wildcard characters and not getting the records you want, you may be using the wildcard characters on the wrong data type.
• In a select (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.), append (append query: An action query that adds the records in a query's result set to the end of an existing table.), or make-table query (make-table query: A query (SQL statement) that creates a new table and then creates records (rows) in it by copying records from an existing table.), if you're using Top Values on the toolbar in query Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.) (or setting the TopValues property in the query's property sheet) to return a specified number of the highest or lowest values in a field, the query might return more records than the number you specified. This is because all records that match the last record are also returned. Therefore, a query would return all three of the following records even if you requested to see the employees with the top two sales totals because the query doesn't distinguish between the two 50,000 sales totals.

  EmployeeID	Sales
  1	60,000
  2	50,000
  3	50,000

  If you don't want duplicate values to be returned, set the UniqueValues property in the query property sheet to Yes.

• If the query includes more than one table or query, check the join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) properties of each join line between them. Join properties determine if a table or query contributes all of its records or only those records that match records in the other table.

• If the type of join you're using retrieves only matching records from the two joined tables, any records in which the joined field contains a Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.) value won't be included in the query's results. You can use the Nz function to convert Null values to a zero, zero-length string, or other specified value so that those records are included in the query's results.
• If you specified criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.), it may be too restrictive. For example, if you specified criteria in the Criteria row for two different fields, both criteria must be true for a record before the query retrieves it. You may want to delete some or all of the criteria to allow the query to return more records.
• If you have entered criteria that combine two or more expressions (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) with an OR operator on more than one criteria row, you must repeat any other expressions in other fields for each criteria row you have used in order for those other expressions to apply to all criteria. For example, if your combined criteria for a ShipCountry field is "Canada" OR "UK", and you want to limit the ExtendedPrice field to <10000 for both countries, enter all the criteria on one criteria row, or repeat the <10000 expression for each criteria row you use.
• Verify that existing criteria is entered correctly. Check for misspellings, unnecessary spaces, or extra characters. Verify that you set the criteria for the appropriate field and that it's the criteria you want the records to meet.
• If a field name includes a colon, you must enclose the name in brackets whenever you refer to the field in an expression. Otherwise, Microsoft Access interprets the field name as a literal text value, and therefore won't retrieve the records you want.
• Use wildcard characters in the criteria if you aren't sure of or don't want to specify the exact field values. If you're already using wildcard characters and not getting the records you want, you may be using the wildcard characters on the wrong data type.
• Add alternative criteria to select the records you want to work with. You can do this in the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) in the Or row, or in the Criteria row using expressions containing the Or operator.
• If you're using criteria to select records containing specific values in a field or fields, any records with Null values in that field don't match the value, and therefore won't be included in the records that are returned. To include records containing Null values, type Is Null in the Or row.

• Check the data you're trying to find. If the data contains extra spaces or other special characters, the criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) must account for this. You can use wildcard characters in the criteria if you aren't sure of or don't want to specify the exact field values. If you're already using wildcard characters and not getting the records you want, you may be using the wildcard characters on the wrong data type.
• Verify that existing criteria is entered correctly. Check for misspellings, unnecessary spaces, or extra characters. Verify that you set the criteria for the appropriate field and that it's the criteria you want the records to meet.
• Verify that the tables and queries you've added to the upper part of query Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.) are the correct ones.

You created your query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) under a different ANSI SQL query mode (ANSI SQL query mode: One of two types of SQL syntax: ANSI-89 SQL (also called Microsoft Jet SQL and ANSI SQL), which is the traditional Jet SQL syntax; and ANSI-92 SQL, which has new and different reserved words, syntax rules, and wildcard characters.) than the current mode of your Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.).

• Remove the fields in the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) that you don't want to see in the query results.
• Remove the asterisk (*) if you added it to the design grid. The asterisk automatically includes all fields from the underlying table. If you want to keep the asterisk (*), clear the Show check box for all fields except the asterisk; otherwise those fields will appear twice in the query's results.
• Hide fields that you don't need to display in the query's results.
• Verify that the Output All Fields check box isn't selected. (To see if it is selected, on the Tools menu, click Options, and then click the Tables/Queries tab.) If it is, all fields from the underlying table or query will appear in the query's results without being added to the query design grid. If you then add a field to the design grid, say, for setting criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.), you need to clear the Show check box for that field; otherwise, the column appears twice in the query's results. If you want, clear the Output All Fields check box. Changing this option affects the property setting for new queries you create, but it doesn't affect existing queries.

• Add more fields to the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) or, if you add the asterisk (*) to the design grid, the query automatically includes all fields from the underlying table or query.
• Add other tables or queries, then add the other fields you need.
• Show hidden fields.
• If the Output All Fields check box is not selected, only the fields in the design grid are retrieved. (To see if it is selected, on the Tools menu, click Options, and then click the Tables/Queries tab.) If it is, all fields from the underlying table or query will appear in the query's results without being added to the query design grid. If you then add a field to the design grid, say, for setting criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.), you need to clear the Show check box for that field; otherwise, the column appears twice in the query's results. Or, if you want, clear the Output All Fields check box. Changing this option only affects the property setting for new queries you create, not existing queries.

• In the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.), remove the fields that you don't want the query to retrieve.
• If columns are missing, add their fields to the query design grid.
• Verify that the tables and queries you're retrieving records from are the correct ones.

You created your query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) under a different ANSI SQL query mode (ANSI SQL query mode: One of two types of SQL syntax: ANSI-89 SQL (also called Microsoft Jet SQL and ANSI SQL), which is the traditional Jet SQL syntax; and ANSI-92 SQL, which has new and different reserved words, syntax rules, and wildcard characters.) than the current mode of your Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.).

• If there are records with Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.) (blank) values in the field you are calculating, those records won't be included in the calculation.
• If the query isn't performing the calculation on the correct groups, verify that you have Group By in the Total cell for the field or fields you want to use for grouping calculations. Also, where you specify criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) in the query design grid determines if records are excluded before the query groups or performs calculations, if groups are excluded before the calculation is performed, or if certain results are excluded after the calculation is performed.
• In a crosstab query (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.), you might have too many or too few Group By fields. Add, remove, or change the fields designated as row headings that group the totals vertically. Verify that you're using the correct field's values as column headings that group the totals horizontally.
• Verify that the aggregate (aggregate function: A function, such as Sum, Count, Avg, or Var, that you use to calculate totals.) you selected is the correct one. If not, select a different aggregate from the list in the Total cell.

In the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.), delete the aggregate (aggregate function: A function, such as Sum, Count, Avg, or Var, that you use to calculate totals.) you specified in the Total cell for that field (column), and then select one in a different field.

If you use an arithmetic operator (such as +, -, *, /) in an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.), such as UnitsInStock] + [UnitsOnOrder], and one of the fields in the expression contains a Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.) value, then the result of the entire expression will be a Null value. To avoid this, you can convert Null values to zeros or other numbers by using the Nz function.

You created your query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) under a different ANSI SQL query mode (ANSI SQL query mode: One of two types of SQL syntax: ANSI-89 SQL (also called Microsoft Jet SQL and ANSI SQL), which is the traditional Jet SQL syntax; and ANSI-92 SQL, which has new and different reserved words, syntax rules, and wildcard characters.) than the current mode of your Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.).

For AutoLookup to work, certain conditions must be met:

• The query must be based on more than one table and the tables must have a one-to-many relationship (one-to-many relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.). (Referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you enter or delete records.) doesn't have to be enforced.)
• The join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) field on the "one" side of the relationship must have a unique index (unique index: An index defined by setting a field's Indexed property to Yes (No Duplicates). A unique index will not allow duplicate entries in the indexed field. Setting a field as the primary key automatically defines the field as unique.). A unique index means that the field is a primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) or its Indexed property in table Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.) is set to Yes (No Duplicates).
• The join field you add to the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) must come from the table on the "many" side of the one-to-many relationship. (In other words, it's the foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) for that table.) For AutoLookup to work, this field can't be a primary key and its Indexed property can't be set to Yes (No Duplicates). For example, in a query that includes the Customers and Orders tables, drag to the design grid the CustomerID field from the Orders table, not the one from the Customers table.
• The value you enter in the join field from the "many" side must already exist in the join field from the "one" side.

Even if you haven't created relationships (relationship: An association that is established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.) between tables, Microsoft Access automatically creates joins if you add two tables to a query and the tables each have a field with the same or compatible data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) and if one of the join fields is a primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.). You can enable or disable these automatic joins.

1. On the Tools menu, click Options.
2. Click the Tables/Queries tab.
3. Select or clear the Enable AutoJoin check box.

Note   This setting applies to new queries only.

In some cases, you can edit data in query Datasheet view (Datasheet view: A window that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.) to change the data in the underlying table. In other cases, you can't. The following information shows whether a query's results can be updated, and if not, whether there is an alternative.

If you get prompted to type parameters when you attempt to run a query, report, or data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.), and you don't expect this prompt, one of the following might apply:

• You deleted a parameter from the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) but not from the Query Parameters dialog box.
• You renamed or misspelled a field in the query, report, or data access page but not in the query's underlying table(s).
• You have a field that refers to a calculated field (calculated field: A field, defined in a query, that displays the result of an expression rather than displaying stored data. The value is recalculated each time a value in the expression changes.). If a field in the query performs a calculation based on the calculated value, make sure the Show check box is selected for the calculated field in the query design grid.

You created your query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) under a different ANSI SQL query mode (ANSI SQL query mode: One of two types of SQL syntax: ANSI-89 SQL (also called Microsoft Jet SQL and ANSI SQL), which is the traditional Jet SQL syntax; and ANSI-92 SQL, which has new and different reserved words, syntax rules, and wildcard characters.) than the current mode of your Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.).

If you get prompted to type parameters when you attempt to run a query, report, or data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.), and you don't expect this prompt, one of the following might apply:

• You deleted a parameter from the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) but not from the Query Parameters dialog box.
• You renamed or misspelled a field in the query, report, or data access page but not in the query's underlying table(s).
• You have a field that refers to a calculated field (calculated field: A field, defined in a query, that displays the result of an expression rather than displaying stored data. The value is recalculated each time a value in the expression changes.). If a field in the query performs a calculation based on the calculated value, make sure the Show check box is selected for the calculated field in the query design grid.

1. In the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) under the field that contains the prompt in the Criteria cell, type [prompt] Is Null in the Or cell, where prompt is the same prompt that is in the Criteria cell for that field. For example:

   In this cell	Type
   Criteria	Like [Enter Category ID:]
   Or	[Enter Category ID:] Is Null

2. Run the query.

3. Leave the Enter Parameter Value dialog box blank, when Microsoft Access prompts you.

You created your query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) under a different ANSI SQL query mode (ANSI SQL query mode: One of two types of SQL syntax: ANSI-89 SQL (also called Microsoft Jet SQL and ANSI SQL), which is the traditional Jet SQL syntax; and ANSI-92 SQL, which has new and different reserved words, syntax rules, and wildcard characters.) than the current mode of your Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.).

You can set the query's ColumnHeadings property to specify which column headings are displayed and in what order you want them to appear.

Data in a crosstab query (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.) is not updatable.

Microsoft Access returns "<>" as the column name for any Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.)Null value in the field with Column Heading in its Crosstab cell. To avoid this you can:

• Explicitly define your column headings by setting the query's ColumnHeadings property.

  Sort or limit column headings displayed in a crosstab query

  You can change the order or limit the column headings in a crosstab query (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.). For example, in column headings containing the months of the year, you can display the months chronologically rather than alphabetically. Or, you can limit the columns to just January through June.

  1. Open the crosstab query in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).
  2. Click the background of query Design view, outside the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) and the field lists (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.).
  3. On the toolbar, click Properties to display the query's property sheet.
  4. In the ColumnHeadings property box, enter the column headings you want to display, in the order in which you want to display them. Between the column headings, type a comma or the list separator for your country/region. (To find the list separator for your country/region, see the regional settings in Microsoft Windows Control Panel.)

     The column headings you enter must exactly match the column headings in the query datasheet. For example, if a column heading in the datasheet is "USA," you must enter a column heading of "USA"— not "US." (After you press ENTER or move the pointer to a different location, Microsoft Access places quotation marks around each heading.)

  5. To view the query's results, click View on the toolbar.

  Notes

  • If you run a crosstab query often, or if you use it as the basis for a form, report, or data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.), you can speed up the query by using the preceding procedure to specify fixed column headings.
  • If you frequently use the same column headings in different queries, consider creating a table with one Text (Text data type: In a Microsoft Access database, this is a field data type. Text fields can contain up to 255 characters or the number of characters specified by the FieldSize property, whichever is less.) field to store the column headings. Then open the table and copy the headings into the ColumnHeadings property box as needed.
• In the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.), type Is Not Null in the Criteria cell of the designated Column Heading field.
• In the Criteria cell of the designated Column Heading field, use the Nz function in an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) that will convert Null values to a more meaningful value, such as "Unknown."

Verify that the field whose values you want to use as column headings is in the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) with Column Heading in the Crosstab cell, and that the field or fields whose values you want to use as row headings are in the query design grid with Row Heading in the Crosstab cell. To change the value in a Crosstab cell, click the cell, and then click a value from the list. If you want to display only certain values as column headings, set the query's ColumnHeadings property.

You created your query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) under a different ANSI SQL query mode (ANSI SQL query mode: One of two types of SQL syntax: ANSI-89 SQL (also called Microsoft Jet SQL and ANSI SQL), which is the traditional Jet SQL syntax; and ANSI-92 SQL, which has new and different reserved words, syntax rules, and wildcard characters.) than the current mode of your Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.).

Verify that you have selected the correct fields for the data you want to append, entered the correct criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) in the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.), and selected the correct fields to which you want to append data. Restore the appended query to its original state, through a backup copy if one is available, then do one or more of the following:

Note  If you added the asterisk (*) in the Field cell, select the asterisk in the Append To cell. All the field names in both tables must be exactly the same. You can't specify criteria for selecting records in the column that contains the asterisk, but you can specify criteria in a separate column.

• You need to specify a different update by changing the expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) in the Update To cell. For example, to raise salaries by 5 percent, type [Salary] * 1.05 in the Update To cell of the Salary field.
• Verify that you're updating data in the correct field. If you're updating data in the wrong field, specify an update for a different field. To specify an update for a field, type the expression you want in the Update To cell.

Delete the duplicate record or the original directly in query Datasheet view (Datasheet view: A window that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.).

If you want Microsoft Access to delete all the duplicates and keep the original records automatically, don't use the Find Duplicates Wizard or convert a Find Duplicates query to a delete query.

Make sure the delete query includes:

• The table you want to delete records from. Drag the asterisk (*) from the field list (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.) for that table to the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.).
• The field(s) you want to specify criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) for. Drag the field you want from the table's field list to the design grid.

In the query design grid, From or Where is displayed in the Delete row. From is displayed in the Delete cell of the table you'll delete records from, and Where is displayed in the Delete cell of the field(s) you specify criteria for.

You created your query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) under a different ANSI SQL query mode (ANSI SQL query mode: One of two types of SQL syntax: ANSI-89 SQL (also called Microsoft Jet SQL and ANSI SQL), which is the traditional Jet SQL syntax; and ANSI-92 SQL, which has new and different reserved words, syntax rules, and wildcard characters.) than the current mode of your Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.).