The Overtime Tracker is an application developed in Access 2013. It was designed to store and analyze the Overtime hours submitted and compensated by the employees of one client.
This app can be modified and in the real environment it is updated with more modules and features in order to satisfy multiple needs of the HR and/or Finance departments.
In this post I will present the basic features of a testing version only to make an idea of what can be done and how it is done.
Very important: the database is split into back-end (tables) and front-end (VBA, forms etc). The front-end is linked to the back-end tables. The back-end file is located on a server location where all users have read and write access. The front-end is distributed to every user and after they log in, they can alter the data from the database file in the same time. Managers can see reports in their own front-end file or connect to the database with Excel Power Query to get reports and charts with live data at any moment.
1. Before using the application the Admin must create the users with all details (Username, name, initial password, email etc) into the “Employees” table.
2. Each user must Log in with unique Username and Password. If the password is the default password, the user is asked to change it:
3. If the user is set up by the Admin as “Inactive”, he cannot access the application:
4. If the user tries to Log in with wrong username and/or password, he will receive an error:
5. The users are able to see only required data as per the Access Rights Type set up by Admin. In this testing app, the access types are as follows:
Normal: can see only their records and access data only from “Normal” section.
Teamleader: can see his records and the records of the members from the Clusters where he is Teamleader. He access only the data from sections “Normal” and “Teamleader”
Admin: can see all the records and access data from all sections
6. All users can insert records using user forms:
7. Normal users can see only their records:
7.1 Normal user cannot access Teamleader or Admin sections:
8. Teamleaders can see their own data and the data from other members of the Teams that they lead:
9. Teamleaders cannot see Admin data:
10. Admin users can see all data:
11. Admin users can see, filter and export to Excel a nice Overtime Ageing report which calculates the age of the Overtime not compensated yet (in days):
12. You can see below some samples from the “back-end” to find out how I developed this simple Access application with user Logins, Access rights, Forms and more:
– All objects (in the real environment users cannot see the objects from left pane. They see only the Main menu form !)
– Table Employees Access. How the access to forms is given considering the user type:
– Query My Clusters (for Teamleaders):
– Query Ageing (for Admins):
– Query Grouped Hours (for Admins):
– VBA Login button:
– VBA Change Password form:
– VBA Insert record Form:
– VBA My Cluster Form:
– VBA Ageing Form:
!!! Important Notes:
- All Sample Projects posts are examples of some apps created by me from beginning to end. All customer private data is hidden, the aim being to present briefly the app’s features, ideas, methods used etc.
- I am not an expert in Microsoft Access. Actually I developed this app in approximate 2 days, among other things, so it can be improved. I am interested in more complex Database applications, but this Access app was done very quick in order to satisfy a need with the free tools that I had available at that moment..
