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:

Below is what I am wanting as the result:

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.

CodePudding user response：

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: