Solved

Continuous Form Calculate dates based on previous row value

Posted on 2014-03-27
21
567 Views
Last Modified: 2014-04-07
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
Comment
Question by:mlaurin
  • 9
  • 6
  • 3
  • +2
21 Comments
 
LVL 34

Expert Comment

by:PatHartman
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

by:mlaurin
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 34

Expert Comment

by:PatHartman
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

by:mlaurin
ID: 39959559
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])
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39960392
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
0
 

Author Comment

by:mlaurin
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 34

Expert Comment

by:PatHartman
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

by:mlaurin
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 34

Expert Comment

by:PatHartman
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

by:mlaurin
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):

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
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 34

Expert Comment

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

Author Comment

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

Author Comment

by:mlaurin
ID: 39973423
Any suggestions for this problem or is it not possible to do this?
0
 
LVL 57
ID: 39981366
I just ran your query here and it looks correct:

Screen shot - query output
Jim.
0
 
LVL 30

Expert Comment

by:hnasr
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

by:mlaurin
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):
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
0
 
LVL 30

Assisted Solution

by:hnasr
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
FROM qryProject INNER JOIN Tasks_tbl ON qryProject.Project_ID = Tasks_tbl.Project_ID
ORDER BY Tasks_tbl.Order;

Open in new window

ML-Test-2.accdb
0
 
LVL 22

Accepted Solution

by:
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

by:mlaurin
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

by:hnasr
ID: 39983232
Welcome!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now