Results 1 to 10 of 10
  1. #1
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121

    Design for monthly reports and changes

    I have a database with hundred of employees. For every month employees work until their assigned end date, they get a bonus amount based on their salary/hours worked/overtime, etc. So for instance, employee 1 gets $300 in August, $320 in September and so on. In October, employee 1 terminates (prior to their assigned end date) so the Oct report would have to show a credit, -$620 for October. I am not sure now to structure the monthly data, i.e., do I write each months totals to a table/tables?

    At some point down the road I would like be able to provide this info to employees so they could see their cumulative amount or a monthly summary, i.e. for employee 1

    August $300
    September $320
    October -$620


    Net $0

    I'm simplifying this quite a bit but interested in structure suggestions

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Somewhat simplified but if you had a table for employees who are on this payment scheme (or a table for all and a field to define them as a member, or not, of this scheme) and a table for payment records, that ought to give you what you asked for.
    tblEmployee
    EmplID Fname Lname etc.
    1 Joe Black
    2 Sam White
    3 Mary Brown
    tblEmplPayment
    EmplPayID EmplIDfk PayDate Amt
    1 1 9/30/20 300
    2 2 9/30/20 300
    3 3 9/30/20 320
    4 1 10/31/20 250
    5 2 10/31/20 325
    6 3 10/31/20 300
    7 1 11/30/20 310
    8 2 11/30/20 320
    9 3 11/30/20 330

    To get summary results, create Totals query, Group By EmplID and summing Amt over a date range.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    each record will have a date.
    use a form to hold the start/end dates: txtStartDate, txtEndDate
    the base query ,Q1, will pull data for that month, inside the 2 dates:

    Q1:
    select * from table where [DateEvent] between forms!frmRpts!txtSTartDate and forms!frmRpts!txtEndDate

    Q2 will then use Q1 to chop up the data in the metrics you want.

  4. #4
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121
    Let me provide a bit more info. My customer wants a monthly report that shows the retain amount calculated from the timesheet detail table. In the month the employee terminates, I need to reverse the sum of all previous months. The employee who terminated in Oct below would then no longer appear on future month reports. How do I refer back to previous monthly data in the current report period to do this?

    Name Report Month Term Date Retain Amount
    Employee 1 Aug 280
    Sep 300
    Oct 10/9/2020 -580

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    WHERE [Term Date] Is Null ? If you want that and a particular month, add in the month criteria.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121
    Ok, I have written the results of query Q6A to a table called EmplMoAccrual so it has the results for Oct. The fields in this table are exactly as they are in query Q6A. Now I need to run Nov and if an Employee terminated in Oct, it needs to create a credit in Nov which is a reversal of the Oct Present Value amount. How do I do that? Attached is a pic of what the credit calc looks like and the fields from Q6A and/or the EmplMoAccrual table.
    Attached Files Attached Files

  7. #7
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121
    Not only Oct but all previous months..

  8. #8
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121
    Ok, I have written the results of query Q6A to a table called EmplMoAccrual so it has the results for Oct. The fields in this table are exactly as they are in query Q6A. Now I need to run Nov and if an Employee terminated in Oct, it needs to create a credit in Nov which is a reversal of the Oct Present Value amount and any other previous months. How do I do that? Attached is a pic of what the credit calc looks like and the fields from Q6A and/or the EmplMoAccrual table.

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Having had a lot of experience of commission and reward schemes, most of the time they fail because of poor or ambiguous terminology.

    So when you sayemployee 1 gets $300 in August, $320 in September do you mean they are physically paid these amounts in August and September? and deducted from salary in October? or is the amount accrued to be paid once (or twice or four times) a year, providing they are still an employee at that time?

    Assuming the latter which I suspect is the case then I would not be storing a monthly value to potentially be subsequently reversed or adjusted. Instead I would have a calculation which calculates the 'year to date' position. This can easily show monthly values if required

    From an accounting perspective, the accrued 'debt' would be handled with a reversing journal

  10. #10
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121
    Thanks Ajax, that is intriguing as I have already had to rerun query's to the monthly table after making enhancements.

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

Similar Threads

  1. Monthly and Daily Reports
    By cocapuff in forum Reports
    Replies: 1
    Last Post: 12-04-2019, 05:37 PM
  2. Weekly or monthly reports on Access
    By duddu in forum Access
    Replies: 2
    Last Post: 09-23-2017, 11:40 PM
  3. Running monthly reports
    By sunnyday in forum Reports
    Replies: 1
    Last Post: 12-23-2014, 01:33 AM
  4. Monthly Sales reports with wholesale average
    By Yarrrm8e in forum Reports
    Replies: 3
    Last Post: 01-31-2014, 04:39 PM
  5. Automated Email Reports - monthly no clicks
    By Bamber in forum Reports
    Replies: 1
    Last Post: 05-12-2010, 12:34 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