Continuous Form Calculate dates based on previous row value

In a relational database, rows have no relation to each other.  Tables and queries are by definition unordered sets.  Your request assumes a particular order so you MUST have a unique identifier that will maintain that order.  In the BeforeInsert event of the form, you would find the "previous" record by finding the record with the maximum ID and use the end date from that as your start date.

Keep in mind that with a schema like this it is not possible to make changes to the dates after the fact since that would completely mess up anything that follows unless you wrote code to modify all the "newer" records.  That is why relational databases don't do this.  

The better practice solution is to use a generated sequence number to maintain order within a group.  (If you need to be able to reorder the set, you will need to build a special procedure to do that.  I usually generate sequence numbers with large gaps and let the users update the sequence numbers.  So they can take item 110 and make it 56 to move it between 50 and 60.  Then I have another procedure that renumbers the group if the gaps get too small so the users can't easily move things around).  You also should not store the end date since that is dependent on the start date.  You would use the "project date" as the start date.  That way you would always calculate the start date of an individual row by summing the duration for all the "previous" records and adding that  to the start date.  By storing the start date at the project level, you can reschedule everything on the fly by simply changing the start date.  No updates are necessary because the query calculates the start/stop date for each line every time it runs.

Sounds good except I have no idea how to do what you are suggesting.  Also - the users will definitely be changing the order and will do so many times.  

The start/end dates are currently arrived at with a formula in the query that drives the form.
The order and the duration with the main form start date are used to calculate the dates on the continous form.  I attached a sample DB with qryTasks that has the calculations.

However the calculation I am using in the query results in incorrect dates and I can not figure out how to alter it so that it works.

Glad to hear that you are already calculating the dates.  The database isn't attached and I can't download it anyway.  Try posting the SQL statement and we'll see if we can see the calculation error.

Thanks much!!

StartDate: [Required_Start]+Nz(DSum("[T_Aggressive_duration]","Tasks_tbl","Project_ID=" & [Tasks_tbl]![Project_ID] & " AND Order<" & [Order]),1)

EndDate: [Required_Start]+DSum("[T_Aggressive_duration]","Tasks_tbl","Project_ID=" & [Tasks_tbl]![Project_ID] & " AND Order<=" & [Order])

I think you want to subtract 1 from the EndDate.  That way, the start date counts as day 1 of the task.

EndDate: [Required_Start]+DSum("[T_Aggressive_duration]","Tasks_tbl","Project_ID=" & [Tasks_tbl]![Project_ID] & " AND Order<=" & [Order]) -1

Thank you Pat for your response.  Sorry to say, not working.  

The first row the start date should refer to the main form date --  4/17/14 and the end date should be that date +5 (the duration) -- 4/22

Instead the formula for start date is giving the date of 4/18 and your formula for the end date is giving 4/21.

The next start date (Order - 2) needs to be the above end date + 1.  The end date should be that rows start date + duration. Follow that pattern for the rest of the records.

Any ideas of what might work?

If the project starts on the 17th, are you counting the 17th as day 1.  If so, the last day will be the 21st. (17 + 5 -1).

I see your point, however they are figuring that if a task takes 1 day - it would be more of a 24 hour time frame.  So a 1 day duration would be from the 17th til the 18th.  

So, that being the case - does the  Start date formula need to be altered?

You could add 1 to start date and that will work for all records except the first one.  So, you have to do it conditionally.  If the sum is > 0 then you would add 1.  Alternatively, you could start the project one day earlier.

Unfortunately this is bringing me full circle back to why I posted.
What you suggesting in your last post is basically what I am doing. For the start date what seems logical and what it seems like I am doing with my formula is not working.

It works for the first 2 lines, then it falls apart and gives the incorrect start date.
For the end date - I think it would always simply be the start date + duration, so it seems that getting the start date correct is the key.

Here is my formula in the query (Required_Start is the project start date in the main form):
StartDate: IIf([Order]=1,[Required_Start],[Required_Start]+Nz(DSum("[T_Aggressive_duration]","qryTasks","Order<" & [Order]))+1)

Here are the results of that formula:
Order      Dur      TaskStart            Should BE:
1                5      4/17/14            
2                4      4/23/14            
3                3      4/27/14            4/28/14
4                6      4/30/14            5/2/14
5              10      5/6/14            5/9/14

You will need to post qryTasks also since the error seems to be there.

Thanks - db attached with qryTasks.
ML-Test.accdb

Any suggestions for this problem or is it not possible to do this?

I just ran your query here and it looks correct:


Jim.

After uploading the last database, could you explain what to run and what we see and what do you expect?

Hi - qryTasks is the object to run.

Here is my formula in the query (Required_Start is the project start date in the main form):
StartDate: IIf([Order]=1,[Required_Start],[Required_Start]+Nz(DSum("[T_Aggressive_duration]","qryTasks","Order<" & [Order]))+1)

Following example shows what the TaskStart is and what it should be.

Here are the results of that formula:
Order      Dur      TaskStart            Should BE:
1                5      4/17/14            
2                4      4/23/14            
3                3      4/27/14            4/28/14
4                6      4/30/14            5/2/14
5              10      5/6/14              5/9/14

Thank you so much to both of you!!  Both solutions work perfectly.  Huge relief to finally have this solved and your solutions taught me a lot.

Welcome!