Grouped Query not sorting correctly

Question

I have a query that links 4 different tables. That query is sorted by last name and then by enrollment start (this is a database of students). Students may have multiple enrollments in the same program where they start, stop, come back, stop again, come back again and eventually graduate or drop again. Therefore, in this query there may be multiple lines o=f data for the same student. Most students will start and graduate and only have one line. To further complicate things, this query also has one table representing job data after completion of the training. So they may have 4 lines of data due to multiple enrollments and 2 or more jobs after graduating. But that's not my problem ( I don't think)

The query I just described is the base query for another query that groups the data on the program of study (some students take more than one program). By grouping on the program I hope to consolidate all lines of data into one line per program of study. I group on Student ID and then on Program. I then sort on name and then enrollment start date. I also have the base query described above sorted on name and enrollment start just to speed up the results in the second query.

In the group query I want to see the first enrollment start date and I want to see the last enrollment end date and the last enrollment status (drop, grad, active...etc). Most of the students result correctly but I have a few who do not show the correct data elements. I have put together a spreadsheet showing 2 of the students (out of 15,000 students) as an example. One sorted correctly and the other did not. I highlighted the data that the query results to to make it easier. The first line is the group query results, and the next lines are the base query results that it pulls from. You can see that the lines o=f data in the base are in the correct order. But the group query is picking the wrong data for some reason.

The SQL for the Group Query:

SELECT [Grad and Employment query].STUDENT_ID, [Grad and Employment query].PROGRAM_DESCRIPTION, First([Grad and Employment query].STUDENT_NAME) AS FirstOfSTUDENT_NAME, First([Grad and Employment query].ENROLLMENT_START_DATE) AS FirstOfENROLLMENT_START_DATE, Last([Grad and Employment query].ENROLLMENT_END_DATE) AS LastOfENROLLMENT_END_DATE, Last([Grad and Employment query].STUDENT_STATUS) AS LastOfSTUDENT_STATUS, Last([Grad and Employment query].STATUS_DATE) AS LastOfSTATUS_DATE, Last([Grad and Employment query].ENROLLMENT_STATUS) AS LastOfENROLLMENT_STATUS, First([Grad and Employment query].TRANSFER_IN) AS FirstOfTRANSFER_IN, Last([Grad and Employment query].TRANSFER_OUT) AS LastOfTRANSFER_OUT, Last([Grad and Employment query].GRAD_DATE) AS LastOfGRAD_DATE, Last([Grad and Employment query].LDA) AS LastOfLDA, Last([Grad and Employment query].DROP_REASON) AS LastOfDROP_REASON, Last([Grad and Employment query].[student-enrollment].PLACEMENT_STATUS) AS [LastOfstudent-enrollment_PLACEMENT_STATUS], Last([Grad and Employment query].[Student-Job1].PLACEMENT_STATUS) AS [LastOfStudent-Job1_PLACEMENT_STATUS], Last([Grad and Employment query].FULL_PART_TIME) AS LastOfFULL_PART_TIME, Last([Grad and Employment query].PAY_TYPE) AS LastOfPAY_TYPE, Last([Grad and Employment query].CURRENT_JOB) AS LastOfCURRENT_JOB, Last([Grad and Employment query].JOB_TITLE) AS LastOfJOB_TITLE, Last([Grad and Employment query].JOB_START_DATE) AS LastOfJOB_START_DATE, Last([Grad and Employment query].IN_FIELD) AS LastOfIN_FIELD, Last([Grad and Employment query].PAY_AMOUNT) AS LastOfPAY_AMOUNT, Last([Grad and Employment query].COMPANY_NAME) AS LastOfCOMPANY_NAME, Last([Grad and Employment query].COMPANY_CONTACT) AS LastOfCOMPANY_CONTACT, Last([Grad and Employment query].ADDRESS) AS LastOfADDRESS, Last([Grad and Employment query].CITY) AS LastOfCITY, Last([Grad and Employment query].STATE) AS LastOfSTATE, Last([Grad and Employment query].ZIP) AS LastOfZIP, Last([Grad and Employment query].PHONE) AS LastOfPHONE, Last([Grad and Employment query].MONTHS) AS LastOfMONTHS, First([Grad and Employment query].Bonus_Req_Grad_Date) AS FirstOfBonus_Req_Grad_Date, First([Grad and Employment query].allowed) AS FirstOfallowed, First([Grad and Employment query].DaysAllowed) AS FirstOfDaysAllowed, First([Grad and Employment query].ACCSC_Req_Grad_Date) AS FirstOfACCSC_Req_Grad_Date, First([Grad and Employment query].AllowedB) AS FirstOfAllowedB, First([Grad and Employment query].DaysAllowedB) AS FirstOfDaysAllowedB, First([Grad and Employment query].RequiredGradDateB) AS FirstOfRequiredGradDateB

FROM [Grad and Employment query]

GROUP BY [Grad and Employment query].STUDENT_ID, [Grad and Employment query].PROGRAM_DESCRIPTION

ORDER BY First([Grad and Employment query].STUDENT_NAME), First([Grad and Employment query].ENROLLMENT_START_DATE);

Answer 1

This is a FAQ. You can use a correlated subquery: https://stackoverflow.com/questions/46095187/sql-access-select-maxdate-and-corresponding-value

Answer 2

Brilliant! This worked. I created a Totals query where I get my max and min dates and then created another select query where I connect the first Totals query to the rest of the data elements and the Order by function works perfectly so I can get First and Last where needed.

Answer 3

That option (Max) works for date fields. But how would I get the corresponding status for the record with the max date to display?

Answer 4

I see you rely heavily on “Last”. I have never found this reliable. Without attempting to completely understand your requirements, it may be necessary to use Max of a date field to pull the appropriate records.

Answer 5

Here is the spreadsheet I mentioned