Answer 1

I need to update  all the rows  in a table  with a date  that increments by two months  each time.

I.e.  Row1 should have a date of 1/3/2010,  Row2 = 1/5/2010, Row3 1/7/2010 

The tricky  bit is that the user  needs to be able to enter  the row  number, so that it can start  on any row and the start date.

e.g. Input 7 and 1/9/2010 and the query  update all rows from 7 onwards  with date 1/9/2010 incrementing  each time, so row8 is 1/11/2010 etc.

I know how to add  months to a date, but I can't work  out how to make the query work on the previous  record.

 

Please help!!!

What exactly is the bigger picture that's going on? Generally speaking you store a base date in the record  and adjust it dynamically as its presented. For example and Invoice Date and then showing number  of days past due by doing the math from today's date backwards.

Also, [AND VERY IMPORTANTLY], the order in which records are stored in a database is entirely arbitrary. If you add 10 records one after the other, they may be stored in any order imaginable.

Answer 2

It sounds like a) you're working directly in tables; b) assume that Autonumber values are "row numbers"  (they're not); and c) want to store derived data. All three issues are problematic!

What's the context? What's in this table? Where is the user  going to "input 7"? If you have rows  1, 2 and 3, what about row  228123?

Answer 3

Thanks to you both.

The exact example is ....

I have a table  with a list of tasks (tbl_tasks).  Each task has an issue number  (issue) which is the key. Each task also has a date  it must be completed by (compDate). I've been using DateAdd to add  the 2 months  to the start  date, but can't work  out how to add the two months to the result from the previous.

A user  needs to be able to enter  a task number, a start date and then a period (either monthly, bi-monthly, weekly, bi-weekly) an and then automaticlly update  all the rows  from the input  task number with the start date, and then subsequent tasks with the compDate set to the previous  tasks compDate plus the period selected.

e.g. User enters Task 7, start date 1/1/2010, and bi-montly

Task7 compDate should be 1/1/2010, Task8 1/3/2010, Task9 1/5/2010 all the way to the end of the table.

I've been trying to do this use the query  function and linking two queries togther.

Answer 4

Assuming you have a table  of estimated time  for starting a task after the start  of the project, like this:

Estimated      ForTask

24                   2  ' ie, add  24 days to the start of the project to get the start ot task 2

...                  ...

111                  5 

115                  6

125                   7  ' add 125 days to the start of the project to get the start of task 7

145                   8

Assuming all the task are already in the 'running' table,

Assuming that on MODIFYING, say, task 5, you then wish to AUTOMATICALLY update  starting (ending would be similar) tasks 6, 7 and 8

It is then a matter to change date  associated to task 6 with   115-111  days after newStartingDate for task 5

and to associate task 7 with  125-111 + newStartingDate for task 5, ... etc.

So, we need two parameter, the task number  which is modified and the newStartingDate for this task.

UPDATE runningTable    ' not the one with estimated time, but the one with actual data

SET startingDate = [newStartingDate]

         + DLookup("Estimated", "estimatedTimes", "task=" & task)

         - DLookup("Estimated", "estimatedTimes", "task=" & task -1)

WHERE task > [updatedTask]