Table Alias - input table missing

[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;

 

[mscertified]

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;

 

[mscertified]

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;

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;

 

[Marshall Barton]

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;

Try something more like:

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