ACC: Query with Subquery Returns Incorrect Result
ID: Q171948
|
The information in this article applies to:
-
Microsoft Access versions 2.0, 7.0, 97
SYMPTOMS
Moderate: Requires basic macro, coding, and interoperability skills.
A query that contains a subquery that acts as criteria returns incorrect
results.
CAUSE
The query is based on an ODBC data source, and the ODBC driver incorrectly
interprets the data type of the subquery result as text.
WORKAROUND
There are two workarounds for this behavior:
- Use a SQL pass-through query.
- Try using a different SQL statement that would return the same
result. For example, change the SQL statement
SELECT DISTINCT a1.au_id FROM dbo_authors AS a1
WHERE 1 < (SELECT COUNT(*) FROM dbo_authors AS a2
WHERE a2.city = a1.city);
to the following:
SELECT DISTINCT a1.au_id FROM dbo_authors AS a1
WHERE a1.city In (SELECT a2.city FROM dbo_authors AS a2
GROUP BY a2.city HAVING (Count(a2.au_id)>1));
STATUS
Microsoft has confirmed this to be a problem in Microsoft Access versions
2.0, 7.0 and 97.
MORE INFORMATIONSteps to Reproduce Problem
- Open the sample database Northwind.mdb (or NWIND.MDB in Microsoft
Access version 2.0).
- On the File menu, point to Get External Data, and then click Link
Tables.
If you are using Microsoft Access version 2.0, click Attach Table on
the File menu.
- In the Files Of Type list, click ODBC Databases() and select the SQL
Server Data Source; provide the SQL Server Login information if
necessary, and then click OK.
If you are using version 2.0, select SQL Server in the Attach dialog
box; click the SQL data source, provide the SQL Server Login
information if necessary, and then click OK.
- Click Options and select (or type) Pubs in the Database box, and click
OK.
- Select the Authors table and click OK.
If you are using version 2.0, select the Authors table and click
Attach. When you receive the message that the table has been
successfully attached, click OK.
- While in the Database window, click the Queries tab.
- Click New, and then click OK.
- In the Show Table dialog box, click Close.
- On the View menu, click SQL View (or SQL in Microsoft Access versions
2.0 or 7.0).
- Type the following SQL statement, which should return only those
records where the value in the City field occurs in another record as
well:
SELECT DISTINCT a1.au_id FROM dbo_authors AS a1
WHERE 1 < (SELECT COUNT(*) FROM dbo_authors AS a2
WHERE a2.city = a1.city);
- On the Query menu, click Run. Note that the query incorrectly returns
all of the records.
- If you are using Microsoft Access version 7.0 or later, click SQL
View (or SQL) on the View menu. Enclose the numeral 1 following the
first instance of WHERE in quotation marks so that the SQL statement
appears as follows:
SELECT DISTINCT a1.au_id FROM dbo_authors AS a1
WHERE "1" < (SELECT COUNT(*) FROM dbo_authors AS a2
WHERE a2.city = a1.city);
Then run the query. Note that only those records where the value in
the City field occurs in another record are returned. However, this SQL
statement returns the error "Type mismatch" if it is run in Microsoft
Access version 2.0.
REFERENCES
For more information about subqueries, search the Help Index for
"subqueries" and display the topic "Use a subquery to define a field or
define criteria for a field."
For additional information, please see the following article in the
Microsoft Knowledge Base:
Q114678 How to Create and Use Subqueries
Additional query words:
prb sub-select
Keywords : kbinterop QryProb OdbcSqlms OdbcProb
Version : 2.0 7.0 97
Platform : WINDOWS
Issue type : kbbug
|