Results 1 to 6 of 6
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Grouping Telephone Calls (Access XP)

    How would you handle calls that say start prior to midnight and end after midnight? Shouldn't your data track the day as well?
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Grouping Telephone Calls (Access XP)

    What is the format of your time data fields?
    Regards,

    Gary
    (It's been a while!)

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Telephone Calls (Access XP)

    The calls around midnight is a problem. Fortunately there were only a few. My time is in seconds. In my reports I'm using a formula from the lounge to translate it in HH:MM. CStr(Format(Int([SumOfDuration]/3600),"00")) & ":" & CStr(Format(Int(([SumOfDuration] Mod 3600)/60),"00")) & ":" & CStr(Format(Int(([SumOfDuration]/3600) Mod 60),"00"))

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Grouping Telephone Calls (Access XP)

    You can create a separate table containing the time brackets:

    <table border=1><td>BracketStart</td><td>BracketEnd</td><td align=right>00:00</td><td align=right>01:00</td><td align=right>01:00</td><td align=right>02:00</td><td align=right>...</td><td align=right>...</td><td align=right>22:00</td><td align=right>23:00</td><td align=right>23:00</td><td align=right>00:00</td></table>
    Create a query based on this table and on your call details table. Don't join them. For each call and each bracket, use

    Start <= BracketEnd And Finishing >= BracketStart

    to determine if the call falls (partly) within that bracket.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping Telephone Calls (Access XP)

    Edited by HansV - inserted <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags to preserve spacing; see [help=19

    I have a table with the call detail per location. I would like to calculate the number of calls for every hour. For example if I have a call that starts at 08h00 and last till 10h05 it needs to be listed for the bracket 08 & 09 & 10.

    In my table I have the starting time, duration & finishing time all in seconds.

    How will I set the query to populate the differant time brackets example:
    <pre> Start Duration Finishing 12:00 13:00 14:00
    Call 1 44904 7200 52104 1 1 1
    </pre>

    I don't have to much problems to set the hour brackets based on the start & end put how will I set the inbetween bracket 13:00

    Thanks for any suggestions

  6. #6
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Telephone Calls (Access XP)

    Thanks Hans & Steve

Posting Permissions

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