Selecting last dated training record for each person

[Guest]

Hi all,

I have an existing table that looks like this (training periods are between
startdate-enddate, empty value is because the training is still on-going):

RecordNo - TrainingID - Startdate - Enddate - PersonID - ClassWhenAttending
1 Tr_A 21/01/2007 1/03/2007 1 C1
2 Tr_A 21/01/2007 1/03/2007 2 C1
3 Tr_B 1/01/2007 20/02/2007 1 C2
4 Tr_B 1/01/2007 20/02/2007 3 C2
5 Tr_C 1/06/2007 [empty] 2 C3
6 Tr_C 1/06/2007 [empty] 3 C3
7 Tr_C 1/06/2007 [empty] 4 C2

I want to select the record of each person (PersonID) where he/she (last
attended) OR (is last starting because training is not ended yet) so that the
result should be like this (each record of personid 1, 2, 3 & 4):

RecordNo - TrainingID - Startdate - Enddate - PersonID - ClassWhenAttending
3 Tr_B 1/01/2007 20/02/2007 1 C2
5 Tr_C 1/06/2007 [empty] 2 C3
6 Tr_C 1/06/2007 [empty] 3 C3
7 Tr_C 1/06/2007 [empty] 4 C2

Can someone help how to make a query design (using filter?) or a SQL
statement for this?

Thanks for your help.
Rudy

 

[Guest]

Hi

Try this link

http://www.mvps.org/access/queries/qry0020.htm

 

[Guest]

Thanks Wayne.

Your info helped a lot. I tried yesterday and it worked fine. The Library
case (BookID, DateOut, BorrowerID) is really similar to my case (TrainingID,
Enddate, PersonID) except I added IIF function in the SQL to check whether
using the latest Enddate or the latest Startdate (if Enddate is empty).

Cheers,
Rudy
Jakarta, ID - Canberra, AU

Hi

Try this link

http://www.mvps.org/access/queries/qry0020.htm


--
Wayne
Manchester, England.

Hi all,

I have an existing table that looks like this (training periods are between
startdate-enddate, empty value is because the training is still on-going):

RecordNo - TrainingID - Startdate - Enddate - PersonID - ClassWhenAttending
1 Tr_A 21/01/2007 1/03/2007 1 C1
2 Tr_A 21/01/2007 1/03/2007 2 C1
3 Tr_B 1/01/2007 20/02/2007 1 C2
4 Tr_B 1/01/2007 20/02/2007 3 C2
5 Tr_C 1/06/2007 [empty] 2 C3
6 Tr_C 1/06/2007 [empty] 3 C3
7 Tr_C 1/06/2007 [empty] 4 C2

I want to select the record of each person (PersonID) where he/she (last
attended) OR (is last starting because training is not ended yet) so that the
result should be like this (each record of personid 1, 2, 3 & 4):

RecordNo - TrainingID - Startdate - Enddate - PersonID - ClassWhenAttending
3 Tr_B 1/01/2007 20/02/2007 1 C2
5 Tr_C 1/06/2007 [empty] 2 C3
6 Tr_C 1/06/2007 [empty] 3 C3
7 Tr_C 1/06/2007 [empty] 4 C2

Can someone help how to make a query design (using filter?) or a SQL
statement for this?

Thanks for your help.
Rudy