Update (not delete) all duplicate records except first one

[Eprice]

Hi,
I have searched and cannot find help on how to update duplicates but not delete them or change one of the duplicates. In the duplicate records I have a field "Prem" that I want to blank out except on one record, it doesn't matter which one. Some of the companies might only have one record but there may be seven records for another company. I'm sure I would use an update query but have no idea how to write it. Any ideas??
Lisa

 

[PHV]

What is the PrimaryKey ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eprice]

There is no primary key set in the table but they all have the same risk number. The reason behind this is the data is exported to excel where someone else in our company will add all the premiums. If a company is listed in the database three times because of three different locations (which they also need to see) the premium will show three times and when added would be 9000 instead of 3000. Make sense?
Lisa

 

[PHV]

So, I rephrase:
which combination of fields will uniquely identify an occurrence ?
Or:
how do you distinguish 2 duplicate records ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eprice]

Sorry, I misunderstood. The identical fields would be RISK_NO and INS_NAME.
Lisa

 

[PHV]

How do you distinguish one record from another having SAME RISK_NO and SAME INS_NAME ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eprice]

They have different locations and contact information.