Table Alias - input table missing

  • Thread starter Thread starter Casa
  • Start date Start date
C

Casa

Hello everyone;

Here is a simple problem. I want to find duplicates in a particular column
of a query (this query is joining together two tables). So I've designed a
duplicates query to do the task. I want to create a table alias (for the
first query, since it's name is long). I write the code to do this, and when
I run the duplicates query, Access says that it cannot find the input query.
I'm certain that I've messed up something simple. Can you tell me what it
is? Here is my code:

SELECT A.Certificate, A.Certificate2, A.DDH
FROM qry_Select_Assays_for_Excel as A
WHERE (((A.Certificate) In (SELECT [Certificate]
FROM [A] As Tmp GROUP BY [Certificate] HAVING Count(*)>1)))
ORDER BY A.Certificate;
 
SELECT A.Certificate, A.Certificate2, A.DDH
FROM qry_Select_Assays_for_Excel as A
WHERE (((A.Certificate) In (SELECT [Certificate]
FROM [A] As Tmp GROUP BY [Certificate] HAVING Count(*)>1)))
ORDER BY A.Certificate;
Click to expand...
 
You cannot read [A] in the subselect. you must specify the table name. e.g.
SELECT A.Certificate, A.Certificate2, A.DDH
FROM qry_Select_Assays_for_Excel as A
WHERE (((A.Certificate) In (SELECT Tmp.Certificate
FROM qry_Select_Assays_for_Excel As Tmp GROUP BY Tmp.Certificate HAVING Count(*)>1)))
ORDER BY Tmp.Certificate;
Click to expand...

Also you dont need [ ] around table or column names unless they contain a
special character or an embedded space.

-Dorian
SELECT A.Certificate, A.Certificate2, A.DDH
FROM qry_Select_Assays_for_Excel as A
WHERE (((A.Certificate) In (SELECT [Certificate]
FROM [A] As Tmp GROUP BY [Certificate] HAVING Count(*)>1)))
ORDER BY A.Certificate;
Click to expand...
 
Casa said:
Here is a simple problem. I want to find duplicates in a particular column
of a query (this query is joining together two tables). So I've designed a
duplicates query to do the task. I want to create a table alias (for the
first query, since it's name is long). I write the code to do this, and when
I run the duplicates query, Access says that it cannot find the input query.
I'm certain that I've messed up something simple. Can you tell me what it
is? Here is my code:

SELECT A.Certificate, A.Certificate2, A.DDH
FROM qry_Select_Assays_for_Excel as A
WHERE (((A.Certificate) In (SELECT [Certificate]
FROM [A] As Tmp GROUP BY [Certificate] HAVING Count(*)>1)))
ORDER BY A.Certificate;
Click to expand...


Try something more like:

(SELECT Tmp.Certificate
FROM qry_Select_Assays_for_Excel As Tmp
GROUP BY Tmp.Certificate
HAVING Count(*)>1)))
 
Back
Top