Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228

    Timesheet management

    Im creating a standalone application for managing incoming timesheet data. (also developing a web app for the time sheet submissions)

    The database side of things right now is one table. Shown here:

    Click image for larger version. 

Name:	Screenshot_3.png 
Views:	55 
Size:	40.8 KB 
ID:	35788



    It can show multiple jobs on any given day and the hours/mileage associated with that job.

    I would like some guidance on how to deliver this information to users (office staff). Payroll and project managers specifically.

    Main consideration right now is:

    payroll want a weekly view. They are not concerned with a breakdown of hours. Just how many.

    In my mind the best way to do this:

    use a combination of filters, one where they can select the week ending (list of dates). Also they can select the employee name. the system then tells them the hours worked that week.

    I was thinking of applying the week ending date to every entry using some sugestions from here;

    https://forums.windowssecrets.com/sh...ed-with-a-date

    But I'm now concerned that with a lot of data this will become slow and I should apply this after the filters have been selected (it will look within a set range then do the calculations.)

    Any thoughts on anything ive mentioned are appreciated. I like to ask just as confirmation rather than spending a lot of time on a bad solution.

    Thanks, Andy.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    on a form, have 2 txt boxes , txtStartDate, txtEndDate
    enter the date for that week,
    the query will use this to sum a persons hrs. (turn on sum/grouping)

    select TNAME, WeekOf, Sum(TsHrs) as Hrs from table where TsDate between forms!myForm!txtStartDate and forms!myForm!txtEndDate

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    no need to store the weekending date, you can calculate weekending easily enough on the fly using the weekday function

    To give you a list of weekendings to use in your combo

    SELECT DISTINCT TSDate+7-weekday(TSDate) AS WeekEndings
    FROM myTable
    ORDER BY TSDate+7-weekday(TSDate) Desc

    -- returns the last Saturday if TSDate is between the previous sunday and saturday

    not sure what you consider to be a lot of data but I would use the indexed date field (TSDate) in your criteria rather than determining a calculated value which won't be indexed

    TSDate BETWEEN [Enter Weekend Date]-7 and [Enter Weekend Date]

    will give you the weeks records and will be faster than

    TSDate+7-weekday(TSDate)=[Enter Weekend Date]

    If this is for payroll, you need a loop back from payroll to confirm what has been paid (hours and mileage - rates shouldn't matter, that is someone else's problem) so you can mark off what has been paid so you can exclude those records if the report is run again (or at least have the option to exclude them)

  4. #4
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Appreciate the replies, some good advice there. Hopefully this is pretty straight forward.

  5. #5
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Hi again,

    I've encountered a problem.

    "TSDate+7-weekday(TSDate)" will also apply to a date that is already a saterday. I do have a work around but wondering if you have a better solution.

    I am planning on storying the week ending date on the record as they come in. I understand i dont have to but for me it will make things easier.

    So, I was thinking to populate the weekending date field where there is a match for a "saterday" (or sunday in this case) then apply your expression to all the null fields.

    Basically excluding the ones that are on a weekennding date already. Unless you have a more elegent solution?

    Thanks. Andy.

  6. #6
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I really wouldn't store the weekend date, it's superfluous, and if someone corrects an entry somewhere in the system, you then have to capture that event and update your static weekend data.

    I'm slightly confused by your description of "TSDate+7-weekday(TSDate)" will apply if it's a Saturday?
    What defines a week period within your business Monday to Sunday? Sunday to Saturday?

    Whatever it is you can create a suitable function to work out the week the event happened and be consistent throughout your application.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I appreciate what you are saying but I wont be making approved data editable to anyone but me. In which case I'll have to be careful. Only reason I want to store it is because if I dont I will need more complex queries for project managment costs.

    I'll be using two near identical tables. One is where entered data goes. Ill have some queries to check its not been entered before and update the employee ID and then apply the week ending date. Once it's all correct it can be appended to the main table and the import table is deleted of all data. (not set in stone, just how its going right now)

    There is some confusion between saterday and sunday. Our work week ends on sunday and also includes any work done on that sunday. The code supplied by ajax gives the following saterday (which is fine I've amended it) But thats why I was mentioning saterdays.

    Now back to the problem, the code here:
    Code:
    TSDate+8-weekday(TSDate)
    If the date is already a sunday... this code finds the next sunday. I need it to remain as the date it already is.

    Any thoughts?

    edit: I've changed it to 8 from 7. maybe thats the problem... (testing)

  8. #8
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Code:
    [TSDate]+7-Weekday([TSDate]-1)
    has resolved the issue. I understand why I cant add a day to TSDate at the start. Thats why it was knocking it into the next week. Ive just added the day to the output to show the sunday rather than the saterday.

    Thanks, Andy.

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

Similar Threads

  1. Timesheet problem
    By Peter Simpson in forum Queries
    Replies: 2
    Last Post: 11-14-2017, 07:47 PM
  2. Need help in timesheet application
    By shod90 in forum Forms
    Replies: 7
    Last Post: 01-04-2016, 11:05 AM
  3. Timesheet problem
    By vincentsp in forum Access
    Replies: 1
    Last Post: 01-27-2015, 11:50 AM
  4. Design a timesheet application
    By Trevi in forum Database Design
    Replies: 1
    Last Post: 11-04-2010, 11:25 AM
  5. Timesheet template
    By meso in forum Access
    Replies: 0
    Last Post: 07-27-2009, 05:28 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