Results 1 to 2 of 2
  1. #1
    pjdube is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    May 2017
    Posts
    25

    Trying to get last Course and last Section Number in Query. Query Sql Help

    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

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is the SQL from post #1 reformatted via PoorSQL.
    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;
    It may offer better readability.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Group Section Page Number Problem
    By ey54025192 in forum Reports
    Replies: 3
    Last Post: 06-28-2018, 06:30 AM
  2. Replies: 6
    Last Post: 03-26-2013, 12:17 PM
  3. Replies: 6
    Last Post: 02-20-2013, 12:32 AM
  4. Replies: 4
    Last Post: 07-10-2012, 01:51 PM
  5. Replies: 6
    Last Post: 06-20-2012, 06:42 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums