Others

How to Use a Lookup Table for Training Days in Microsoft Access

Tauseeq Magsi Oct 22, 2026 868 views

Question details

The user needs to set up a database structure in Microsoft Access where each record corresponds to one training type and its specific number of training days, without relying on an inappropriate multivalued field.

How to Use a Lookup Table for Training Days in Microsoft Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Designing a data-entry form and table structure to accurately track employee or student training days based on selected training types.
Observed behavior: The user is looking for a way to map a single training type to a specific number of days, achieving a better relational database design through lookup tables rather than multivalued fields.

Before you start

Before modifying your database structure, gather a complete list of all training types and their default required days to populate your new lookup table.

Solution 1Recommended

Create a Lookup Table and Link with a Foreign Key

Use a dedicated lookup table for training types and days, linking it to your main table via a foreign key to ensure proper database normalization.

A multivalued field is designed for storing multiple values in a single field, which models a many-to-many relationship. Because each record in your scenario only has one training type with a set number of training days, standard relational design using a lookup table is much more efficient and reliable.

Create the lookup table

Create a new table named 'TrainingRequiredDays'. Add three fields: 'TrainingTypeID' (set as AutoNumber and Primary Key), 'TypeOfTraining' (set as Short Text), and 'NumberOfTrainingDays' (set as Number).

Populate the lookup table

Switch to Datasheet View and enter your standard training types along with their corresponding required days into this new table.

Update the main table

Open your main data table in Design View. Remove any existing multivalued field for training. Add a new Number field called 'TrainingTypeID' to serve as a foreign key.

Create a Combo Box on the entry form

Open your data-entry form in Design View. Add a Combo Box bound to the new 'TrainingTypeID' field. Set its Row Source to query the 'TrainingRequiredDays' table, allowing users to select the training name while securely storing the ID behind the scenes.

Create a Lookup Table and Link with a Foreign Key

Database Best Practice: Using a foreign key and lookup table greatly simplifies querying and reporting, and keeps your Microsoft Access database properly normalized.

Free Microsoft Office alternative

Looking for a Free Alternative to Microsoft Office?

While Microsoft Access is a powerful relational database tool, many everyday data tracking, list management, and reporting tasks can be easily and efficiently managed using advanced spreadsheets. WPS Office provides a lightweight, highly compatible, and completely free alternative to the Microsoft Office suite.

Fully compatible with Microsoft Excel (.xlsx) formats for seamless data transition and sharing.Manage tables, track training days, and utilize VLOOKUP functions easily without complex database setups.Lightweight installation with a familiar, user-friendly interface that requires zero learning curve.Completely free core features to handle your everyday document, spreadsheet, and presentation needs.

Frequently Asked Questions

Why shouldn't I use a multivalued field for training days?

Multivalued fields are meant for many-to-many relationships, where you need to store multiple selections in a single field. Since your records involve only one training type and one duration per entry, a standard lookup table is more efficient, easier to query, and strictly follows database normalization rules.

How do I display the number of training days on my form after selecting the training type?

Once your combo box is set up to select the TrainingTypeID, you can display the days by including the NumberOfTrainingDays column in your combo box's Row Source. You can then reference it in an unbound text box using an expression like '=[ComboBoxName].Column(2)'.

Can I track historical changes to the required training days?

Yes. If the required training days might change in the future and you need to keep historical records accurate, you should use a junction table or an AfterUpdate macro on your combo box. This can copy the current default 'NumberOfTrainingDays' into a dedicated static field in your main table, preserving the historical value.

Office Quick Solutions