Finding Maximum Date

[jsgarlata]

Hello, I have a database that is keeping attendance logs for students.  The attendance table basically consist of student name, student ID, and date attended.  I would like to pull up a list of all students and their last day that they attended (this would be the maximum date attended).  I wish to only see the student's last day, not all the records for that student.   so ultimately, my report would look like:

John Jones     1/1/17

John Smith    3/12/17

Joe Smith     2/28/17

I cant seem to find anywhere to do this.  

 

Thank you in advance

 

[Mathilda]

On 8/9/2017 at 4:02 PM, jsgarlata said:

Hello, I have a database that is keeping attendance logs for students.  The attendance table basically consist of student name, student ID, and date attended.  I would like to pull up a list of all students and their last day that they attended (this would be the maximum date attended).  I wish to only see the student's last day, not all the records for that student.   so ultimately, my report would look like:

John Jones     1/1/17

John Smith    3/12/17

Joe Smith     2/28/17

I cant seem to find anywhere to do this.  

 

Thank you in advance

 

Hey, I would recommend creating a report datapage using table with students (where each record is unique). Then insert calculated field on results page and run SQL query like that:

select Top(1) CONVERT(VARCHAR(32), Datefield, 1) from table_name where studentID = target.[@field:studentID] Order by Datefield Desc

[jsgarlata]

Thank you Mathilda, i will give it a try

 

[Kurumi]

You may also want to try this one: 

CONVERT(VARCHAR(10),(SELECT MAX(Test_Date_Of_Email) FROM Table2 WHERE Test_ID =target.[@field:Test_ID]),101)

CONVERT(VARCHAR(10),(SELECT MAX(TableFieldName) FROM Name_of_Table WHERE Field_Name =target.[@field:Field_Name]),101)

I hope this helps!