Hi everyone!
Just need help with this query sql statement.
In short a student is enrolled on a course and sends in their lesson. However that same student could have enrolled on multiple courses or could have completed a course. What I am trying to achieve here with my query is to get the Last Course and the Last Section (Lesson) he or she sent in. I would think it would be easy to figure out as I have the date of when they sent in the lesson but the below query is only getting the MAX Lesson number, when I want the MAX Lesson (Section) for the Last Course he was one. Can someone help me here>
Here is my query code:
Code:
SELECT Students.[Full Name], Last(Students.[Current Course]) AS [LastOfCurrent Course], Students.Started, Last(Lessons.Section) AS [Section], ExtStructure.Sections, Last(Lessons.[Date In]) AS [LastOfDate In]FROM (Lessons INNER JOIN Students ON Lessons.[Full Name] = Students.[Full Name]) INNER JOIN ExtStructure ON Students.[Current Course] = ExtStructure.[Course]
GROUP BY Students.[Full Name], Students.Started, ExtStructure.Sections;
I am assuming that I have to have some sort of Select Distinct somewhere in the above code.
But in essence I want the query datasheet to look like:
Full Name |
Current Course |
Started |
Section |
Sections |
Joe Blow |
Blah Blah Course |
1 Jan 2020 |
5 |
20 |
Mike Smith |
Bleh Bleh Course |
3 Mar 2020 |
6 |
15 |
Even though Joe Blow also submitted 8 Lessons (Sections) of the Grrrr Grrrr Course but did this last year.
Instead it is showing:
Full Name |
Current Course |
Started |
Section |
Sections |
Joe Blow |
Grrrr Grrrr Course |
30 Nov 19 |
8 |
15 |
Mike Smith |
Bleh Bleh Course |
3 Mar 2020 |
6 |
15 |
I hope this was clear enough!
Thanks!
Phil