Hi, I searched all the mails with this subject, but still do not have a difinate answer. I would like to eliminate duplicates within a field in a query with other fields been distinct. So for example:

Col1 Col2 Col3
ref1 val1 val2
ref2 val10 val11
ref2 val5 val11
ref3 val20 val21

As you can see, I have ref2 twice in Col1 (which is a primary key on the original table). I have used DISTINCT, but it only eliminates entire duplicate records. I know I can leave only Col1 field and DISTINCT will do the job, but I would like to keep all my fields. Also, by using COUNT I can eliminate them, but I am getting the number of duplicates say in Col2 and hence the values of a column dissappears.

Is there a way round this?
I am using Access 2003.

Any suggestions will be appreciated,

Thank you

Sure you can do it. What do you expect the output to look like? In other words, are you going to drop:
ref2 val10 val11
or
ref2 val5 val11
and why would you choose the one you'd drop over the other?

One way would be to use Max() on the second column. Or you could use Min(). Or...

Thanks for a reply,

In this particular query I do not mind which one to drop (also to make things simpler).
How can I use Max() or Min() when I have my data as a text and not as numbers? Is this possible, if yes can you please be more specific?

Thanks you

I'm just curious as to why you need that field in the query? It doesn't seem to be important enough to care what is shown...why not just remove it from the query?

Quick SQL for ya ....

SELECT Co1, First(Col2), First(Col3)
FROM YourTable
GROUP BY Col1

Sorry to be so short .. but I'm in a bit of a rush.

Hi, Thank you for your replies. I have followed George's method and used First or Last instead of Min() or Max() and it worked.

Thanks again

Glad we could help.