Results 1 to 9 of 9
  1. #1
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581

    Need to find time balances and dates

    I have a problem that is hard to explain. I have leave time that needs to be calculated. This leave time needs to be taken within 1 year. It is accumulated during the year and some of the leave is take throughout the year. I need to find a way to look back and find the oldest leave date that hasn't been used yet and make sure it is used within that year. So, if I earn 8 hours 1/1/2020, 8 hours 1/20/2020, use 6 hours on 2/10/2020 and 2 hours 3/1/2020, and 1 hour 4/1/2020, I need to find out how many hours need to be used before the year of the oldest time earned date. That probably wasn't clear enough. It's hard to describe what I need to do. I don't even know where to begin. I'm hoping someone can give me a starting point and a direction. I'm stumped right now.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please show us your table(s) design and any code/sql/queries that you are working with/are developing.
    Seems you need to account for

    LeaveAccumulated
    LeaveTaken
    LeaveRelatedDate

    Much like a typical inventory

    CurrentStock = StockAcquired- StockSold

  3. #3
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I don't have any code written yet because I'm not exactly sure how to start it. The table has only a few fields that I will be using:
    TimeSheetID - Primary
    EmployeesID - Number
    SchDate - Date/Time
    CompTimeUsed - Number
    CompTimeEarned -Number

    I'm not sure where to start. If I add up the CompTimeEarned and subtract the CompTimeUsed that will give a current balance. After that, I'm stumped.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You might try creating sample scenario(s)

    EmployeeId 400 is Gustav. So far this year has accumulated 12 days of Leave and has taken 2 days. Adjust/Expand the scenario as necessary to suit your environment.

    I'm not sure how exactly you earn hours of leave, but you must and can probably give us an example or two so we can understand. It appears you have some "rules" regarding for every X hours worked, you can accumulate Y hours of leave. You can accumulate some maximum in a specific time period. You must use/take your leave earned in a specific time before some specified date.
    Good luck.

  5. #5
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Time is accumulated by holidays. Each recognized holiday is 8 hours accumulated. Leave can be taken at any increments. There is no maximum time that can be accumulated. It just has to be used within 1 year. So, if Gustav accumulated:
    1/1/20 8 hours CompTimedEarned
    1/25/20 8 hours CompTimeEarned
    2/3/20 8 hours CompTimeUsed
    3/5/20 2 hours CompTimeUsed

    1/1/20 has been used on 2/3/20. However, he only used 2 hours after that. Therefore, he has to use 6 hours by 1/25/21. I hope that makes it a little clearer. It's not easy to explain.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    1/1/20 has been used on 2/3/20. It seems to me that he used 8 hours of earned leave on 2/3/20. I think you would be working with some summary data when it comes to using leave. Much like when you pay $834.79 in local taxes. They make sure you pay and it goes to some bucket of money that represents tax collected from all tax payers. Spending is drawn from the bucket. I don't think local authorities would identify $57.88 of your specific payment went to fix a stop sign on Main Street. I agree that details would be recorded for taxes paid, but feel there would be a transaction for the sign fixing that would be paid from funds in the "bucket".

  7. #7
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The only way to do this with the stated rules, would be to loop through the leave taken and allocate it to the first leave earned that hasn't been completely allocated.
    Any leave earned but not used over 1 year old is ignored.

    This wouldn't normally be how you would handle holiday allowances.
    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 ↓↓

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    "I hope that makes it a little clearer. It's not easy to explain. "
    I suggest you try to explain to help readers understand your requirement. You have some business rules and, while they may seem complex and causing you to be stumped, it would help readers to help you.
    It seems you are using( generally) some form of FIFO, but that is used for Inventory accounting and I don't think it applies to your situation. More details will help and may get you a strategy. As Minty suggested -"This wouldn't normally be how you would handle holiday allowances."

  9. #9
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Minty, You seem to be onto something. I'm not sure how to go about it. "...to loop through the leave taken and allocate it to the first leave earned that hasn't been completely allocated."
    I agree. This isn't how I would handle it personally. But, I was given the rules.

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

Similar Threads

  1. Find a date between 2 dates
    By ArvinFx in forum Queries
    Replies: 2
    Last Post: 01-31-2020, 08:01 AM
  2. find out overlapping dates
    By HS_1 in forum Programming
    Replies: 3
    Last Post: 07-27-2018, 11:48 AM
  3. Time-series database for monthly loan balances
    By rlmax in forum Database Design
    Replies: 6
    Last Post: 03-08-2017, 09:15 AM
  4. Find 2 most recent dates
    By cmiley in forum Queries
    Replies: 17
    Last Post: 01-19-2017, 04:57 PM
  5. Find gaps in Dates
    By soldat452002 in forum Queries
    Replies: 5
    Last Post: 07-31-2016, 03:57 PM

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