Results 1 to 8 of 8
  1. #1
    cobby is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2009
    Posts
    4

    Staffs monthly time sheet query calculation

    Please help me do the following calculation for each month time sheet. In my database query, I have the following fields, StaffID, StartTime, EndTime and TotalHours. TotalHours is calculated by finding the time difference between EndTime and StartTime. Now, I want to be able to calculate the sum of TotalHours for each employee every month, either by the user specifying monthly dates say; December or (1/12/2009 to 31/12/2009), January or (1/01/2009 to 31/01/2009), February or (1/02/2009 to 28/12/2009)........ I really appreciate your help.


    Regards,

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Copy the same query and then change it to an agregating query. Press the Sigma button on the right side of the design ribbon. It also says Totals.

  3. #3
    cobby is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2009
    Posts
    4
    Hi Rural,

    Thanks for replying to my question, I am having trouble locating your code or link to the code you have suggested. Please redirect me to this code. Remember I am new this forum.
    Regards,
    Cobby

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It is not *my* code...it is *yours*. Open *your* existing query in design mode and save it as another name. Now press the Totals button on the ribbon and take it from there.

  5. #5
    cobby is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2009
    Posts
    4
    Hi Rural again,
    Thanks for showing me the aggregating query, I am already using this, and my main problem that is I want to give the user options to put in the dates like 1/01/2009 to 31/01/2009 to return hours for only January hours or February 1/02/2009 to 28/02/2009 and so on. My query already calculates the time worked for each staff. Thanks in advance for your help.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Sorry, I did not understand your first post correctly. I would put the FromDate and ToDate controls on your form and then use the calendar to set the values and not enable the query button until the dates are completed properly. The query can then reference those two controls on the form.

  7. #7
    cobby is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2009
    Posts
    4
    Thanks once again, your idea seems very good, how do I go on about doing this with the calendar. Do I create extra two text boxes on my existing form or extra two fields on my table? Please explain step by step how to go on by your idea. I am struggling how to go on about this

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If a control is bound to a DateTime field, ac2007 has a "use the calendar if DateTime" property that can be set to YES.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. IIF() in a query calculation
    By bbylls in forum Queries
    Replies: 4
    Last Post: 12-03-2009, 04:42 PM
  2. Query Calculation
    By kellyd in forum Queries
    Replies: 5
    Last Post: 08-31-2009, 11:14 AM
  3. ODBC and Long Query Time
    By pdouglas in forum Access
    Replies: 0
    Last Post: 07-09-2009, 10:21 AM
  4. Time Query
    By kbsudhir in forum Queries
    Replies: 1
    Last Post: 07-06-2009, 05:19 AM
  5. crosstab query calculation
    By rutica in forum Queries
    Replies: 2
    Last Post: 04-28-2009, 09:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums