need help with access query - removing duplicated rows

Question

hi

i have this table

A-----B-----C

123 ----3--------5

123----44-------7

123----6--------8

how to delete duplicated rows - but leave only the firs row ?

i'll get this result

A----B----C

123----3------5

thank's in advance

Answer 1

USe a group by query: group on the 'unique' column and select the first value for the other columns. Delete all rows from the table where the fields are NOT the same as the output of this query.

Answer 2

Based on birger's guidance here is an example

DELETE tblStack_example.*, tblStack_example.B, tblStack_example.C
FROM tblStack_example
WHERE (((tblStack_example.B) Not In (SELECT First(tblStack_example.B) AS FirstOfB
FROM tblStack_example
GROUP BY tblStack_example.A)) AND ((tblStack_example.C) Not In (SELECT First(tblStack_example.C) AS FirstOfC
FROM tblStack_example
GROUP BY tblStack_example.A)));

Hope this helps

Answer 3

Use Distinct keyword

e.g.

SELECT DISTINCT column1,column2,column3 from Table1;