Access SQL syntax error to find and delete duplicates leaving only one

0

Good afternoon guys.

I'm trying to make an example SQL code that I found on the Internet to find the duplicates of my Access table and delete keeping only the first record. However, when I changed all fields where I said (Field1, Field2, ...) by the names of the columns themselves, it started giving syntax error. Before when the "Field" values were present, it would run and open a prompt asking for the name of each column. Here is the code:

DELETE *
FROM [Export Excel]
WHERE [Export Excel].ID IN


(SELECT F.ID
FROM [Export Excel] AS F
WHERE Exists (SELECT [Export Excel].[Criado em], [Export Excel].Encerrado, [Export Excel].Número, [Export Excel].Elemento Primário, [Export Excel].Descrição resumida, [Export Excel].Grupo designado, [Export Excel].Atribuído a, [Export Excel].Categoria, [Export Excel].Estado, Count(ID)
FROM [Export Excel]
WHERE [Export Excel].Criado em = F.Criado em
   AND [Export Excel].Encerrado = F.Encerrado
   AND [Export Excel].Número = F.Número
   AND [Export Excel].Elemento Primário = F.Elemento Primário
   AND [Export Excel].Descrição resumida = F.Descrição resumida
   AND [Export Excel].Grupo designado = F.Grupo designado
   AND [Export Excel].Atribuído a = F.Atribuído a
   AND [Export Excel].Categoria = F.Categoria
   AND [Export Excel].Estado = F.Estado
GROUP BY [Export Excel].Criado em, [Export Excel].Encerrado, [Export Excel].Número, [Export Excel].Elemento Primário, [Export Excel].Descrição resumida, [Export Excel].Grupo designado, [Export Excel].Atribuído a, [Export Excel].Categoria, [Export Excel].Estado
HAVING Count([Export Excel].ID) > 1))
AND [Export Excel].ID NOT IN


(SELECT Min(ID)
FROM [Export Excel] AS F
WHERE Exists (SELECT Criado em, Encerrado, Count(ID)
FROM [Export Excel]
WHERE [Export Excel].Criado em = F.Criado em
   AND [Export Excel].Encerrado = F.Encerrado
   AND [Export Excel].Número = F.Número
   AND [Export Excel].Elemento Primário = F.Elemento Primário
   AND [Export Excel].Descrição resumida = F.Descrição resumida
   AND [Export Excel].Grupo designado = F.Grupo designado
   AND [Export Excel].Atribuído a = F.Atribuído a
   AND [Export Excel].Categoria = F.Categoria
   AND [Export Excel].Estado = F.Estado
GROUP BY [Export Excel].Criado em, [Export Excel].Encerrado, [Export Excel].Número, [Export Excel].Elemento Primário, [Export Excel].Descrição resumida, [Export Excel].Grupo designado, [Export Excel].Atribuído a, [Export Excel].Categoria, [Export Excel].Estado
HAVING Count([Export Excel].ID) > 1)
GROUP BY Criado em, Encerrado);
    
asked by anonymous 25.08.2016 / 20:58

1 answer

0

@Rodrigo BRF Field names that have spaces or hyphens should be enclosed in brackets.

Example: F.[Criado em]

    
28.12.2016 / 20:24