Add Time Together From Previous Row in Microsoft Access

huskypride 0
Viewed 55

I am using Microsoft Access 2016 and I have a table where the goal is using the provided start time for each date, add the given Slot to the Start to calculate the End time. Where I am having difficulties is where Start is null, it is to pull the previous records' End time as its Start time and repeat the process.

Below is a sample table of data:

Day PrimaryKey AbovePrimaryKey Start Slot End
9/19/2022 171 4:00:00 PM 2:30
9/19/2022 172 171 2:30
9/19/2022 173 172 1:30
9/20/2022 174 173 11:00:00 AM 1:30
9/20/2022 175 174 1:30
9/20/2022 176 175 2:30
9/20/2022 177 176 2:30
9/20/2022 178 177 1:00
9/20/2022 179 178 1:00

Below is what I am wanting as the result:

Day PrimaryKey Start Slot End
9/19/2022 171 4:00:00 PM 2:30 6:30:00 PM
9/19/2022 172 6:30:00 PM 2:30 8:30:00 PM
9/19/2022 173 8:30:00 PM 1:30 10:00:00 PM
9/20/2022 174 11:00:00 AM 1:30 12:30:00 PM
9/20/2022 175 12:30:00 PM 1:30 2:00:00 PM
9/20/2022 176 2:00:00 PM 2:30 4:30:00 PM
9/20/2022 177 4:30:00 PM 2:30 7:00:00 PM
9/20/2022 178 7:00:00 PM 1:00 8:00:00 PM
9/20/2022 179 8:00:00 PM 1:00 9:00:00 PM

I was able to get the previous row's primary key to use in reference in some subquery to pull the previous row's End, but am unable to determine how to do so, especially since I have also have to calculate Start and End.

Any help would be greatly appreciated. Thank you.

ms-access-2016 ms-access sql
1 Answers

You can do this with four sets of subqueries:

SELECT 
    SampleData.Day, 
    SampleData.PrimaryKey, 

        (Select S2.Start 
        From SampleData As S2 
        Where S2.PrimaryKey = 
            (Select Max(S.PrimaryKey) 
            From SampleData As S 
            Where S.Start Is Not Null And S.PrimaryKey <= SampleData.PrimaryKey)) +

        (Select CDate(IIf(Sum(S3.Slot) Is Null, #00:00#, Sum(S3.Slot))) 
        From SampleData As S3 
        Where S3.PrimaryKey < SampleData.PrimaryKey And S3.PrimaryKey >= 
            (Select Max(S.PrimaryKey) 
            From SampleData As S 
            Where S.Start Is Not Null and S.PrimaryKey <= SampleData.PrimaryKey)) As [Start],

    SampleData.Slot, 

        (Select S2.Start 
        From SampleData As S2 
        Where S2.PrimaryKey = 
            (Select Max(S.PrimaryKey) 
            From SampleData As S 
            Where S.Start Is Not Null And S.PrimaryKey <= SampleData.PrimaryKey)) +

        (Select CDate(Sum(S3.Slot)) 
        From SampleData As S3 
        Where S3.PrimaryKey <= SampleData.PrimaryKey And S3.PrimaryKey >= 
            (Select Max(S.PrimaryKey) 
            From SampleData As S 
            Where S.Start Is Not Null And S.PrimaryKey <= SampleData.PrimaryKey)) As [End] 

FROM 
    SampleData;

Output:

enter image description here

uservDNe0
Related