Troubleshoot queries

All queries

I'm getting an unexpected message.

I'm getting an SQL syntax message.

You may have typed incorrect syntax in SQL view.
You created your query under a different ANSI SQL query mode than the current mode of your Microsoft Access database.
Why you should avoid mixing queries created under different ANSI SQL query modes
The two ANSI SQL query modes, ANSI-89 and ANSI-92, are not compatible. When you create a Microsoft Access database, you need to decide which query mode you are going to use, because mixing queries 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 file format to 2000 or 97 file format. ANSI-92 SQL query mode is only supported in the 2002 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. In Access 2002, 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 protecting 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.

I'm getting the message "Can't bind name <name>."

You may encounter this message if you didn't explicitly declare in the Query Parameters dialog box the parameters used for a crosstab query or a query that a crosstab query or chart is based on. To resolve this, do the following:

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

How?

On the Query menu, click Parameters.
In the first Parameter cell, type the first prompt you entered in the query design grid.

In the Data Type cell to the right, click the appropriate data type 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 that do recognize this data type (Microsoft Access does not recognize this data type).

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

In the same query, set the ColumnHeadings property.
How?
You can change the order or limit the column headings in a crosstab query. 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.
2. Click the background of query Design view, outside the design grid and the field lists.
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, 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 field to store the column headings. Then open the table and copy the headings into the ColumnHeadings property box as needed.

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 that do recognize this data type (Microsoft Access does not recognize this data type).

I'm getting a key violation message.

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 for related tables.
Your query attempts to append or update records that contain primary key 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.

I'm getting a validation rule violation message.

A validation rule violation occurs if you attempt to update or append records that violate the validation rules for a field or record.

Restrict or validate data

A field validation rule is used to check the value entered into a field as the user leaves the field. A record validation rule controls when an entire record can be saved. Unlike field validation rules, record validation rules can refer to other fields.

You can also determine whether data that already existed before you specified a validation rule or changed the Required or AllowZeroLength property violates the current settings.

Open a table in Design view.
Do one of the following:
Define a validation rule to control what values can be entered into a field
1. In the upper portion of the window, click the field you want to define a validation rule for.
2. In the lower portion of the window, click the ValidationRule property box, and then type the validation rule, or click the Build button to create the validation rule using the Expression Builder.
  For example, you could define the validation expression ">9" for a Quantity field to prevent a user from placing an order for fewer than 10 units.
3. In the ValidationText property box, type the message that you want displayed when the rule is broken.
  For example, for the validation expression ">9", you could enter "You must order 10 or more units."
4. If you set a validation rule in a field that contains data, Microsoft Access will ask if you want to apply the new rule to existing data when you save the table. If you click Yes, Access will warn you when existing data violates the validation rule.
Define a validation rule to control when a record can be saved
1. Click Properties on the toolbar to display the table's property sheet.
2. In the ValidationRule property box, type the validation rule. Or click the Build button to create the validation rule using the Expression Builder.
  For example, you could define the validation expression "[RequiredDate]<=[OrderDate]+30" to make sure that the date entered into the RequiredDate field is within 30 days of the date in the OrderDate field.
3. In the ValidationText property box, type the message that you want Microsoft Access to display when the rule is broken.
  For example, for the validation expression "[RequiredDate]<=[OrderDate]+30", you could enter "The required date must be within 30 days of the order date."
4. If you set a validation rule in a table that contains data, Microsoft Access will ask if you want to apply the new rule to existing data when you save the table. If you click Yes, Microsoft Access will warn you when existing data violates the validation rule.
Test data that already existed before you specified a validation rule
- Right click the title bar of the table, and click Test Validation Rules.
  Microsoft Access will warn you if existing data violates a validation rule or the Required or AllowZeroLength settings.

I'm getting the message "Query contains ambiguous outer joins."

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. There are three types of joins in Microsoft Access: inner joins (also called equi-joins), left outer joins, 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.

Example of a multiple join using three tables

For example, if you have three tables, Consultants, Active Consultants, and Projects, and you create an SQL query by joining these tables in the following way:

Consultants LEFT OUTER JOIN Active Consultants INNER JOIN Projects

Microsoft Access can process this query in two ways:

(Consultants LEFT OUTER JOIN Active Consultants) INNER JOIN Projects

Access first creates a left outer join between the Consultants and Active Consultants tables, and then creates an inner join between the results of those two tables and the Projects table.

Consultants LEFT OUTER JOIN (Active Consultants INNER JOIN Projects)

Access creates a left outer join between the Consultants table and the results of the inner join between the Active Consultants and Projects tables

Because Microsoft Access can produce different results between these two ways of joining, you must specify which way to join by changing one of the joins or by separating the query into two queries.

Multiple joins that aren't ambiguous

The following combinations of joins can only produce one result and will not generate an "ambiguous outer joins" message:

Consultants INNER JOIN Active Consultants INNER JOIN Projects

Consultants INNER JOIN Active Consultants LEFT OUTER JOIN Projects

Consultants LEFT OUTER JOIN Active Consultants LEFT OUTER JOIN Projects

Consultants RIGHT OUTER JOIN Active Consultants INNER JOIN Projects

Consultants RIGHT OUTER JOIN Active Consultants LEFT OUTER JOIN Projects

Consultants RIGHT OUTER JOIN Active Consultants RIGHT OUTER JOIN Projects

Multiple joins that are ambiguous

The following combinations of joins can produce more than one result and will generate an "ambiguous outer joins" message:

Consultants INNER JOIN Active Consultants RIGHT OUTER JOIN Projects

Consultants LEFT OUTER JOIN Active Consultants INNER JOIN Projects

Consultants LEFT OUTER JOIN Active Consultants RIGHT OUTER JOIN Projects

I'm getting a type mismatch message.

Verify that the criteria you specified is for the same data type 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 and the criteria uses the values that display on the lookup list instead of their associated foreign key 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 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.

I'm getting the message "Too few parameters expected."

You may encounter this message if a parameter query exported to the IDC file type doesn't have data types specified in the Query Parameters dialog box.

In the Database window, click Queries under Objects.
Click the name of the parameter query you want to modify, and click Design on the Database window toolbar.

Specify a data type for the parameter.

How?

On the Query menu, click Parameters.
In the first Parameter cell, type the first prompt you entered in the query design grid.

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

Save and run the query.

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 that do recognize this data type (Microsoft Access does not recognize this data type).

I'm getting an error message about s_Generation in my append query.You can get this message in a replicated database when you run an append query that contains the asterisk (*) in the Field row in the design grid. The asterisk selects all the fields from one of the underlying tables. To avoid this problem, remove the asterisk from the design grid, and then add each field from the table's field list to the design grid by double-clicking the title bar of the field list.

I'm getting the message "Operation must use an updatable 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 (total) was calculated for the field (using either the Totals row or a domain function 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.

In query Design view for the current query, remove the field you added from the crosstab or select query (where an aggregate was calculated for the field).
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 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])
```
To run the query, click Run on the toolbar.
To see the results, open the table by clicking Tables under Objects in the Database window and clicking Open on the Database window toolbar.

The wildcard character in my query is behaving unexpectedly.

The two ANSI SQL query modes, ANSI-89 and ANSI-92, are not compatible and use different wildcard characters. 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.

When you create a Microsoft Access database, you need to decide which query mode you are going to use because if you create a later query under a different ANSI SQL query mode than the current mode of your Access database, your query could produce runtime errors or unexpected results.

About using wildcard characters to search for partial or matching values

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, you can use the following wildcard characters in queries and expressions 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.

Character	Description	Example
*	Matches any number of characters. It can be used as the first or last character in the character string.	wh* finds what, white, and why
?	Matches any single alphabetic character.	B?ll finds ball, bell, and bill
[ ]	Matches any single character within the brackets.	B[ae]ll finds ball and bell but not bill
!	Matches any character not in the brackets.	b[!ae]ll finds bill and bull but not ball or bell
-	Matches any one of a range of characters. You must specify the range in ascending order (A to Z, not Z to A).	b[a-c]d finds bad, bbd, and bcd
#	Matches any single numeric character.	1#3 finds 103, 113, 123

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.

Character	Description	Example
%	Matches any number of characters. It can be used as the first or last character in the character string.	wh% finds what, white, and why
_	Matches any single alphabetic character.	B_ll finds ball, bell, and bill
[ ]	Matches any single character within the brackets.	B[ae]ll finds ball and bell but not bill
^	Matches any character not in the brackets.	b[^ae]ll finds bill and bull but not ball or bell
-	Matches any one of a range of characters. You must specify the range in ascending order (A to Z, not Z to A).	b[a-c]d finds bad, bbd, and bcd

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. You must enclose the opening and closing brackets in brackets ([[ ]]).

The same underlying query behaves differently in a data access page and form.

The query underlying a form runs under the current ANSI SQL query mode setting of the Microsoft Access database. On the other hand, the query underlying a data access page always runs under ANSI-92 SQL query mode because a page connects to your Access database by using ADO and the Microsoft Jet OLE DB provider, both of which can only use ANSI-92 SQL. Therefore, if the SQL query mode setting of your Access database is ANSI-89, the query underlying the form runs as an ANSI-89 query, but the query underlying a page always runs under ANSI-92 regardless of the Access database setting. To resolve this, use a different query for the form and, if possible, rewrite the query for the form to produce the same results as the underlying query for the page.

I'm not getting the records I want.

There are too many records.

If tables or queries in a query aren't joined 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 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 or in criteria expressions 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, append, or make-table query, if you're using Top Values on the toolbar in query Design view (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.

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

There are too few records.

If the query includes more than one table or query, check the 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 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, 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 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 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.

The records are wrong.

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.
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 are the correct ones.

The query is running under a different ANSI SQL query mode.

You created your query under a different ANSI SQL query mode than the current mode of your Microsoft Access database.

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

The two ANSI SQL query modes, ANSI-89 and ANSI-92, are not compatible. When you create a Microsoft Access database, you need to decide which query mode you are going to use, because mixing queries 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/region 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 file format to 2000 or 97 file format. ANSI-92 SQL query mode is only supported in the 2002 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. In Access 2002, 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 protecting 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.

I'm not getting the columns I want.

There are too many columns.

Remove the fields in the query design 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, 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.

There are too few columns.

Add more fields to the query design 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, 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.

The columns are wrong.

In the query design 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.

The query is running under a different ANSI SQL query mode.

You created your query under a different ANSI SQL query mode than the current mode of your Microsoft Access database.

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

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

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

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 file format to 2000 or 97 file format. ANSI-92 SQL query mode is only supported in the 2002 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. In Access 2002, 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 protecting 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.

I'm not getting the calculation results I expected.

The results of a totals (aggregate) calculation are wrong.

If there are records with 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 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, 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 you selected is the correct one. If not, select a different aggregate from the list in the Total cell.

The results of a totals (aggregate) calculation are in the wrong column.

In the query design grid, delete the aggregate you specified in the Total cell for that field (column), and then select one in a different field.

The arithmetic calculation in the design grid returns a Null (blank) value.

If you use an arithmetic operator (such as +, -, *, /) in an expression, such as UnitsInStock] + [UnitsOnOrder], and one of the fields in the expression contains a 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.

The query is running under a different ANSI SQL query mode.

You created your query under a different ANSI SQL query mode than the current mode of your Microsoft Access database.

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

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

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

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 file format to 2000 or 97 file format. ANSI-92 SQL query mode is only supported in the 2002 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. In Access 2002, 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 protecting 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.

Specific types of queries

Select queries

My AutoLookup query isn't filling in 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. (Referential integrity doesn't have to be enforced.)
The join field on the "one" side of the relationship must have a unique index. A unique index means that the field is a primary key or its Indexed property in table Design view is set to Yes (No Duplicates).
The join field you add to the design grid must come from the table on the "many" side of the one-to-many relationship. (In other words, it's the foreign key 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.

Microsoft Access automatically joins tables or queries I don't want joined.

Even if you haven't created relationships 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 and if one of the join fields is a primary key. You can enable or disable these automatic joins.

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

Note This setting applies to new queries only.

I can't update data from a query.

In some cases, you can edit data in query Datasheet view 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.

Data is updatable

You can update a query or query field in the following cases:

A query based on one table
A query based on tables with a one-to-one relationship
The query's results contain a Memo, Hyperlink, or OLE Object

Data is updatable under certain conditions

If a query is based on tables with a one-to-many relationship, you might not be able to edit the data for the following query fields.

Query field	Solution
Join field from the "one" side	Enable cascading updates between the two tables.
New records, if the "many" side join field doesn't appear in the datasheet	Add the join field from the "many" side to your query to allow adding new records.
Join field from the "many" side, after you've updated data on the "one" side	Save the record; then you'll be able to make changes to the "many" side join field.
Blank field from the table on the "one" side of a one-to-many relationship where an outer join exists	Enter values in fields from the table on the "many" side, but only if the joined field from the "one" side contains a value for that record.
New records, if entire unique key of ODBC table isn't output	Select all primary key fields of ODBC tables to allow inserts into them.

Data can be deleted but not updated

Query or query field	Solution
Query (or underlying table) for which Update Data permission isn't granted	To modify data, permissions must be assigned.
Query (or underlying table) for which Delete Data permission isn't granted	To delete data, permissions must be assigned.

Data can't be updated

Query or query field	Solution
Query based on three or more tables in which there is a many-to-one-to-many relationship	Though you can't update the data in the query directly, you can update the data in a form or data access page based on the query if the form's RecordsetType property is set to Dynaset (Inconsistent Updates).
Crosstab query	None
SQL pass-through query	None
Query that calculates a sum, average, count or other type of total on the values in a field, or an update query that references a field in the Update To row from either a crosstab query, select query, or subquery that contains totals or aggregate functions	By using a domain aggregate function in the Update To row of an update query, you can reference fields from either a crosstab query, select query, or subquery that contains totals or aggregate functions.
Union query	None
Query whose UniqueValues property is set to Yes	None
Query that includes a linked ODBC table with no unique index, or a Paradox table without a primary key	None
Query that includes more than one table or query, and the tables or queries aren't joined by a join line in Design view	You must join the tables properly in order to update them.
Calculated field	None
Field is read-only; the database was opened as read-only or is located on a read-only drive	None
Field in record that has been deleted or locked by another user	A locked record should be updatable as soon as it is unlocked.

I get prompted for a parameter when I don't expect it.

If you get prompted to type parameters when you attempt to run a query, report, or data access page, and you don't expect this prompt, one of the following might apply:

You deleted a parameter from the query design 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. 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.

The query is running under a different ANSI SQL query mode.

You created your query under a different ANSI SQL query mode than the current mode of your Microsoft Access database.

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

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

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

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 file format to 2000 or 97 file format. ANSI-92 SQL query mode is only supported in the 2002 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. In Access 2002, 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 protecting 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.

Parameter queries

I get prompted for a parameter when I don't expect it.

If you get prompted to type parameters when you attempt to run a query, report, or data access page, and you don't expect this prompt, one of the following might apply:

You deleted a parameter from the query design 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. 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.

I want the option of returning all records with a parameter query.

In the design 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
Run the query.
Leave the Enter Parameter Value dialog box blank, when Microsoft Access prompts you.

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

The query is running under a different ANSI SQL query mode.

You created your query under a different ANSI SQL query mode than the current mode of your Microsoft Access database.

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

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

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

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 file format to 2000 or 97 file format. ANSI-92 SQL query mode is only supported in the 2002 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. In Access 2002, 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 protecting 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.

Crosstab queries

The column headings aren't in the order I want, or I want to exclude some of them.

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

I can't update the data in my crosstab query.

Data in a crosstab query is not updatable.

One or more column headings in the query's results are labeled "<>".

Microsoft Access returns "<>" as the column name for any NullNull 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. 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.
2. Click the background of query Design view, outside the design grid and the field lists.
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, 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 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, 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 that will convert Null values to a more meaningful value, such as "Unknown."

The wrong field values are showing as row or column headings.

Verify that the field whose values you want to use as column headings is in the query design 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.

Sort or limit column headings displayed in a crosstab query

You can change the order or limit the column headings in a crosstab query. 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.

Open the crosstab query in Design view.
Click the background of query Design view, outside the design grid and the field lists.
On the toolbar, click Properties to display the query's property sheet.
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.)
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, 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 field to store the column headings. Then open the table and copy the headings into the ColumnHeadings property box as needed.

The query is running under a different ANSI SQL query mode.

You created your query under a different ANSI SQL query mode than the current mode of your Microsoft Access database.

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

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

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

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 file format to 2000 or 97 file format. ANSI-92 SQL query mode is only supported in the 2002 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. In Access 2002, 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 protecting 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.

Action queries (append, update, delete)

The append query appended data to or from the wrong fields.

Verify that you have selected the correct fields for the data you want to append, entered the correct criteria in the query design 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:

Select the fields whose data you want to append

Add the following fields to the query design grid for your append query:

The fields you want to append.
The fields you want to specify criteria for.
The field corresponding to the primary key field of the table you're appending records to.

Select the fields to which you want to append data

In the Append To cell of the fields whose data you want to append, click the name of the field you want to add the data to.

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.

The update query didn't perform the update I wanted.

You need to specify a different update by changing the expression 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.

I don't know the best way to delete duplicate records after using the Find Duplicates Wizard.

Delete the duplicate record or the original directly in query Datasheet view.

Delete a record

Delete a record in a datasheet or form

Open a datasheet, or open a form in Form view.
Click the record you want to delete.
Click Delete Record on the toolbar.

Note When you delete data, you might want to delete related data in other tables. For example, if you delete a supplier, you probably want to delete the products that the supplier supplies. In some cases, you can make sure the proper data is deleted by enforcing referential integrity and turning on cascade deletions.

Delete a record in a data access page

Important Once you delete a record, you cannot undo the deletion from the database.

On the record navigation toolbar, click Delete .

Note If you don't see the Delete button, it means one of the following:

The page does not support deletions.
The designer of the page has customized the button. Look for custom instructions on how to use the page, or contact the designer.

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.

Delete duplicate records from a table

Deleting duplicate records from a table requires two steps. First, you create a copy of the structure of the table that contains duplicates, and then make primary keys of all the fields that contain duplicates. Second, you create and run an append query from the original table to the new table. Because fields that are primary keys can't contain duplicate records, this procedure produces a table without duplicate records.

Copy the table and make primary keys of fields with duplicates.
How?
1. In the Database window, click Tables under Objects.
2. Click the name of the table you want to delete duplicate records from.
3. Click Copy on the toolbar.
4. Click Paste on the toolbar.
5. In the Paste Table As dialog box, type a name for the copied table, click Structure Only, and then click OK.
6. Open the new table in Design view, and select the fields that contained duplicates in the table you copied.
7. Click Primary Key on the toolbar to create a primary key based on the selected fields.
8. Save and close the table.
Append only unique records to the new table.
How?
1. Create a new query based on the original table that contains duplicates.
2. In query Design view, click Query Type on the toolbar, and then click Append Query.
3. In the Append dialog box, click the name of the new table in the Table Name list, and then click OK.
4. Include all the fields from the original table by dragging the asterisk (*) to the query design grid.
5. Click Run on the toolbar.
6. Click Yes when you receive the message that you're about to append rows.
7. Click Yes when you receive the message that Microsoft Access can't append all the records in the append query. This transfers only unique records to your new table and discards the duplicates.
8. Open the table to see the results.
9. When you're sure the new table has the correct unique records, you can delete the original table, and then rename the new table using the name of the original table.

The delete query deleted records from the wrong table.

Make sure the delete query includes:

The table you want to delete records from. Drag the asterisk (*) from the field list for that table to the query design grid.
The field(s) you want to specify criteria 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.

The query is running under a different ANSI SQL query mode.

You created your query under a different ANSI SQL query mode than the current mode of your Microsoft Access database.

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

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

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

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 file format to 2000 or 97 file format. ANSI-92 SQL query mode is only supported in the 2002 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. In Access 2002, 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 protecting 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.