Hi, I am new to Access and working my way through some online courses to learn everything I can about using it. I am in the process of trying to create a database to use for my work to track monthly productivity stats and annual goals (measured quarterly). Once everything is done, my goal is to have a database where other supervisors who may have little or no knowledge of Access will be able to use various forms to add/edit information, and run reports to see the data.
A little bit about what exactly I’m wanting to do. My company has 31 different stats that we use to measure employees’ productivity each month. I want to be able to track productivity for employees for those stats, then run reports to see those stats by employee, shift, by position, by assignment, etc. We also have 5 annual goals that we track quarterly. These goals do change slightly from year to year, so they are not constant. Our goals are tracked by our fiscal year, which runs from September to August, and broken down into Q1 to Q4, Q1 being September through November. To make it even more complicated, some of our goals have to be met quarterly, some of them have to be met annually. All of the data would be entered by each shift supervisor.
Here is how I have things broken down so far for my tables. I have several reference tables to establish the breakdown of our shifts, positions, different assignments and training. For these, I have tblPositions, with columns PositionID (PK) and Position. Then I have tblShiftList, with columns ShiftID (PK) and ShiftName. Next is tblSpecializedAssignments, with AssignmentID (PK) and SpecializedAssignment columns, then tblSpecializedTraining with TrainingID (PK) and SpecializedTraining columns. Those are all reference tables that I use in tblEmployeeList. This table has EmployeeID (PK), LastName, FirstName, Position, Shift, SpecializedAssignment, SpecializedTraining, Active (checkbox) columns. The Position, Shift, SpecializedAssigment and SpecializedTraining columns are linked to the other reference tables so they are simple drop down menus.
I’m not entirely sure what the best way would be to set up my stats and goals tables would be. My thought would be to have reference tables for those, such as tblStatsList with something like columns StatID (PK) and StatName, then tblGoalsList with columns GoalID (PK) and GoalName. Then have tblStats which would have ID (PK), EmployeeID, StatID, StatValue, and Month/Year columns. In tblGoals, I would have ID (PK), EmployeeID, GoalID, GoalValue, StartDate, and EndDate columns. Since we track the goals quarterly, I’m guessing the Start and End Dates should be by quarter?
My thought is by using reference tables for so many things, I am future proofing the database so as things grow and change, it will be easier to add and change things in the database to keep up. My plan is to have forms set up so even if I move to a different part of the company, other supervisors will be able to keep things updated without needing any kind of special knowledge on Access.
Am I on the right track so far to make everything work the way I’m wanting it to work? I’m looking for any suggestions or advice on how to make this as complete and efficient as possible.
A little bit about what exactly I’m wanting to do. My company has 31 different stats that we use to measure employees’ productivity each month. I want to be able to track productivity for employees for those stats, then run reports to see those stats by employee, shift, by position, by assignment, etc. We also have 5 annual goals that we track quarterly. These goals do change slightly from year to year, so they are not constant. Our goals are tracked by our fiscal year, which runs from September to August, and broken down into Q1 to Q4, Q1 being September through November. To make it even more complicated, some of our goals have to be met quarterly, some of them have to be met annually. All of the data would be entered by each shift supervisor.
Here is how I have things broken down so far for my tables. I have several reference tables to establish the breakdown of our shifts, positions, different assignments and training. For these, I have tblPositions, with columns PositionID (PK) and Position. Then I have tblShiftList, with columns ShiftID (PK) and ShiftName. Next is tblSpecializedAssignments, with AssignmentID (PK) and SpecializedAssignment columns, then tblSpecializedTraining with TrainingID (PK) and SpecializedTraining columns. Those are all reference tables that I use in tblEmployeeList. This table has EmployeeID (PK), LastName, FirstName, Position, Shift, SpecializedAssignment, SpecializedTraining, Active (checkbox) columns. The Position, Shift, SpecializedAssigment and SpecializedTraining columns are linked to the other reference tables so they are simple drop down menus.
I’m not entirely sure what the best way would be to set up my stats and goals tables would be. My thought would be to have reference tables for those, such as tblStatsList with something like columns StatID (PK) and StatName, then tblGoalsList with columns GoalID (PK) and GoalName. Then have tblStats which would have ID (PK), EmployeeID, StatID, StatValue, and Month/Year columns. In tblGoals, I would have ID (PK), EmployeeID, GoalID, GoalValue, StartDate, and EndDate columns. Since we track the goals quarterly, I’m guessing the Start and End Dates should be by quarter?
My thought is by using reference tables for so many things, I am future proofing the database so as things grow and change, it will be easier to add and change things in the database to keep up. My plan is to have forms set up so even if I move to a different part of the company, other supervisors will be able to keep things updated without needing any kind of special knowledge on Access.
Am I on the right track so far to make everything work the way I’m wanting it to work? I’m looking for any suggestions or advice on how to make this as complete and efficient as possible.
. An even better resource is this forum, where users advise on advanced techniques not available in any books but he books are helpful to grasp basic priniciples (and some advanced also). 
