2

I'm working on an access query and kinda hit a dead end. I want to delete all duplicate rows in a table that have the same value in the columns Brand, SerialNr, Seats and LastRepair that have the value "2013" in the year column.

I'm trying to delete all rows that have duplicates in those columns and the year 2013 so there isnt a single one left. (Not just delete the duplicated so there is only one left but delete all instances so there is none left)

The original table looks like this:

Brand SerialNr Seats Color LastRepair Year
Ford 145 4 Blue 01.01.2020 2010
Ford 145 4 Red 01.01.2020 2010
Ford 145 4 Red 01.01.2020 2013
Ford 145 4 Green 01.01.2020 2013
Porsche 146 2 White 01.01.2022 2013
Ferrari 146 2 White 01.01.2022 2013
Volkswagen 147 4 Blue 01.01.2021 2017
Volkswagen 147 4 Red 01.01.2021 2013
Volkswagen 147 4 Orange 01.01.2021 2013

And the outcome table should look like this:

Brand SerialNr Seats Color LastRepair Year
Ford 145 4 Blue 01.01.2020 2010
Ford 145 4 Red 01.01.2020 2010
Porsche 146 2 White 01.01.2022 2013
Ferrari 146 2 White 01.01.2022 2013
Volkswagen 147 4 Blue 01.01.2021 2017

I tried doing it with this question but I need the rows deleted if they have a duplicated value in the those columns so there isnt a single one left who has the same year.

I also tried to do a "find duplicates" query and make an outter join but was unsuccesful so far achieving the desired outcome. I'm thankful for any help.

DELETE Exists (SELECT 1 
   FROM carTable As t2 
   WHERE t1.Brand = t2.Brand AND t1.SerialNr = t2.SerialNr AND t1.Seats = t2.Seats AND t1.LastRepair = t2.LastRepair
   HAVING Count(*) > 1
), t1.[FilNr], *
FROM carTable AS t1, carTable
WHERE (((Exists (SELECT 1 
   FROM carTable As t2 
   WHERE t1.Brand = t2.Brand AND t1.SerialNr = t2.SerialNr AND t1.Seats = t2.Seats AND t1.LastRepair = t2.LastRepair
   HAVING Count(*) > 1
))<>False) AND ((t1.[year])=2013));
Lily of the Valley
  • 23
  • 3

2 Answers2

1

You can use an EXISTS subquery to identify duplicated rows and delete them.

In the subquery, we just select based on the columns you want to identify duplicates by, then check if the count is greater than 1 (since Count is an aggregate, it's in the HAVING clause).

DELETE * FROM t AS t1
WHERE EXISTS(
   SELECT 1 
   FROM t As t2 
   WHERE t1.Brand = t2.Brand AND t1.SerialNr = t2.SerialNr AND t1.Seats = t2.Seats AND t1.LastRepair = t2.LastRepair 
   HAVING Count(*) > 1
) 
AND Year = 2013
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Perfetto, thank you so much! But do you know why I can run this query only once? After 1 time access converts the sql code to something else and says Aggregate Type is a non valid name. Ill update the qustion with the sql code – Lily of the Valley Nov 11 '22 at 13:33
  • You can run the query as much times as you want. Just never open it in design view since Access is stupid and tends to ruin well-written queries when opened in design view. – Erik A Nov 11 '22 at 13:40
  • copy that, in that case thank you again so much. I upvoted your answer but i need 15 points for it to go through – Lily of the Valley Nov 11 '22 at 13:42
0

If your goal is to never have duplicate information in the "Brand" column, that can be accomplished in the table design itself. It's much more efficient to setup the table such that it limits what the user can input in certain circumstances. There's a couple ways you can do this. You can set the primary key to the Brand column, or change the "Indexed" property of that column to "Yes (No Duplicates)" If you are using an auto-number as the ID field and plan on relating a table by that ID, then the index is your best bet.

Access Table Design Example

June7
  • 19,874
  • 8
  • 24
  • 34
Shawn
  • 3
  • 4
  • Thank you for your asnwer Shawn but there can be multiple rows with the same brand. It really just needs to be deleted if the columns Brand AND SerialNr AND Seats AND LastRepair are the same AND the year is 2013. I updated my sample data for clarification – Lily of the Valley Nov 11 '22 at 10:02