Continuous Form Calculate dates based on previous row value

I have a tricky issue I am hoping an expert can help me with!

On a continuous subform for the 1st record (Order - 1) I need the start date to refer to the main form project date, the end date should be that date + duration.
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.

I have attached a DB. qryTasks has the fields with the formulas for StartDate and EndDate.  

Below I copied in what I need to arrive at:

(Project start date is 4/17/2014              
Order      Duration           StartDate         EndDate
1                 5                   4/17/2014        4/22/2014
2                10                  4/23/2014        5/3/2014
3                 3                   5/4/2014          5/7/2014
4                 6                   5/8/2014          5/14/2014
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
mlaurinAuthor Commented:
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.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

mlaurinAuthor Commented:
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
mlaurinAuthor Commented:
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).
mlaurinAuthor Commented:
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.
mlaurinAuthor Commented:
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.
mlaurinAuthor Commented:
Thanks - db attached with qryTasks.
mlaurinAuthor Commented:
Any suggestions for this problem or is it not possible to do this?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I just ran your query here and it looks correct:

Screen shot - query output
Hamed NasrRetired IT ProfessionalCommented:
After uploading the last database, could you explain what to run and what we see and what do you expect?
mlaurinAuthor Commented:
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
Hamed NasrRetired IT ProfessionalCommented:
Try this:
SELECT Tasks_tbl.Task_ID, Tasks_tbl.Project_ID, Tasks_tbl.Task_Name, qryProject.Required_Start, Tasks_tbl.T_Aggressive_Duration AS Duration, Tasks_tbl.Order, IIf([Order]=1,[Required_Start],[Required_Start]+Nz(DSum("[T_Aggressive_duration]","Tasks_tbl","Order<" & [Order] & " AND Project_ID=" & [qryProject].[Project_ID]))+DCount("[T_Aggressive_duration]","Tasks_tbl","Order<" & [Order] & " AND Project_ID=" & [qryProject].[Project_ID])) AS StartDate, [StartDate]+[Duration] AS EndDate
FROM qryProject INNER JOIN Tasks_tbl ON qryProject.Project_ID = Tasks_tbl.Project_ID
ORDER BY Tasks_tbl.Order;

Open in new window

Here's another option. Add duration to your start date to get the end date and use DLookup to get your start date (Except when Option equals 1)


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlaurinAuthor Commented:
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.
Hamed NasrRetired IT ProfessionalCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.