Results 1 to 11 of 11

Thread: timesheet query

  1. #1
    Join Date
    Oct 2007
    Posts
    21

    Unanswered: timesheet query

    I have an employee timesheet that records the employees name, time in, time out, and date. I want to make a query/report that calculates the total hours worked from the time in to the time out, and then sums the total hours worked with a date parameter for each employee. How can I do this. I have limited knowledge in access so some codes would be a big help. Thanks in advance

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Timesheet example

    This will get you started.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Oct 2007
    Posts
    21
    I'm using the same code, but for some reason its not working... take a look at my database attached
    Attached Files Attached Files

  4. #4
    Join Date
    Oct 2007
    Posts
    21
    Quote Originally Posted by fstath
    I'm using the same code, but for some reason its not working... take a look at my database attached
    What I meant was, I applied the same code but it didn't work.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You need to change the "Group By" for the date column to "Where". Otherwise Access thinks you want to Group by this field (even though you've marked it not to show). Sorry about my example where I didn't do this (I did it fairly fast.)

    Thanks for uploading an example - it makes it soo much easier to troubleshoot.
    Last edited by pkstormy; 10-29-07 at 19:18.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Also, please by all costs, avoid having any field names called: Date. Instead call it DateOfSomething or DateEntered or DateUpdated or UpdatedDate or something other than just Date.

    See the list of reserved Access names you should never call a field or a table. Access is going to complain at some point or another (and make your life miserable.)
    Last edited by pkstormy; 10-29-07 at 19:22.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Oct 2007
    Posts
    21
    I made the changes noted and did some fiddling around and got it to work... thanks!... I've been trying to solve that silly little problem for about a week now. My next step is linking the user login to the timesheet form and creating something so that only the user logging in can view/edit/delete what they have entered. Hiding the login form and recording the userid in a table and creating a query with a criteria with the user name was suggested... but I dont know where to start. I have once again attached an updated copy of the DB. Thanks
    Attached Files Attached Files

  8. #8
    Join Date
    Oct 2007
    Posts
    21
    Just when I thought all was well... I just noticed that its rounding the total hours up. Also, shouldn't the total hours be in Date/Time? just wondering.

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    fstath,

    I did this really fast so it's NOT perfected (and I only added in a small amount of code - mainly the 2 modules and the new LoginID field) but I wanted to show you how you could utilize the LoginID field and a routine to automatically get the user LoginID. Again, I only tweaked a small part, adding the LoginID to the tblEmployees and the Assignment Data table. You can disregard this example if you want but I wanted to show you one way (of several) where you could store the LoginID which is helpful for automating and setting up your forms to only show records pertaining to the loginID.

    By the way, you've got some nice looking forms.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Oct 2007
    Posts
    21
    haha, Thank you... I'm good at the cosmetic stuff. and thank you for you help. This is coming along great! I kinda see how this is working. I don't understand the abbreviated names in the LoginID or the query. Also I noticed the enter assignment form is limiting me to view only what I enter at that time, not previous logins. And would I be able to completely eliminate the combo box with the enter employee name since I have the getuser function. Maybe send that to the table instead of selecting a name?

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The abbreviated names in the LoginID field should actually be the user loginID (what the user logs in as). I entered a few 2-3 letter stuff just as an example but usually you have login names like: pkohn or ispak or ctkohn, etc...etc... The main form has a field called loginID which shows what is recognized as the actual login name utilizing the getuser function.

    I made the form a single form so that's why you're not seeing the continuous records. Sorry about the quick example but I think you get the point of how to retrieve the loginID using the getuser function.

    I try to avoid having the user select their name to login and usually automate it with the getuser function. I usually don't even have a password since the getuser function retrieves who the user is.

    Here's a better example of utilizing the getuser routine and a permissions type setup: http://www.dbforums.com/showpost.php...0&postcount=20 (look for the getuser routine in the Access Code Bank if the link doesn't work.)
    Last edited by pkstormy; 10-31-07 at 14:19.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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