querying for whole records with a distinct field

  • Thread starter Raphael Crawford-Marks
  • Start date

R

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
 
Ad

Advertisements

J

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
 
R

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;
 
Ad

Advertisements

J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top