Hi,
I have a table of people's degrees. Its fields are:

ID (the id of the person)
OrgID (the ID of the college that awarded the degree)
Degree (usually BA, BS, etc )
StudyField ( something like "Political Science" "Physics," etc)
DegDt (the date the degree was awarded

I have a query I'm using to find the most recent degree of the person
(some people have three or four):

SELECT DISTINCT tblExternalDegrees.ID, tblExternalDegrees.[Org ID],
tblExternalDegrees.Degree, tblExternalDegrees.[StudyField],
tblExternalDegrees.[DegDt]
FROM tblExternalDegrees
WHERE (((tblExternalDegrees.[Deg Dt])=(select Max([Deg Dt]) from
tblExternalDegrees as Ext where Ext.ID = tblExternalDegrees.ID)));

I use the date the degree was awarded to find the most recent. It
works just fine for nearly all the people in the database.
Unfortunately, some people have two degrees awarded on the same date
from the same institution. This occurs when a double major was
recorded as two degrees or sometimes when a person had done enough to
get a BA and an MA from the same institution but they were awarded at
the same time. In those cases, my query above finds both.
What I need is to just find one of the two. It does not really matter
which of the two, but I need to return just one degree per person. I
need to modify the query to return the most recent degree and if two
were awarded on the same day, return just one of them. I cannot get
my head round a way to do that.

Any help appreciated.

Mike

Gilgamesh wrote:

Assuming you have a primary key field in this table, I think
this might do it:

SELECT ID, [Org ID], Degree, StudyField, DegDt
FROM tblExternalDegrees As T
WHERE PK = (SELECT TOP 1 PK
FROM tblExternalDegrees as Ext
WHERE Ext.ID = T.ID
ORDER BY DegDt DESC, Degree DESC,
StudyField DESC)

--
Marsh

On Feb 5, 8:36*pm, Marshall Barton <marshbar... (AT) wowway (DOT) com> wrote:
Marsh,
Thanks. That was just what I needed.
Michael