Unique / distinct with duplicates in non-primary key fields

[I love SQL... :S]

Hi everyone,

I'm new here, so forgive me if something like this has been posted many
times.

I have some massive tables, one of which has duplicates in various fields.
As such, I had to import the date into Access without any primary key, but
it's imperative that I of course only have unique records based on ONE field
which I'll call Fld1 below. So here is an example of my problem.

Fld1(PK) Fld2 Fld3
001 ABC ABC
001 AAA ABC

The differences are merely typos, so selecting any one of the duplicate rows
would be fine. So any ideas how to do this? I've tried just taking the
unique Fld1 via SELECT DISTINCT Fld1 FROM THETABLE but then of course trying
to link it back, I still end up with every single record from "THETABLE"!!!
I hope this makes sense somehow and someone can please help me ASAP! There
must be something simple and obvious I've forgotten or don't know? :'(

Thanks very much,
Rachel

 

[I love SQL... :S]

Sorry, I didn't make it clear that I need to get each field from this table,
so if I just go DISTINCT, then every row really is distinct due to the typos
in various fields. So for a clearer example again...

Fld1 Fld2 Fld3
Smith Fred 53
Smith Frdd 53

So I would just like one Smith, but it doesn't matter too much about which
one (the data / information will be changed manually later).

Thanks again in advance!

 

[I love SQL... :S]

Nevermind..not that anyone was. :'( All I did was create a duplicate table
(structure without the data) making Fld1 the only primary key and then I
created an append query. And - IT WORKED!

There are so many records to go through and check though (total is over
15,000) and not much info in the Help file that I could find, that I'm not
sure how Access selects which record to choose as the unique one. If anyone
knows, please post.

 

[Marshall Barton]

Well, I was trying to figure something for this kind of
situation, but didn't come up with a good way.

What you've done is all well and good if the field you
designated as the primary key is sufficient to uniquely
identify an individual. However, your example might blow
away some potentially important records such as:

Fld1 Fld2 Fld3
Smith Fred 53
Smith Fred 54
Smith George 77

Depending on the semi-random retrieval order, any two of
those records will not appear in the result. You may need
to expand the primary key to include Fld2 so you don't lose
track of George (or Fred?).

If you require the records to retrieved in a specific order,
instead of a semi-random order, then create a query that
sorts the data. Then use that query instead of the table in
the append query.

Bottom line is that it is very important that you carefully
select the fields to use in the primary key index.