SELECT DISTINCT only on some fields but display all fields (AC 200

[Bill Nevin]

Hi, I'm looking to write a query that will select records distinctly on some
of the fields of the table, but I want to display all of the fields. The
problem is, when I add all of the fields to the SELECT statement, it includes
it into the DISTINCT statement. For example, if I have a table with six
fields and I want only distinct records for the first four fields, I could do:

SELECT DISTINCT [FIELD 1], [FIELD 2], [FIELD 3], [FIELD 4]
FROM tblExample;

This would yield, of course, only unique combinations of those fields. This
is what I want, unfortunately, I'd also like to show the last two fields, but
not to include them into the DISTINCT clause. If I give you an example table:

[FIELD 1] [FIELD 2] [FIELD 3] [FIELD 4] [FIELD 5] [FIELD 6]
<REF NUM>

APPLE JOHN 1/1/07 1/3/07 PAUL 0.80
1
APPLE JOHN 1/1/07 1/3/07 MIKE 0.10
2
APPLE JOHN 1/1/07 1/3/07 BILL 0.10
3
APPLE JOHN 2/1/07 2/3/07 PAUL 0.80
4
APPLE JOHN 2/1/07 2/3/07 MIKE 0.10
5
APPLE JOHN 2/1/07 2/3/07 BILL 0.10
6
APPLE JOHN 3/1/07 3/3/07 MIKE 0.60
7
APPLE JOHN 3/1/07 3/3/07 BILL 0.40
8
GRAPE PAUL 1/1/07 1/3/07 JOHN 1.00
9

I apologize if this isn't displayed fixed-width. If it doesn't and you're
curious to help, simply paste it into a text editor with a fixed-width font.

As you can see, however, that the above query would select records 1, 4, 7,
and 9 from this table. The exact records, I'd like to select. However, I'd
also like to see the [FIELD 5] and [FIELD 6] values from these records. If I
select them, in the above table's case, I would end up selecting all of the
records.

I hope you can see my issue. Does anyone know a way to do what I'm looking
for?

Thanks.

 

[Douglas J. Steele]

You'd have to create a query that uses DISTINCT on the first 4 fields, and
then join that query to the table to get the other fields.

Alternatively, you could try

SELECT DISTINCT [FIELD 1], [FIELD 2], [FIELD 3], [FIELD 4], [FIELD 5],
[FIELD 6]
FROM tblExample
GROUP BY [FIELD 1], [FIELD 2], [FIELD 3], [FIELD 4]

 

[David W. Fenton]

You'd have to create a query that uses DISTINCT on the first 4
fields, and then join that query to the table to get the other
fields.

Alternatively, you could try

SELECT DISTINCT [FIELD 1], [FIELD 2], [FIELD 3], [FIELD 4], [FIELD
5], [FIELD 6]
FROM tblExample
GROUP BY [FIELD 1], [FIELD 2], [FIELD 3], [FIELD 4]

Or use a derived table and join it to the full table:

SELECT Unique.Field1, Unique.Field2, Unique.Field3, Unique.Field4,
tblExample.Field5, tblExample.Field6
FROM [SELECT DISTINCT Field1, Field2, Field3, Field4
FROM tblExample]. As Unique JOIN tblExample
ON Unique.Field1 = tblExample.Field1
AND Unique.Field2 = tblExample.Field2
AND Unique.Field3 = tblExample.Field3
AND Unique.Field4 = tblExample.Field4

(SQL may be slightly in error, but that's the basic idea)

 

[John Spencer]

As far as I can see your requirements (as stated) cannot be met. Unless
you only want a one value from field 5 and one from Field 6 or want to
concatenate the multiple values into one record for field 5 and field 6

Given your example (First three rows)
[FIELD 1] [FIELD 2] [FIELD 3] [FIELD 4] [FIELD 5] [FIELD 6] <REF>
APPLE JOHN 1/1/07 1/3/07 PAUL 0.80 1
APPLE JOHN 1/1/07 1/3/07 MIKE 0.10 2
APPLE JOHN 1/1/07 1/3/07 BILL 0.10 3

What would you expect to see returned?
APPLE JOHN 1/1/07 1/3/07 ??? ???

You could return one of the following
Paul and .8
Or
Mike and .1
Or
Bill and .1

Or you could return
Paul, Mike, Bill : .8, .1, .1

Or you could return Max values such as
Bill, .8

Or a Max value and a sum, such as
Bill, 1.0

etc.

One example using a totals query

SELECT DISTINCT [FIELD 1], [FIELD 2], [FIELD 3], [FIELD 4]
First([Field 5]) as F5, First([Field 6]) as F6
FROM tblExample
GROUP BY [FIELD 1], [FIELD 2], [FIELD 3], [FIELD 4]



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi, I'm looking to write a query that will select records distinctly on some
of the fields of the table, but I want to display all of the fields. The
problem is, when I add all of the fields to the SELECT statement, it includes
it into the DISTINCT statement. For example, if I have a table with six
fields and I want only distinct records for the first four fields, I could do:

SELECT DISTINCT [FIELD 1], [FIELD 2], [FIELD 3], [FIELD 4]
FROM tblExample;

This would yield, of course, only unique combinations of those fields. This
is what I want, unfortunately, I'd also like to show the last two fields, but
not to include them into the DISTINCT clause. If I give you an example table:

[FIELD 1] [FIELD 2] [FIELD 3] [FIELD 4] [FIELD 5] [FIELD 6]
<REF NUM>

APPLE JOHN 1/1/07 1/3/07 PAUL 0.80
1
APPLE JOHN 1/1/07 1/3/07 MIKE 0.10
2
APPLE JOHN 1/1/07 1/3/07 BILL 0.10
3
APPLE JOHN 2/1/07 2/3/07 PAUL 0.80
4
APPLE JOHN 2/1/07 2/3/07 MIKE 0.10
5
APPLE JOHN 2/1/07 2/3/07 BILL 0.10
6
APPLE JOHN 3/1/07 3/3/07 MIKE 0.60
7
APPLE JOHN 3/1/07 3/3/07 BILL 0.40
8
GRAPE PAUL 1/1/07 1/3/07 JOHN 1.00
9

I apologize if this isn't displayed fixed-width. If it doesn't and you're
curious to help, simply paste it into a text editor with a fixed-width font.

As you can see, however, that the above query would select records 1, 4, 7,
and 9 from this table. The exact records, I'd like to select. However, I'd
also like to see the [FIELD 5] and [FIELD 6] values from these records. If I
select them, in the above table's case, I would end up selecting all of the
records.

I hope you can see my issue. Does anyone know a way to do what I'm looking
for?

Thanks.