delete duplicates

[prasadgov]

Hi,

Is there a way to delete the second occurrence from a table entry where there are duplicates sessions?

For example, I need to delete the second occurrence of CDS4 since a Registrant cannot attend two courses in the same session.
I need a query which deletes the second occurrence of all the sessions where a Registrant has entered two courses.

TIA

 

[ebs17]

How can I delete Duplicate Records from a table

Hello Sir/Madam Please help me to deleting duplicate records records from a Table I tried but it deletes all the recordes with the key, i am trying.

www.access-programmers.co.uk

 

[prasadgov]

in Access query, I am not sure if "Exists" can be used

 

[theDBguy]

in Access query, I am not sure if "Exists" can be used

One way to find out is to give it a try.

 

[ebs17]

in Access query, I am not sure if "Exists" can be used

I'm very sure of that.

 

[plog]

And how did you become very sure of that? Did you post on forums and get an answer and wash your hands of it? Or did you confirm it for yourself as Dbguy suggested?

You missed the bigger picture.

 

[ebs17]

Who is the question for?
EXISTS is part of the ANSI89 language set that Jet-SQL (=Access-SQL) uses. So you can use it, and I haven't seen a case where it didn't work if you use it correctly, i.e. in conjunction with a correlated subquery.
I have already written a lot of queries so that the one linked above could be written from the abstraction without having to test it separately.

I also find this design very clear and easy to understand and prefer it to other variants with a JOIN (which also exist).

 

[ebs17]

delete the second occurrence

A query is mass data processing - all at once. Therefore, you immediately need a secure and reliable attribute for a first or second occurrence in order to be able to delete accurately. I don't trust myself to derive a reliable rule from a random(?) example.