Stuck with GROUP BY query

  • Thread starter Thread starter F.
  • Start date Start date
F

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,
 
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
*
 
strive4peace said:
SELECT
assignmentID
, personID
, assignmentEndDate
FROM
tablename
WHERE assignmentEndDate = dMax(
"assignmentEndDate"
,"Tablename"
, "personID=" & [personID]
)

Warm Regards,
Crystal
Click to expand...

Thanks a lot. I was completely on the wrong track... again...
*
(: have an awesome day :)
*
Click to expand...
You too :-)
 
you're welcome ;) happy to help

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



F. said:
strive4peace said:
SELECT
assignmentID
, personID
, assignmentEndDate
FROM
tablename
WHERE assignmentEndDate = dMax(
"assignmentEndDate"
,"Tablename"
, "personID=" & [personID]
)

Warm Regards,
Crystal
Click to expand...

Thanks a lot. I was completely on the wrong track... again...
*
(: have an awesome day :)
*
Click to expand...
You too :-)
Click to expand...
 
Back
Top