August 2022
This database module is a mock-up that tests the concepts for a time tracking database module to use as part of a more extensive application for use in my consulting business. Ultimately, the application will be built using ASP.NET and Microsoft SQL Server, however, this module is based on Microsoft Access and VBA.
REQUIREMENTS
Initially this database needed to be able to track time based on an employee number and project number. Upon reviewing how I wanted to track the time expended on a project, I decided it was important to add another level of granularity, so I added a task requirement to the tracking schema. Therefore, the database would track an employee’s time on a project, the task worked on, and the client that project belongs to and includes these capabilities in its first version. For the next version will include the capability to add approved hours per employee number to tasks so that overages (or not) can be tracked.
Additionally, the Microsoft Access application needed to include reporting capabilities. These reporting capabilities would need to include: timesheet reports broken down by employee, timeframe, and project; invoice generation; and project management reporting showing performance on projects and their tasks.
SCHEMA DESIGN
The database design included six tables: Employees, Customers, Projects, Tasks, TimeTracking, and ProjectEmployeeAssigned.
- Employees: This table stores information relevant to employees such as an employee number, first and last names, email address, wage rate, among other things needed for describing an employee. Of most importance is the employee number which will be at the center of recording relevant information on projects and/or tasks.
- Customers: Recording customers in the database is paramount to generating invoices for the customers. Projects are owned by customers and a customer ID will be stored within the project table.
- Projects: This table stores the specifics about each project that are taken on by the company. Each record stores a project name, number, description, and the contracted customer. This table relates to the TimeTracking table so that time spent can be associated with the proper project, and with the ProjectEmployeeAssigned for future use in assigning time and employees to specific projects.
- Tasks: This table records general tasks as identified by the company based on its stated services. Tasks identified can belong to more than one project, or not be included, in a project based on its statement of work.
- TimeTracking: To record the actual time spent on a project, this table stores links to the employee, task, and project tables, and records an employee’s check in and out time through the user interface.
- ProjectEmployeeAssigned: This table is a late addition to the project with a new requirement of tracking project stats such as budgeting hours on a project and how they are applied. Essentially this table allows project managers the ability to efficiently control the time resources of their projects.
USER INTERFACE
Rather than a switchboard type interface this application uses menu with buttons type interface. This menu provides access to all the functions of the application which include adding records for each of the data types, accessing reports based on the underlying data, and the function of entering time—the main purpose of this application. The buttons use a VBA code command to open the corresponding form or report for access. Figure 2 shows the main menu and its intended functionality.
From the design phase we can see that there are several data objects that would require an interface for submitting data to the database. These objects are: Customers, Projects, Tasks, Employees, and permissions. Each form that corresponds to these objects contains table bound fields and macros to effect the CRUD functions for each data object. Figure 3 shows one example of a data entry form, this one for employees, as an exemplar of all the entry forms.
The timecard form is the central purpose of the application. This form allows an employee to scan or enter a three-tier number (Employee Number, Project Number, and Task Number) in a format of xx-xx-xx. The dashes in the number feed into the process for splitting out the different numbers in the VBA code. Once the employee enters the appropriate number, the code takes over by splitting out the three numbers and running them through SQL queries to request information from the database. Identification numbers from the Employee, Project, and Task tables are populated into fields on the form that are hidden from the view of the user as they are not necessary for the user experience. If the application finds that an employee is checked in on a project, then the “Check Out” button will be activated and the user can then check out. On the other hand if the application finds no record of the employee being checked in on the project, then the “Check In” button will be activated allowing the employee to log a time entry into the project and also adds a new record to the junction TimeTracking table. Figure 4 shows the Time Clock form filled in as it would look after entering the prescribed project identification and after the VBA code has filled in all the fields.
Finally, there is the project management form. This is a late addition to the application as is under design and development. This form would allow a project manager to specify how much time is allocated to a project and/or task. Theoretically this would give a more granular ability to manage certain aspects of a project and real time status of hours used on a project potentially saving a project time and money in the long run.
REPORTS
Currently this section of the application is under design and development. Two main reports are expected to be available from the main menu—a timecard report summary showing a specified time frame for an employee and an invoicing report.
It is envisioned that timecard report would show formats from a report showing a daily summary for an employee to a weekly, pay period, or custom time frame. Once the button is pressed for this report a new form would appear to allow a user to select the appropriate requirements for the report. Using VBA, the application would then format the report and present it for the user’s review.
The ultimate benefit I was hoping to realize with this application was the automation of invoicing for consulting purposes. By adding a timeclock function tracking time I spent on certain projects and tasks, I hoped to be able to quickly generate an invoice using a specified time range that could be printed for dissemination to clients. This function is still under design and development and will be added to a future version of the database application.
Putting together this prototype application has advanced my knowledge of Microsoft Access database development immensely. I have learned additional applications for using recordsets to manage data from many different tables and how to manipulate data for entry into a table via VBA. This database has also extended my knowledge of database design and manners for connecting database objects using relationships. I feel this project has been a success so far and I will be testing it in my own consulting work.