Solved Query to Get First and Last Record in a Single Row

[Pac-Man]

Hi,
I have two tables named as tblStudents and tblStudentClass and their structure is as below:
tblStudents:
StudentID (PK)
StudentName

tblStudentClass:
RecordID
StudentID (FK)
ClassID (FK; from another table)
DateOfAdmission
DischargeDate

tblStudentClass can have multiple records for a single StudentID each record for registering the student in a class and then its discharge date. I want to create a query which which gives me following fields and a single record for single student:
StudentID
FirstClassAttended
LastClassAttended

I have VBA code for GetFirstClass and GetLastClass which use query to use TOP 1 record of records sorted in ASC and DSC order by DateOfAdmission but using VBA makes the loading very slow. Is it possible to do the same by queries/subqueries?

Best Regards

 

[June7]

Correlated subquery is one method. Be aware this can perform slowly with large dataset.
Review http://allenbrowne.com/subquery-01.html#TopN

Build query for first and query for last then JOIN those queries to tblStudents.

 

[The_Doc_Man]

Please note that "First" and "Last" have variable meanings in terms of Access recordsets. If the recordset is not sorted (ORDER BY clause) then the order of the recordset MIGHT be "most recently EDITED record last" - which has nothing to do with classes attended.

 

[Pat Hartman]

To take that further. People frequently say "first" when they mean earliest date or occurring before something else. But, think about it this way - since tables and queries are unordered sets, they are sort of like bags of marbles. In this case, "first" is what comes out of the bag when you reach in to pull out the first record. ALWAYS sort your queries, NEVER rely on them being in any particular order unless you specifically sort them.

 

[plog]

Super simple via SQL:

SELECT StudentId, MIN(DateOfAdmission) AS FirstClass, MAX(DateOfAdmission) AS Last class,
FROM tblStudentClass
GROUP BY StudentId

 

[ebs17]

Related to the one date field:

SELECT
   StudentID,
   MIN(DateOfAdmission) AS FirstClassAttended,
   MAX(DateOfAdmission) AS LastClassAttended
FROM
   tblStudentClass
GROUP BY
   StudentID

 

[June7]

@ebs17, my understanding is that they need to know the classes not just the dates. So, take that aggregate query and JOIN to tblStudentClass with compound link on StudentID and DateOfAdmission fields. Also include tblStudents to get student name and tblClasses to get class name.

Possibly perform better than correlated subquery.

 

[CJ_London]

We are just guessing until you provide table design and relationships. If previous suggestions meet your requirements then fine. Otherwise please do so to prevent further guessing

 

[Pac-Man]

Thanks a lot for replying and sorry for late response from my end, I was away from PC. I tried with Min and Max, there are two issues that I face, firstly it gives date whereas I need ClassName as pointed out by @June7 and secondly it will give min/max from whole table, not for specific student. Kindly see that attached DB which I made to show what I want to do and is same structure as that of my original db. Query1 is required to give me FirstClassAttended and LastClassAttended for each student in a single row. I am unable to get the results without using VBA (which slows the loading by great amount).

To take that further. People frequently say "first" when they mean earliest date or occurring before something else.

Yes, I mean the same.

Best Regards

 

[Josef P.]

Are you looking for something like this:

select
    G.StudentID
  , C1.ClassID as FirstClass_ClassID,  C1.DateOfAdmission as FirstClass_DateOfAdmission
  , C2.ClassID as LastClass_ClassID,  C2.DateOfAdmission as LastClass_DateOfAdmission
from
(
   (
      SELECT SC.StudentID, Min(SC.DateOfAdmission) AS MinDateOfAdmission, Max(SC.DateOfAdmission) AS MaxDateOfAdmission
      FROM tblStudentClass As SC
      GROUP BY SC.StudentID
   ) G
   left join
   tblStudentClass As C1 ON C1.StudentID = G.StudentID and C1.DateOfAdmission = G.MinDateOfAdmission
)
   left join
   tblStudentClass As C2 ON C2.StudentID = G.StudentID and C2.DateOfAdmission = G.MaxDateOfAdmission

 

[Pac-Man]

Thanks for SQL, I'll apply it and then let you know.

 

[Pac-Man]

What is difference between C1 and C2, they look same and which query do put StudentName etc from tblStudents?

 

[Pac-Man]

Thanks a lot, it is working as desired. I have applied it in my main DB and it is working there too. I do not understand the login as it seems complex to me but I applied it in actual database, use little knowledge of making queries to put other required fields and it works. Thanks a lot again.

 

[arnelgp]

see also qryFinal.

 

[The_Doc_Man]

What is difference between C1 and C2, they look same and which query do put StudentName etc from tblStudents?

They SHOULD look the same. It is common to use different aliases for the same table when you want to implement JOIN clauses (more specifically, the ON clauses) to compare to more than one other thing, so you use different names to keep the multiple comparisons separate.

 

[Pac-Man]

Thanks a lot. I had been trying to do this for two days before posting here. Finally I decided to post here and my issue is resolved in two hours. Thanks again.

 

[Pac-Man]

Hi,

I want to make another similar query. Purpose is still same, get First Class Attended and the last class attended but difference is it should also include those students that are not yet discharged i.e. discharge date is blank. In current implementation, MaxDischargeDate is joined with DischargeDate and if I filter the records with ClassID, it don't show those records in which DischargeDate is empty i.e. student has not been discharged from the class.

 

[Pat Hartman]

@Pac-Man Please start a new thread for a new question. You can refer to this one if there is something relevant.