Others

How to Design an Access Database for Employees and Projects

Phi Hung Vo Sep 25, 2026 873 views

Question details

Create a relational database interface that allows users to select an employee, view their related projects, choose a project to see associated contracts, and review negotiation details.

How to Design an Access Database for Employees, Projects, and Contracts

Product: Microsoft Access
Device & OS: not provided
Scenario: Building a user-friendly form interface to navigate hierarchical relational data involving employees, projects, and contracts.
Observed behavior: The user needs a structured method to display interconnected database tables interactively using selections to filter subsequent data views.

Before you start

Ensure your database tables (Employees, Projects, Contracts, Negotiations) are already created with properly defined primary keys and foreign keys to establish relational integrity.

Solution 1Recommended

Use Cascading Combo Boxes and Subforms

This method utilizes interconnected combo boxes and VBA AfterUpdate events to dynamically filter projects and contracts based on the selected employee.

By setting up cascading combo boxes on a main parent form, you can force each dropdown to filter its options based on the selection made in the previous dropdown. Subforms can then display detailed records.

Create the Parent Form

Open your Access database and create a new form in single-form view. Set the form's RecordSource to a query that will eventually be filtered by the selected contract.

Add Cascading Combo Boxes

Add three combo boxes to the header: Employee, Project, and Contract. Modify their properties to hide the ID column (set Column Count to 2, and Column Widths to 0cm;3cm) so users see names instead of numbers.

Configure Row Sources

Set the Employee combo to return EmployeeID. Set the Project combo's Row Source to an SQL query that filters by the selected EmployeeID. Set the Contract combo to filter by the selected ProjectID.

Add VBA AfterUpdate Events

Right-click the Employee combo box, select Build Event, and choose Code Builder. Write VBA code (e.g., Me.cboProject.Requery) to requery the Project combo box whenever a new employee is selected. Repeat this for the Project and Contract combo boxes to clear lower-level selections and refresh the main form.

Embed Negotiation Subforms

Drag and drop the NegotiationDetails form onto the main form to create a subform. Ensure the Link Master Fields and Link Child Fields are correctly mapped to the active ContractID.

Data Integrity: Cascading combo boxes prevent users from selecting a contract that doesn't belong to the chosen project, significantly reducing data entry errors.

Solution 2

Organize Data Views with Tab Controls

For complex databases with multiple related tables, using a tabbed interface keeps the form layout clean and intuitive without overwhelming the user.

Free Microsoft Office alternative

Manage Employee and Project Data with WPS Spreadsheet

While WPS Office does not include a direct relational database tool like Access, WPS Spreadsheet is an exceptional, free alternative for building robust flat-file databases, tracking projects, and managing employee lists with cascading dropdowns.

1. Download and Install: Download WPS Office for free from the official website and launch the application.
2. Create a Project Tracker: Open WPS Spreadsheet and structure your columns for Employees, Projects, Contracts, and Negotiations.
3. Apply Data Validation: Navigate to the Data tab and use 'Data Validation' to create dynamic dropdown lists to easily track and assign projects to employees.

Highly compatible with Microsoft Excel (.xlsx) formats for easy data migrationSupports Data Validation and INDIRECT functions to create cascading dropdown listsLightweight, fast, and completely free to use without subscription feesFamiliar tabbed interface requiring zero learning curve for Office users

Frequently Asked Questions

How do I hide the ID column in an Access combo box?

In the combo box Property Sheet, go to the Format tab. Set the Column Count to 2 (or more to match your query fields). Then, set the Column Widths to '0in;1in' (or '0cm;2.5cm'). This hides the ID in the first column while displaying the descriptive text in the second.

Why aren't my subforms updating when I select a new record?

This usually occurs if the Link Master Fields and Link Child Fields properties are not properly defined on the subform control. Also, ensure you are triggering an AfterUpdate event on your selection dropdown to requery the form.

Can I make cascading dropdown lists without VBA?

In Microsoft Access, cascading combo boxes typically require a minimal amount of VBA (using the Requery method) or a Macro in the AfterUpdate event to refresh the dependent dropdown lists when the parent selection changes.

Is it better to use a single form or continuous forms for related data?

It depends on the workflow. A single-form parent with continuous or datasheet subforms is ideal for one-to-many relationships, such as one Employee having multiple Projects.

Office Quick Solutions