Others

How to Design a Microsoft Access Database for Employee Project Hours

Partner Editor Sep 30, 2026 869 views

Question details

The user needs to structure a database to track employee working hours across different projects and years without relying on inefficient spreadsheet-like tables.

How to Design a Microsoft Access Database for Employee Project Hours

Product: Microsoft Access
Device & OS: not provided
Scenario: Tracking employee hours by project and year while migrating from an unnormalized structure (employees as rows, projects as columns, separate tables per year) to a proper relational database.
Observed behavior: The goal is to implement standard database normalization principles, establish correct primary keys, and use composite unique indexes to ensure data integrity and efficient querying.

Before you start

Before creating your new database structure, map out the specific data fields you currently have and ensure you back up any existing unnormalized tables before migrating their data.

Solution 1Recommended

Normalize the Database into Three Relational Tables

Restructure your data into separate, specialized tables (Employees, Projects, and ProjectHours) to eliminate repeating groups and allow infinite scalability across multiple years.

In relational database design, storing projects as columns (Project 1, Project 2) or creating a new table for every year is considered poor practice because it creates repeating groups. This approach requires constant structural changes whenever a new project or year is added.

To resolve this, you must normalize the database. This involves creating centralized dimension tables for your core entities (Employees and Projects) and a fact table (ProjectHours) to record the intersections.

Create the Employees Table

In Access, go to the Create tab and select Table Design. Create a field named EmployeeID and set its Data Type to AutoNumber. Right-click the field and select Primary Key. Add other fields like FirstName, LastName, and Department. Save the table as 'Employees'.

Create the Projects Table

Create another table in Design View. Add a field named ProjectID (AutoNumber) and designate it as the Primary Key. Add fields such as ProjectName and ClientName. Save this table as 'Projects'.

Create the ProjectHours (Time Tracking) Table

Create a third table to store the daily or yearly entries. Add fields for ProjectHourID (AutoNumber, Primary Key), EmployeeID (Number), ProjectID (Number), WorkDate or Year (Date/Time or Number), and Hours (Number). This table will hold one record for each employee's time logged on a specific project for a given period.

Set Up a Composite Unique Index

To prevent accidental duplicate entries for the same employee, project, and date, open the ProjectHours table in Design View. Click 'Indexes' on the Design ribbon. In the Index Name column, type 'UniqueEntry'. Under Field Name, select EmployeeID, then on the next two rows select ProjectID and WorkDate. In the Index Properties for 'UniqueEntry', set 'Unique' to Yes.

Establish Relationships

Navigate to Database Tools > Relationships. Add all three tables to the workspace. Drag the EmployeeID from the Employees table to the EmployeeID field in the ProjectHours table, and check 'Enforce Referential Integrity'. Repeat this process for ProjectID between the Projects and ProjectHours tables.

Normalize the Database into Three Relational Tables

Design Best Practice: Never use an employee's name as a primary key, as names can duplicate or change. Always use a unique system-generated identifier like an AutoNumber EmployeeID.

Free Microsoft Office alternative

Looking for a Simpler Way to Track Project Hours?

Building and maintaining a custom relational database in Microsoft Access can have a steep learning curve and requires a paid subscription. If you want a straightforward, no-code solution to track employee project hours, WPS Office is an excellent free Microsoft Office alternative. With WPS Spreadsheet, you can easily log project hours and use PivotTables to instantly generate comprehensive reports by year, project, and employee without complex database programming.

1. Set up a consolidated tracking log: Open WPS Spreadsheet and create a single flat table with columns for Date, Employee ID, Employee Name, Project Name, and Hours Worked. Record each entry as a new row.
2. Insert a PivotTable for analysis: Highlight your data table, navigate to the Insert tab, and select PivotTable. This powerful tool will allow you to cross-reference data automatically.
3. Summarize hours by project and year: Drag 'Employee Name' to Rows, 'Project Name' or 'Date (Year)' to Columns, and 'Hours Worked' to the Values quadrant to instantly view dynamic, summarized project hours.

Completely free, lightweight, and fast office suite with no forced subscriptionSeamless compatibility with Microsoft Excel formats (.xlsx, .xls) for easy team sharingFamiliar user interface that ensures a smooth and instant migration from Microsoft OfficePowerful PivotTable and filtering features to easily summarize employee hours by project and year

Frequently Asked Questions

Why shouldn't I create a separate Access table for each year?

Creating separate tables for each year fragments your data. It makes querying historical trends difficult, requires you to update the database structure annually, and forces you to build complex Union queries just to summarize total employee hours across multiple years.

Can I use an employee's name as a primary key?

No. Employee names are not guaranteed to be unique (you might hire two people named John Smith), and names can change due to marriage or other reasons. A Primary Key must be unique and immutable, which is why a numeric EmployeeID is the standard best practice.

What are repeating groups in database design?

Repeating groups occur when a table contains multiple columns for the same type of data, such as 'Project 1 Hours', 'Project 2 Hours', and 'Project 3 Hours'. This violates First Normal Form (1NF). The correct approach is to move this data into separate rows within a related child table.

What is a composite index and why do I need it for time tracking?

A composite index applies a rule across multiple columns at once. In a project hours database, creating a unique composite index across EmployeeID, ProjectID, and Date ensures that the same employee cannot accidentally have two overlapping time entries logged for the exact same project on the exact same day.

Office Quick Solutions