querying for whole records with a distinct field

[Raphael Crawford-Marks]

I need to run a query that returns all the fields of a
table (let's call it TableA). But in many records, a
certain field (Field1, let's say) is duplicated. I need
the query to return only unique values for that field. I
can't use SELECT DISTINCT because I need all the fields in
the table. How do I do this?

TABLEA

Visually:

Field1|Field2|Field3
abc |123 |xyz
abc |234 |qrs
bcd |345 |lmnop
bcd |456 |inkd
vfr |987 |kdkf

I want my query to return:

Field1|Field2|Field3
abc |123 |xyz
bcd |345 |lmnop
vfr |987 |kdkf

 

[Jim/chris]

In the query use the grouping option. use group by for
evry field except for Field1. Use one of the other
options first, last ....

Jim

 

[Raphael Crawford-Marks]

Thansk for the help. I got it to work, maybe a little
differently than you hinted at. The query looks like this:

SELECT TableA.Field1, First(TableA.Field2), First
(TableA.Field3)
FROM TableA
GROUP BY TableA.Field1;

 

[John Spencer (MVP)]

Try using a totals query where you Group By the field that you wish to have
appear only once and then use first or last on the other fields to extract on
set of values.

SELECT Field1,
First(Field2) as Fld2,
First(Field3) as Fld3
FROM TableA
GROUP BY Field1

Note that this won't necessarily return any specific record when using first or
last, but it should return the field values from whichever record it decides is
first or last. You can't really control which record it sees as first or last.