CODEWITHSUNDEEP
sqlms-accesssubqueryms-access-2010
Chih-Hsuan Yen
Chih-Hsuan Yen

Reputation: 844

Table not found in subquery using Microsoft Access 2010

I'm trying to use subqueries in Microsoft Access 2010. Here is the table "Table1":

ID  data
--------
1   abc
2   def

There are two possible usage of subqueries: 

SELECT ID
FROM (SELECT ID FROM Table1) AS tmp 
WHERE tmp.ID > 1

It works, and gives the correct result ID = 2. However, 

SELECT ID
FROM (SELECT ID FROM Table1) AS tmp 
WHERE EXIST (SELECT ID FROM tmp WHERE ID > 1)

Gives the following error:

The Microsoft Access database engine cannot find the input table or query 'tmp'. Make sure it exists and that its name is spelled correctly.

It's not a good example of using subqueries, but I need to reference the results of a subquery in another subquery in more complicated cases, like: 

SELECT Temp.rating
FROM (
    SELECT S.rating, AVG(S.age) AS avgage
    FROM Sailors S
    GROUP BY S.rating) AS Temp
WHERE  Temp.avgage = (
    SELECT MIN(Temp.avgage) 
    FROM Temp)

What is the cause of such errors and how to solve it?

Upvotes: 1

Views: 498

Answers (1)

crthompson
crthompson

Reputation: 15865

There are much simpler ways to do what you are suggesting.

For example your second query can be refactored like this:

Select
  top 1 s.rating, avg(s.age) as avgage
from
  sailors s
group by s.rating
order by avg(s.age)

This gives you the minimum average age of the sailors grouped by their rating.

If you must use a subselect try:

SELECT rating, min(avgage)
FROM (
    SELECT S.rating, AVG(S.age) AS avgage
    FROM Sailors S
    GROUP BY S.rating) AS Temp

In the first section of your answer you give this query:

SELECT ID
FROM (SELECT ID FROM Table1) AS tmp 
WHERE EXIST (SELECT ID FROM tmp WHERE ID > 1)

This throws a syntax error because the tmp is out of scope in the exists.

I think you mean to do something like this, though I'm not sure it makes sense:

SELECT ID
FROM Table1 AS tmp 
WHERE EXIST (SELECT 
               1 
             FROM 
               Table1 tmp2 
              WHERE tmp.Id = tmp2.ID 
              and tmp2.Id > 1)

Notice that the self join between the two tables (tmp and tmp2) happens in the exists.

Upvotes: 1

Related Questions