Results 1 to 9 of 9

Thread: Access (97)

  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access (97)

    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

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Access (97)

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access (97)

    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)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access (97)

    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

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access (97)

    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?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access (97)

    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

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Access (97)

    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?
    "Heading for the deep end"

  8. #8
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access (97)

    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

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access (97)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •