Forum Discussion
Employee Certification Tracking
I work for a company that oversees 14 school districts. i have to track employees who are pesticide certified and i want to create a database in Access; however, i have never used Access. what i want is a system so i can see all employees and how many credits they have/ need and when they have to re-certify. Each district has X amount of employees that are certified. Each employee can have one or more license categories ( up to 27 different categories). Each category has a different number of credits required to maintain that certification. each Employee has a License that has to be renewed in 3 year increments. so my data base should be able to tell me that Employee JOHN DOE, is 3A certified and need 2 more of the 6 required credits by 10/27/2023 (the renewal date) vs Employee BOB HOPE, is 3A,7, & 9 Certified and has all the credits for 3A and 7 but short 3 credits for 9 by 1/2/2026. the question is how do i make this happen?
Access is tool for creating relational database applications. That means your first task is to invest time and effort in learning what a relational database application is, and how to design one. Then you can apply that knowledge to designing the tables required for your application.
To help you get started, perhaps a higher-level look at the three components of a relational database application is in order.
- The Data component. Data is stored in a set of related tables. ALL data is stored in those tables. To create a viable set of tables, you need to understand and implement the principles of Normalization. There are many good resources for that, including this one. You should not start on the other two elements, or components, until you have this part done and get feedback on your design.
- The Interface component. Users will interact with your data through an interface. In Access, that means forms, primarily, but also reports. A form, in essence, retrieves data from a table and presents it to the user. Or, a user uses a form to provide data that is then saved to a table. Once your tables are in place, you can start deciding how to present it to users for interaction.
- The Logic component. You need to add code, written primarily in VBA, to manage the interface and to assist in managing the data. This usually happens alongside the development of the interface.
You can also study existing templates or sample databases which are similar to the one you want. There will be differences, so this is a starting point if you find one reasonably close.
Identify each of the things you need to track, such as SchoolDistricts, Employees, Certifications, Categories and so on. Then identify the attributes of those entities you need to track. Obviously names will be important, both for SchoolDistricts and for their employees. "Names" of various types will be fields in your tables.
You also need to identify relationships. For example,
SchoolDistricts HIRE Employees.
Employees TAKE Classes.
We can't tell you specifically which tables and relationships you need. It depends on your requirements.
Once you've set up a set of tables, show them to us for feedback.
- This is a VERY ambitious project for a complete newbie. Maybe too ambitious. Can you think of another simpler project to get you started with Access? You can also study the new Northwind templates. They are good showcases of what Access can do, and have lots of documentation for you to explore.
