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
GROUP BY Event1.PointID, Event1.LogTimeStamp;
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