Stuck with GROUP BY query

[F.]

Hi,
I'm struggling with a query that I'm sure should be easy: I have a table
"assignements" that has fields
- assignmentID (autonumber)
- personID (long)
- assignmentEndDate (date)
- and more.
I want my query to give a list of (personID , assignmentID) with the
assignmentID of the latest assignment of each person. Each person should be
listed only once, of course. I tried GROUP BYs or TOP 1s to no avail

FYI, this query (let's call it "lastAssignmentOfPerson") is to be used in
another query with
.... LEFT JOIN lastAssignmentOfPerson ON [assignements].personID =
lastAssignmentOfPerson.personID
that is, I want to know, when I'm assigning a person to a new job, what was
his last assignment.

Thanks for the help,

 

[strive4peace]

SELECT
assignmentID
, personID
, assignmentEndDate
FROM
tablename
WHERE assignmentEndDate = dMax(
"assignmentEndDate"
,"Tablename"
, "personID=" & [personID]
)

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[F.]

SELECT
assignmentID
, personID
, assignmentEndDate
FROM
tablename
WHERE assignmentEndDate = dMax(
"assignmentEndDate"
,"Tablename"
, "personID=" & [personID]
)

Warm Regards,
Crystal

Thanks a lot. I was completely on the wrong track... again...

*
have an awesome day
*

You too

 

[strive4peace]

you're welcome happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

SELECT
assignmentID
, personID
, assignmentEndDate
FROM
tablename
WHERE assignmentEndDate = dMax(
"assignmentEndDate"
,"Tablename"
, "personID=" & [personID]
)

Warm Regards,
Crystal

Thanks a lot. I was completely on the wrong track... again...

*
have an awesome day
*

You too