running sum in decending order

At present the SQL statement includes the expression VAL(MID(Task,INSTR(Task," ")+1)) which parses the Task column's values to return an integer number.  It first uses the INSTR function to get the position of the space character.  This, plus 1, is then used as the second argument of the MID function to return the remainder of the Task value after the space, i.e. the numeric characters, as a string expression.  Finally the VAL function returns this as a number data type which is capable of sorting numerically, unlike a string expression composed of numeric digits.

In the WHERE clause the number returned by the above expression is then compared with the numbers in all other rows with the same Job # value, counting the rows where the number is equal to or greater than the number returned in the current row.  This gives the TimeRemaing value per row.

The cumbersome expression could be avoided if the table contained sequential numbers of each task per Job #.  If you add a column named tasknumber, of long integer number data type (which is the default number data type) to the table, then the column can be populated with the following UPDATE query:

UPDATE Tasks

SET TaskNumber = VAL(MID(Task,INSTR(Task," ")+1));

Which would number the tasks per job from 1 to 10 with the sample data you posted.  The query to return the TimeRemaining values can then be simplified to:

SELECT [Job #], task, tasknumber, duration, start, finish,

DSUM("Duration", "Tasks","[Job #] = """ & [Job #] & """

 AND tasknumber >= " & tasknumber)

 AS Timeremaining

FROM Tasks

ORDER BY [Job #],tasknumber;

Domain functions like the DSum function are notoriously slow compared with SQL operators like the SUM operator.  The latter could be used in the query, but the result table would not be updatable, so the query would be unsuitable for use as a form's RecordSource property where you need to enter or edit data in the form.  An alternative would be to remove the DSum function call from the query altogether:

SELECT [Job #], task, tasknumber, duration, start, finish

FROM Tasks

ORDER BY [Job #],tasknumber;

and add an unbound text box control to the form, with a ControlSource property of:

=DSum("Duration", "Tasks","[Job #] = """ & [Job #] & """ And TaskNumber >= " & [TaskNumber])

The form will be a little slow to refresh the unbound text box as you scroll through records, but it should be acceptable.

Ken,

That's for the response.  I tried your solution and strangely enough it works for small data set by not a large data set.  Small was 20 records and large was 1120 records.  With the large data set the field 'TimeRemaining' came back with an #Error response.

Also would like to better understand the code you suggested to make sure I understand what it is doing if you have the time.  Thanks.

The following, by calling the VBA DSum function, will return an updatable result table:

SELECT [Job #], Task, Duration, start,Finish,

DSUM("Duration", "Tasks","[Job #] = """ & [Job #] & """

 AND VAL(MID(Task,INSTR(Task,"" "")+1)) >= " & VAL(Mid(Task,INSTR(Task," ")+1)))

 AS TimeRemaining

FROM Tasks

ORDER BY [Job #],VAL(MID(Task,INSTR(Task," ")+1));

where Tasks is the name of the table.  A far more efficient solution would be to join two instances of the table, but this would return a non-updatable result table.  For examples see Balances.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

The example provided was only an example.  Each task I use is unique.  I do have a separate column called sequence number as you've suggested.  My real issue is having access calculate the running total in descending order.  Not only would I like to understand how to calculate the running total of the duration in descending order I'd also like to be able to alter the duration and have the running update.  

So can the field 'time remaining' for task # 9 be the sum of task 10 duration + task duration 9?  And can the field 'time remaining' for task # 8 be the sum of task 10 duration + task duration 9 + task duration 8 and so on up to task #1?

One problem I can see is that the Task column is ascending to humans, but not to computers. They would sort:

Task 1

Task 10

Task 2

as you well know. So the question cannot be answered with the current data set, unless we go to extremes like splitting the number off the rest of the task name, but assuming this is sample data that would likely not work in the real world.

You need to add a column TaskSequentialNumber. It does not have to go 1 -> 10, but it should be ascending (I would leave gaps of 10 so I can insert other tasks in the middle without renumbering).