Subquery sorted by another table

This is a follow-up question to Get the following record in query. But the task is a bit more complicated. I tried to modify the SQL query but I was not able to fulfill the task.

If we have two tables, one called Activity and has rows [ActivityCode and StartTime], and the another one called Students has rows [Name and ID] for example:

Name-----ID-----ActivityCode-----StartTime<BR>
Tom------123------Lunch------------1200<BR>
Tom------123------MathClass--------1300<BR>
Tom------123------EnglishClass-----1500<BR>
Tom------123------EndOfSchool------1700<BR>
Mary-----369-----Lunch------------1200<BR>
Mary-----369-----ScienceClass-----1300<BR>
Mary-----369-----EnglishClass-----1600<BR>
Mary-----369-----EndOfSchool------1700<BR>

And now I want to make one SQL Query to display as follow:

Name-----ID------ActivityCode-----StartTime------EndTime<BR>
Tom------123---  Lunch------------1200-----------1300<BR>
Tom------123-----MathClass--------1300-----------1500<BR>
Tom------123-----EnglishClass-----1500-----------1700<BR>
Tom------123-----EndOfSchool------1700-----------1700<BR>
Mary-----369-----Lunch------------1200-----------1300<BR>
Mary-----369-----ScienceClass-----1300-----------1600<BR>
Mary-----369-----EnglishClass-----1600-----------1700<BR>
Mary-----369-----EndOfSchool------1700-----------1700<BR>

I follow the code, credits to Gustav:

SELECT 
    Activity.ActivityCode, 
    Activity.StartTime, 
    Nz((Select Top 1 StartTime 
        From Activity As T 
        Where T.StartTime > Activity.StartTime 
        Order By StartTime Asc),
        [StartTime]) AS EndTime, 
    CDate(TimeSerial(Val([EndTime])100,Val([EndTime]) Mod 100,0)-
        TimeSerial(Val([StartTime])100,Val([StartTime]) Mod 100,0)) AS Duration
FROM 
    Activity;

I tried to modify the part

Order By StartTime Asc

Because the whole query is sorted according to the Student ID which is from another table. But some message boxes popped up and I couldn’t solve it. How can I modify it? thank you.

Answer

Consider this SQL:

SELECT Students.ID, [Name], ActivityCode, StartTime,
Nz((Select Top 1 StartTime FROM Activity As T 
        WHERE T.StartTime > Activity.StartTime AND T.ID=Activity.ID
        ORDER BY StartTime Asc),Startime) AS EndTime, 
DateDiff("h",TimeSerial(StartTime/100,0,0),TimeSerial(EndTime/100,0,0)) AS Duration
FROM Students INNER JOIN Activity ON Students.ID=Activity.ID;

Advertisement

Answer