Running Totals / Remaining

[burrelly]

I have created a query that calculates the number of days between 2 dates. Date1 and Date2 this uses the following:

No of Days: [Date2]-[Date1]

Problem 1:

What I want next is to create another column that does a running total of the No of Days column

Problem 2:

Now I need to take the results from Problem one and take the number away from 20.

Problem 3:

I also need the totals to group by another column called name. So the results from problem one would only calulate the results for each name for example name1 has 3 records I would only want them calculated for that name.

Name2 has 10 records so it would calculate the results based on those 10 records.

The Result:

The results should look like below.

Date1 Date2 No of Days Days Taken Days Left
1/9/2008 7/9/2008 6 6 14
1/9/2008 7/9/2008 6 12 8
1/9/2008 7/9/2008 6 18 2

Thanks in advance

 

[neileg]

Running totals are possible in a query but not easy. Do a search in these forums for lots of help.

Running totals are easy in a form. Using sorting and grouping in a form you will get what you want with no complex coding.

Be aware that 1/9/200 to 7/9/2008 is actually 7 days not 6.

 

[burrelly]

I have had a search but not sure what I am looking for. I am fairly new to access coding. Really need it in a query or table as the data is going to be used in a number of different locations as well as linked to external sources.

 

[neileg]

Try this for starters:
http://support.microsoft.com/?kbid=208714

 

[burrelly]

I have tried to follow the following: http://support.microsoft.com/kb/290136/en-us

I am unsure how to apply it to my database though. Any ideas?

 

[neileg]

I did warn you it wasn't easy! I'm afraid I'm not in a position to walk you through this due to work commitments. It's method 2 you need to be looking at.