I've taken some essential training online for Access 2016 and some additional shorter sessions re: database design, queries, forms, etc. Below is a brief description of my project and what my initial thoughts are on how to design the database. I know I’ll have more questions to ask as I move through this project, but am looking for some feedback from experienced users to make sure I’m moving in the right direction.

Goal - use data from the payroll system in the form of Excel reports in combination with Access to manage the Attendance Control Program.

The guidelines for the Attendance Control Program are listed below. Please note these must be adhered to as they are part of a legal contract.


  1. Attendance is tracked for a rolling calendar year for each employee.
  2. Employees are given 1 point for each occurrence. An occurrence will be based on the absence code they are given and the amount of time worked.
  3. If an employee works 1 or more hours of their shift, they are NOT given a point.
    1. If shift is 8 hours, then a Sick Code entry attached to an entry of 7 or more hours would = an occurrence.
    2. If shift is 12 hours, then a Sick Code entry attached to an entry of 11 or more hours would = an occurrence.

  4. Attendance reduction policy allows for:
    1. If an employee goes 2 months without an occurrence, the most recent absence occurrence is remove from the record.
    2. If an employee goes 5, then 2 more of the most recent occurrences are removed.
    3. If an employee goes a total of 7 months with an occurrence, all occurrences are removed from the record.

** days for which a point is not given, but the employee is not at work are ‘counted back in’ (i.e. Jane Does has an absence occurrence on 2/1/18. She takes vacation on 3/15/18 and 3/16/18. The next absence occurrence is 4/4/18.
(Ex: 2/1/18 to 4/1/18 = 2 months + 2 days of vacation so – the 2/1/18 occurrence would be removed from her record effective 4/3/18)

  1. Discipline is issued at the following levels:
    1. 4 points – a verbal warning (given in the form of a letter)
    2. 6 points – a written warning
    3. 8 points – 1st strike
    4. 10 points – 2nd strike
    5. 11 points – 3rd strike

  2. Unexcused and No Call No Show payroll codes will result in an immediate strike.



Database Design – here are the tables I have so far


  • Employee Data Table – (this would have to be updated weekly before processing attendance so that new employees are added. I currently have a report from the HR/Payroll system to compile this data and import into the database.
    • Employee ID (PK) – not generated by Access. This data already exists within the HR system.
    • Employee First Name
    • Employee Last Name
    • Employee Job #
    • Employee Job Title
    • Employee Schedule Code

  • Codes to Count Back In Table – a list of payroll codes that do not give an employee an occurrence but are counted back in to determine attendance reduction fall off dates. (refer to #4 above)
    • Auto-Number (PK)
    • Payroll Code

  • Negative Attendance Codes Table – a list of all payroll codes that result in a point/attendance occurrence.
    • Auto – Number(PK)
    • Payroll Code

  • Required Hours Table – used to flag all job #s and their associated schedule codes as either 8 or 12 hours shifts. This data will be used to determine points based on the requirements laid out in #3 above.
    • Job # (PK)
    • Schedule Code
    • Required Hours (either 8 or 12)


Additional Thoughts:
  • I do have a report from the payroll system that will include all payroll codes for the week, but thought this shouldn't be a table because it would actually include calculated fields after Access determines which entries will result in points. Again, this would be run weekly, imported into Access, Access would use expressions to determine which entries would incur points and then that information would be appended to all previous data for the last 12 months so that Access could determine an employee's overall point level.



Ugh...I hope this is enough information and not too much information.



I am open to any feedback regarding my project, the database, or my posting method. Thank you!