Access (97)

[WTH]

Hi

Here's the sample data:
Personid Start End
5 04/04/2000 15/04/2000
5 10/04/2000 01/05/2000
5 12/04/2000 01/05/2000
5 01/06/2000 10/06/2000
6 07/06/2000 12/06/2000
6 16/06/2000 23/06/2000

Here's the problem. I need to determine the net number of days the submission was out with each person for a report. The answer should be for 36 days for personid 5 and 12 days for personid 6.

My attempts using for next loops do not get the correct amount. I thought this would be relatively simple, but for me obviously not.

Any help appreciated.

Thanks
WTH

[rory]

Hi,
I may be being a bit dense here, but can you explain how you get 36 days for personid 5? I had assumed you wanted the total of Enddate-Startdate for each person but by my reckoning that comes to 60 days for personid5 and 12 days for personid 6.
If that is what you wanted, you need a query that groups on person id and sums a calculated field of EndDate-Startdate.
Hope that helps.

[MarkLiquorman]

Use the DateDiff function to calculate the # of days between the 2 dates, then Sum the amounts. If this is a report, I'd put the DateDiff into a field in my query, something like this:
DaysOut=DateDiff("d",[FirstDate],[SecondDate])

Depending on situation, you might want to add +1 to the above. If an item goes out today and comes back tomorrow, is that 1 day or 2?

Then, in a control in report group footer, set controlsource to:
=Sum(DaysOut)

[WTH]

Hi

Thanks for the assistence.

To explain further. The start and end dates are primarily there for tracking correspondence on a submission with a person. Therefore, from a time tracking point view it is the net total time it was with the person, rather than individual sum of dates for each correspondence. This figure is subtracted from the total time a submission was in the system to give net time. By using a simple sum of 60days givens a false figure from a time line point of view, while the 36 day figure is correct. A simple minimum startdate and maximum enddate will not work as indicated by the sample data for personid 6.

I hope this makes things clearer.

Regards
WTH

[MarkLiquorman]

6 07/06/2000 12/06/2000
6 16/06/2000 23/06/2000

This is what you showed before for person #6. The DateDiff for the 1st line is 5 days, and for the 2nd line is 7 days. The total is 12 days. What is the problem?

[WTH]

Hi

Yes that is correct, but the simple summing won't work for personid5 as you get a total of 60days and not 36 days as I require. The comment about mins and maxs using personid6 was to forestall the suggestion that it would be ok to use this method.

I'm obviously not explaining myself well enough! Lets try a different example.

Application opened on 4 June 2001 and closed 25 June 2001. Total time is 21 days. In between those times person A was tracked with the application for the following periods:

5 June to 20 June 2001
7 June to 11 June 2001
13 June to 23 June 2001

By using a simple sum method, the application was with person A for 29 days. So using these figures, working out net time the application was in the system excluding person A is

[weyrman]

Question??

If I read what you are saying correctly, how can a submission go out to a person again before it comes back?

if A has it from 5/6/01 to 20/6/01, how can A also have it from 13/6/01 to 23/6/01 as well?

[WTH]

Hi

Remembering what my earlier post said about tracking of correspondence. So, we may have to write to the person again, before they have responded the first time, about some other aspect of the submission. Therefore, we have overlapping correspondence with the same person. Because our business group also wish to use this tracking system for statistical purposes as alluded to in my last post, then I am left with the current problem.


Regards
WTH

[MarkLiquorman]

I don't see any easy way to do this. You will have to write a function that passes the personID. Within the function, you will somehow have to examine each sequence to see where there is overlap with prior sequence. The function will then return the number of days. This is not something that can be solved in a couple of minutes; it will take some heavy duty coding.