Results 1 to 11 of 11
  1. #1
    Peter Simpson is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    83

    Calculating Annual Leave Days accrued

    Hi Guys



    Please can you help me with the formula for a query that calculates an employees leave every month?

    EG:
    MonthID AccruedLeave
    January 2017 1
    February 2017 2
    March 2017 3
    April 2017 4

    Idealy I want access to automatically update the AccruedLeave field in the query every month (I suppose he correct terminology is Autopopulate)

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Ideally, calculated data, especially aggregate data, would not be saved, it would be calculated when needed.

    Enter transaction records of leave earned and leave used and calculate net balance.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Peter Simpson is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    83
    Ok Thank you

    I have created the query

    Click image for larger version. 

Name:	Annual Leave Qry.jpg 
Views:	17 
Size:	67.5 KB 
ID:	31254

    I have created the report

    Click image for larger version. 

Name:	Annual Leave Rpt.jpg 
Views:	17 
Size:	34.0 KB 
ID:	31255

    The problem lies in that when if I "Group By Sum" in the query, I still get no results in the report. What am I doing wrong?

    Click image for larger version. 

Name:	Annual Leave Qry.jpg 
Views:	17 
Size:	62.9 KB 
ID:	31257

    Click image for larger version. 

Name:	Annual Leave Rpt.jpg 
Views:	17 
Size:	32.3 KB 
ID:	31258

    I also now get asked about a parameter

    Click image for larger version. 

Name:	Paramater.jpg 
Views:	17 
Size:	27.3 KB 
ID:	31259

    What am I doing wrong? Please help

  4. #4
    Peter Simpson is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    83
    Eurika...I solved it

  5. #5
    poleacre is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    11
    How did you solve it ?

    Regards Kevin

  6. #6
    Peter Simpson is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    83
    To which part of the problem are you referring to...I had a multitude of them working this one out.

    with regards to the accumulating the leave days, I used the sum function on the report in the Layout View, the manipulated the design view to accommodate the changes

  7. #7
    poleacre is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    11
    I'm trying to do something similair with Sum , i need to get the sum of several fields in the same query or report : E.G
    Click image for larger version. 

Name:	Screen Shot 2017-11-16 at 13.43.59.png 
Views:	15 
Size:	15.2 KB 
ID:	31298

    I need to query this table and produce

    Click image for larger version. 

Name:	Screen Shot 2017-11-16 at 13.47.37.png 
Views:	14 
Size:	16.8 KB 
ID:	31299

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Build report. Use Sorting & Grouping features. Do Sum() calcs in group footer section. Expression for Current_Stock would be like:

    =Sum([Stock-in]) - Sum([Stock-out])

    Next time, start your own thread with your question. What you did is 'hijack' a thread. Tagging onto an existing thread means your question gets less review and if it is the first reply the original question no longer has 'unanswered' status and also gets less attention. This thread was already marked solved. Provide a link to existing thread in your question if you think it would be helpful to readers in understanding your situation.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    poleacre is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    11
    Thanks , ill raise a seperate query .

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No need if the answer resolves your issue.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Peter Simpson is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    83
    Run your report from your query and do your calculations in the report layout view

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

Similar Threads

  1. syntax for annual leave roster
    By gint32 in forum Programming
    Replies: 25
    Last Post: 03-08-2015, 04:07 PM
  2. Replies: 8
    Last Post: 07-07-2014, 11:21 AM
  3. Annual Leave(vacation) Relationships
    By Rach in forum Access
    Replies: 1
    Last Post: 01-26-2013, 12:45 PM
  4. Calculating days & Time
    By siapliw in forum Forms
    Replies: 5
    Last Post: 09-11-2012, 11:44 AM
  5. Annual Leave Planner
    By Dexter in forum Access
    Replies: 1
    Last Post: 03-01-2011, 05:00 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