garyj
12-17-2015, 06:55 PM
I didn't think this one would be so difficult. :cool:
Table: SkillLevels with fields: GuestID1, RegistID2, SkillID3, Level4 (numbers only to identify what they are for below)
A guest registers for camp. He takes 2 or 3 skills per year, and passes or participates in the level. By using the results of the query I am here trying to create, it is easy enough to make another query to get the following:
Camper name1, skill name2, level passed3, at the last camp4 he took that skill.
But a camper may have taken canoeing every year, and may have 3 records with that skill. So what I want is only the last record for canoeing, but also the last record for each of the other skills he took. Note the following table (I inserted row numbers only to help with reference on this post.)
Row
GuestID
SkillID
Level
RegistID
1
1
1
P1
72
2
1
2
C1
72
3
2
1
C3
73
4
2
5
C4
73
5
1
1
C1
832
6
1
2
C2
832
7
2
2
C1
833
8
2
7
C2
833
The results I want are...
for guest1: rows 5, 6
for guest2: rows 3, 4, 7, 8
So I want the last record for each guest where SkillID is unique for that guest.
Thanks for any help you can give.
I tried the Last aggregate function that comes in the building of queries, but wasn't successful. At the time I was also querying only for guest1, and yet it pulled rows 1 and 5, as well as rows 2 and 6.
GaryJ
Table: SkillLevels with fields: GuestID1, RegistID2, SkillID3, Level4 (numbers only to identify what they are for below)
A guest registers for camp. He takes 2 or 3 skills per year, and passes or participates in the level. By using the results of the query I am here trying to create, it is easy enough to make another query to get the following:
Camper name1, skill name2, level passed3, at the last camp4 he took that skill.
But a camper may have taken canoeing every year, and may have 3 records with that skill. So what I want is only the last record for canoeing, but also the last record for each of the other skills he took. Note the following table (I inserted row numbers only to help with reference on this post.)
Row
GuestID
SkillID
Level
RegistID
1
1
1
P1
72
2
1
2
C1
72
3
2
1
C3
73
4
2
5
C4
73
5
1
1
C1
832
6
1
2
C2
832
7
2
2
C1
833
8
2
7
C2
833
The results I want are...
for guest1: rows 5, 6
for guest2: rows 3, 4, 7, 8
So I want the last record for each guest where SkillID is unique for that guest.
Thanks for any help you can give.
I tried the Last aggregate function that comes in the building of queries, but wasn't successful. At the time I was also querying only for guest1, and yet it pulled rows 1 and 5, as well as rows 2 and 6.
GaryJ