Remove duplicates, leaving only first

[themedman]

I deal with patient data and I have lots of duplicate names due to multiple visits. I want to see unique names or ssn. I can use the standard ms query for duplicates but then I may have 9000 records to sort thru deleting all but first entry. Is there a way in a query to not only find duplicates but also delete say all but first occurence? Im pretty new to access so sorry if this is easy for most of you b/c a lot the questions seem a lot harder in this group.

thanks!

 

[themedman]

I found my answer in previous thread, sorry for post.
thread701-686164

 

[eselhassan]

I have the same question but a little bit different. I'm doing an insert into a SQL table from AS400 using openquery.

I have a field f1 that must be unique - no duplicates - in my SQL table t1. I would like to group the records and keep the first one of each group and delete the rest.

My field f1 is varchar type.
If I write my query this way - see below - I get everything including the duplicates:

insert into t1(f1)
select * from openquery(myAS400, 'select f400 from Lib1/File1')

Any help is greatly appreciated.

 

[eselhassan]

I solved my problem as well. Group by did for me.


insert into t1(f1)
select * from openquery(myAS400, 'select f400 from Lib1/File1')
group by f400