abiyek abiyek - 3 months ago 16
SQL Question

Expected lexical element not found

Every time I used 'Count()' to count the duplicate PointIDs' on the query I get this error.

I have narrowed down the problem is with Count() function, used MAX() with Group by and didn't have any problem. This is on access database populated using ODBC connection. All the help is appriciated - I have done all the research and this is my last online resort.

SELECT Event1.PointID, Event1.LogTimeStamp, Count(Event1.PointID) AS acount
FROM Event1
GROUP BY Event1.PointID, Event1.LogTimeStamp;

Answer

I suspect you actually want to return all duplicate records which is a 2 step operation.

Step 1 get find the ids which are duplicated

   SELECT Event1.PointID, Count(*) AS NumOfRecords
   FROM Event1
   GROUP BY Event1.PointID
   HAVING COUNT(*) > 1

Step 2 join that result back to the original table to find the records

SELECT e.*, d.NumOfRecords
FROM
    Event1 e
    INNER JOIN (
       SELECT Event1.PointID, Count(*) AS NumOfRecords
       FROM Event1
       GROUP BY Event1.PointID
       HAVING COUNT(*) > 1
    ) d
    ON e.PointId = d.PointId