Solved

# Continuous Form Calculate dates based on previous row value

Posted on 2014-03-27
587 Views
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
ML-Test.accdb
0
Question by:mlaurin
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 9
• 6
• 3
• +2

LVL 37

Expert Comment

ID: 39959308
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.
0

Author Comment

ID: 39959367
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.
0

LVL 37

Expert Comment

ID: 39959428
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.
0

Author Comment

ID: 39959559
Thanks much!!

0

LVL 37

Expert Comment

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

0

Author Comment

ID: 39960697
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?
0

LVL 37

Expert Comment

ID: 39961999
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).
0

Author Comment

ID: 39962683
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?
0

LVL 37

Expert Comment

ID: 39963207
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.
0

Author Comment

ID: 39963878
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):

Here are the results of that formula:
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
0

LVL 37

Expert Comment

ID: 39964121
You will need to post qryTasks also since the error seems to be there.
0

Author Comment

ID: 39964173
Thanks - db attached with qryTasks.
ML-Test.accdb
0

Author Comment

ID: 39973423
Any suggestions for this problem or is it not possible to do this?
0

LVL 57

Expert Comment

ID: 39981366
I just ran your query here and it looks correct:

Jim.
0

LVL 30

Expert Comment

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

Author Comment

ID: 39981936
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):

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

Here are the results of that formula:
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
0

LVL 30

Assisted Solution

hnasr earned 250 total points
ID: 39982039
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
``````
ML-Test-2.accdb
0

LVL 22

Accepted Solution

Flyster earned 250 total points
ID: 39982108
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)

Flyster
ML-TestMod.accdb
0

Author Closing Comment

ID: 39983128
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.
0

LVL 30

Expert Comment

ID: 39983232
Welcome!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and installâ€¦
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custoâ€¦
###### Suggested Courses
Course of the Month7 days, 14 hours left to enroll