Picking up last record

S

Sue Compelling

Hi

I have the following query

SELECT [FirstName] & (" ("+[PreferredName]+")") & (" "+[SecondName]) & " " &
UCase([LastName]) AS StName, TblSchools.SchoolMth, TblSchools.SchoolYr,
TblSchools.FormYr, TblSchools.School, TblSchools.Teacher
FROM TblStudents INNER JOIN TblSchools ON TblStudents.StudentID =
TblSchools.StudentIDFK
GROUP BY [FirstName] & (" ("+[PreferredName]+")") & (" "+[SecondName]) & " "
& UCase([LastName]), TblSchools.SchoolMth, TblSchools.SchoolYr,
TblSchools.FormYr, TblSchools.School, TblSchools.Teacher;

Which at times will result in more that one school record being displayed
for a student .. eg


StName SchoolMth SchoolYr FormYr School
Tony TUMUSA 8 Glenavon School
Tony TUMUSA Feb 2009 9 Kelston Boys High
Trey FIAOLA 8 Mt Roskill Intermediate
Trey FIAOLA Feb 2009 9 Lynfield College


I want my query to be able to pick up ONLY the last school entires - eg


StName SchoolMth SchoolYr FormYr School
Tony TUMUSA Feb 2009 9 Kelston Boys High
Trey FIAOLA Feb 2009 9 Lynfield College

Is there a way I can do this easily without the user having to notate the
last record as "current"?

TIA

Sue
 
J

John W. Vinson

Hi

I have the following query

SELECT [FirstName] & (" ("+[PreferredName]+")") & (" "+[SecondName]) & " " &
UCase([LastName]) AS StName, TblSchools.SchoolMth, TblSchools.SchoolYr,
TblSchools.FormYr, TblSchools.School, TblSchools.Teacher
FROM TblStudents INNER JOIN TblSchools ON TblStudents.StudentID =
TblSchools.StudentIDFK
GROUP BY [FirstName] & (" ("+[PreferredName]+")") & (" "+[SecondName]) & " "
& UCase([LastName]), TblSchools.SchoolMth, TblSchools.SchoolYr,
TblSchools.FormYr, TblSchools.School, TblSchools.Teacher;
....

I want my query to be able to pick up ONLY the last school entires - eg


StName SchoolMth SchoolYr FormYr School
Tony TUMUSA Feb 2009 9 Kelston Boys High
Trey FIAOLA Feb 2009 9 Lynfield College
Click to expand...

What - in the table - constitutes the "last" record? schoolYr (2009), a date
combining SchoolMth and SchoolYr, FormYr (8/9), date entered (which you don't
describe and which Access doesn't record)?

You'll need some sort of subquery or DMax() function to find that record. You
may want to consider using a Date/Time field combining schoolyr and schoolmth
- it's easier to display a Date as year, month, or both than to combine an
Integer and a Text into a date (though that can be done).
 
S

Sue Compelling

Thank you John as always. In the end I chose to add a "status" check box to
denote current school. The user data is incomplete in most instances and not
input in chronological order either.

Cheers anyway.
--
Sue Compelling


John W. Vinson said:
Hi

I have the following query

SELECT [FirstName] & (" ("+[PreferredName]+")") & (" "+[SecondName]) & " " &
UCase([LastName]) AS StName, TblSchools.SchoolMth, TblSchools.SchoolYr,
TblSchools.FormYr, TblSchools.School, TblSchools.Teacher
FROM TblStudents INNER JOIN TblSchools ON TblStudents.StudentID =
TblSchools.StudentIDFK
GROUP BY [FirstName] & (" ("+[PreferredName]+")") & (" "+[SecondName]) & " "
& UCase([LastName]), TblSchools.SchoolMth, TblSchools.SchoolYr,
TblSchools.FormYr, TblSchools.School, TblSchools.Teacher;
....

I want my query to be able to pick up ONLY the last school entires - eg


StName SchoolMth SchoolYr FormYr School
Tony TUMUSA Feb 2009 9 Kelston Boys High
Trey FIAOLA Feb 2009 9 Lynfield College
Click to expand...

What - in the table - constitutes the "last" record? schoolYr (2009), a date
combining SchoolMth and SchoolYr, FormYr (8/9), date entered (which you don't
describe and which Access doesn't record)?

You'll need some sort of subquery or DMax() function to find that record. You
may want to consider using a Date/Time field combining schoolyr and schoolmth
- it's easier to display a Date as year, month, or both than to combine an
Integer and a Text into a date (though that can be done).
Click to expand...
 
Top