Delete Rows

[Guest]

Hi I got a table with 4 fields.There are some duplicate rows.I need to keep
one row and delete the rest.

Table is like this

feild1 field2 field3 fieLd4
A B 1 NULL
A B 1 a
C D 1 b
C D 1 b
E F 2 NULL
E F 2 NULL

I need to delete rows based on value in field 4.

From the 1 and 2 rows above,I need to keep row,*where the field4 is
populated for one row.

From the 3 and 4 rows above,I need to keep one row and delete the
other,*where the filed4 is populated for both rows.

From the 5 and 6 rows above,I need to keep one row and delete the
other,*where the filed4 is not populated for both rows.


Can anyone help me in this.

Thanks,

 

[Guest]

I'm a little confused. Do you want the finished product to look like this?

feild1 field2 field3 fieLd4
A B 1 a
C D 1 b
E F 2 NULL

Are there any more fields in the table?

Could there be more than duplicate rows?

Also the actual table and field names would help us to help you.

 

[Guest]

Yes there are more fields.Its ok if other fields which i didnt mention have
duplicate values.

The finished product sould like what u said.

studentID StudentName Course Semester

19258844 Ramesh Arts 1
19258844 Ramesh Arts 1

19264528 Askar Science 1
19264528 Askar Science

19567823 Julie Computers
19567823 Julie Computers

The value in semester filed is populated in some rows and not in others.I
have huge number of rows.I need to keep one row and delete the other
duplicate rows based on the value in the semester field.I have mentioned the
three ways in which semester field will be populated.

 

[Michel Walsh]

SELECT studentID, studentName, course, MAX(semester)
FROM tableName
GROUP BY studentID, studentName, course



should keep the desired row. Make a permanent table out of it, if required.


Hoping it may help,
Vanderghast, Access MVP