find duplicates in one column, match numbers in another

[jr195]

[FONT=Verdana, Arial, Helvetica]Hi,

I have some data that looks like this:

ID  name                  code

1   American Pie      124
2   American Pie      125
3   Amir's Falafel      129
4   Amir's Falafel      128
5   Amir's Falafel       127
6   Ammos              5509
7   Ammos              5510

For each set of duplicate names, I would like to change the code of each to equal the lowest of the codes already there. So I would like the code for each "American Pie" to 124, "Amir's Falafel" to be 127, "Ammos" to be 5509, etc. (there are 6000 total)

I am using Access 2007 and am fairly new to SQL. If you happen to know that it would be done differently with MySQL, that would be great to know too.

thanks!![/FONT]

 

[Husan]

try adding a new field in yr query using DMIN, somethin like:

Expr1: DMin("Code","table2","Name='" & [name] & "'")

replace table2 with the name of the table with the data. you have problems with Amir's as it has an apstrophe....