Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    15

    Unanswered: Query to pull out 40 hours worth

    Hey guys, im currently working on a query where i have a table which indicates various job times. Now The various jobtimes range from 30 minutes to 1 hour (.30 - 1.00) is there any way possible to tell the query for this person bring me 40 hours worth??? i tried to sum the jobtimes field and then tell it to bring me 40 hours worth but that doesnt work... Any ideas??

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Not without code. The other SQL gurus here might come up with something, but I would have to process the data one at a time until the total is >=40.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jul 2009
    Posts
    15

    Code

    any idea of what sort of code i can run to process it??

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Could you supply some samples of the data that must be processed (at least the definition of the tables where they are stored) ?

    Have a nice day!

  5. #5
    Join Date
    Jul 2009
    Posts
    15

    Sure!

    I have one main table which looks at the calls and has a column named TIME which is a number ranging form .50 to 1.4 hours, that table contains names and skill ( time is based on skill ) so when im going to assign work to someone its spread evenly throughout the week based on their skill I.E. John Doe Works 40 hrs he needs to look at 40 agents whom have different call times (.50 - 1.4 hrs ) and needs to do a total of 40.....

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I am not totally clear what you want either. Please could you post up a table of data and also a table of expected results.

    To make it easy on you, pretend you want 4 hours not 40
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Mannyf50
    Now The various jobtimes range from 30 minutes to 1 hour (.30 - 1.00)...

    Well now wait a minute... There aren't 100 minutes in an hour...


    Any reason you're not simply storing minutes if that's how granular you want to be? You can always /60 later. There's also the idea of storing start and end time as true datetime datatypes and using datediff() to derive the actual time spent.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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