Newbie query question

  • Thread starter Thread starter Nigel Brown
  • Start date Start date
N

Nigel Brown

I have a query that identifies duplicate values in a table as follows:

SELECT Fleet.[Project Ref], Count(Fleet.[Project Ref]) AS Dups
FROM Fleet
GROUP BY Fleet.[Project Ref]
HAVING (((Count(Fleet.[Project Ref]))>1));

I would like to be able not to use the Fleet.[Project Ref] results to
create a subset of my table.I have tried the following

SELECT Fleet.*
FROM Fleet AS A INNER JOIN (SELECT Fleet.[Project Ref],
Count(Fleet.[Project Ref]) AS Dups
FROM Fleet
GROUP BY Fleet.[Project Ref]
HAVING ((Count(Fleet.[Project Ref]))>1)) As B
WHERE A.[Project Ref] = B.[Project Ref];

This Is causing a syntax error and highlighting the WHERE part of the
query. WHY?
Regards
Nigel
 
In Access, you cannot have a subquery in the From clause. Instead, you put
it in the Where clause and us the IN operator. Something like this:

SELECT Fleet.* FROM Fleet
WHERE Fleet.[Project Ref] IN (SELECT Fleet.[Project Ref]
FROM Fleet
GROUP BY Fleet.[Project Ref]
HAVING Count(Fleet.[Project Ref])>1)

Untested, but that's the idea.
 
JOIN goes with ON Predicate, NOT WHERE.

Try:

SELECT Fleet.*
FROM Fleet AS A
INNER JOIN
( SELECT Fleet.[Project Ref], Count(Fleet.[Project Ref]) AS Dups
FROM Fleet
GROUP BY Fleet.[Project Ref]
HAVING ((Count(Fleet.[Project Ref]))>1)
) As B
ON A.[Project Ref] = B.[Project Ref];
 
Back
Top