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

Asked

Viewed 25 times

0

Good afternoon, everyone.

I’m trying to make an example SQL code that I found on the Internet to find duplicates of my Access table and delete only keeping the first record. However, when changing all fields where it said (Field1, Field2, ...) by the names of the columns themselves, it started to give syntax error. Before when the "Field" values were, it ran and opened a prompt requesting the name of each column. Follow 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);
ms-access

1 answer

0

@Rodrigo BRF The names of the fields that have spaces or hyphen should be placed between brackets.

Example: F.[Criado em]

Browser other questions tagged ms-access

You are not signed in. Login or sign up in order to post.