MS Access database engine (JET) does not recognize
WITH
and
OVER
commands.
To find duplicates in your table, use something like this:
SELECT [FieldWithDuplicates] AS [Value], COUNT([FieldWithDuplicates]) AS [CountOfDuplicates]
FROM YourTable
GROUP BY [FieldWithDuplicates]
HAVING COUNT([FieldWithDuplicates])>=1
To find non-duplicates, you can use
LAST[
^] or
FIRST[
^] or
DISTINCT[
^] functions too, for example:
SELECT DISTINT [FieldWithDuplicates]
FROM YourTable
To delete duplicates and leave the newest values, use:
DELETE *
FROM YourTable
WHERE [ID] NOT IN (SELECT LAST([ID]) FROM YourTable)