How to Fix Lookup Field Design Problems in Microsoft Access

Access lookup field errors usually come from table design: store keys, enforce relationships, and show brand plus product through queries or forms.

Why this breaks in Access

Independent lookups allow invalid pairs

If Brand and Product are chosen separately in the Consumption table, Access can store a product under the wrong brand. That creates false nutrition comparisons later.

Calculated fields are the wrong layer

Concatenating brand and product inside a table field often returns IDs, rejects lookup column settings, or raises type mismatch errors. Access calculated fields are limited for relational display logic.

The real issue is data modeling

The fix is to redesign the relationship between Brands, Products, and Consumption. Once the keys are correct, forms and queries can display clear text safely.

What to check first in Microsoft 365 Access

• Open each table in Design View and confirm which field is the primary key.
• In Products, verify whether one product belongs to one brand only, or can appear under multiple brands.
• Open Database Tools > Relationships and check whether the relationships are enforced.
• If Consumption stores both BrandID and ProductID, decide whether that extra BrandID is actually needed.

Model 1: when brands and products are many-to-many

Use this model only if the same product can exist under more than one brand and each brand can have multiple products. In that case, the brand-product pair must be stored as a valid combination.

Create a table that holds BrandID and ProductID together. That pair becomes the allowed catalog of branded products.

Enforce relationships

In Database Tools > Relationships, join the keys and enable referential integrity so only valid pairs can be stored.

Store the pair, not display text

Consumption should reference the approved combination, not a manually typed brand name or a concatenated text field.

Model 2: when each product belongs to one brand

This is the simpler and usually better design. If a product belongs to only one brand, the Consumption table should store only ProductID.

Use the normalized hierarchy

• Products contains a foreign key to Brands.
• Consumption contains only a foreign key to Products.
• Do not also store BrandID in Consumption, because the brand is already determined by ProductID.
• Storing both creates a transitive dependency and breaks Third Normal Form (3NF), which opens the door to update anomalies.

Why table lookup fields cause trouble

Lookup tables are useful, but table-level lookup fields in Access often hide the real stored value and make debugging harder.

• Use lookup tables to standardize values.
• Use forms, combo boxes, list boxes, and queries for friendly selection and display.
• Store only the key value that uniquely identifies the record.

• Do not rely on table lookup fields to solve relational design problems.
• Do not concatenate brand and product in a calculated table field to enforce validity.
• Do not store text descriptions when a ProductID already identifies the choice.

Store the key, display the detail

The recommended pattern is simple: store ProductID in the table, then show brand and product text in the interface.

Show multiple columns so users can see both brand and product before selecting the stored ProductID.

Display multiple columns in the dropdown. After selection, Access still stores the ProductID rather than the text.

Unbound display control

Use an unbound combo box or display control to show an extra descriptive column without changing the table schema.

Concatenated row source

Build the combined Brand + Product text in the combo box row source, not in a calculated table field.

Use queries and forms instead

Access should present the combined text through queries, forms, or reports. That keeps the schema clean and still gives users a clear selection experience.

Remove the table-level fix attempt

Open the Products table in Design View and stop using a calculated field to concatenate the brand lookup with the product description. This avoids ID display issues, unsupported lookup column references, and type mismatch errors.

Choose the correct relationship model

If one product belongs to one brand, keep BrandID in Products and store only ProductID in Consumption. If products and brands are many-to-many, create a junction table and reference the valid pair from Consumption.

Enforce the relationships in Access

Go to Database Tools > Relationships, add the relevant tables, drag the primary key to the matching foreign key, and enforce each relationship. The expected result is that Access blocks invalid brand-product combinations from being stored.

Build data entry on a form, not in the table

Create a form with an unbound combo box for Brand and a bound combo box for Product, then filter the product list so it shows only items for the selected brand. This keeps the stored value normalized while guiding users to valid choices.

Verify with a joined query

Create a query that joins Consumption to Products and Products to Brands, then display the brand name and product description together. Verification is complete when every consumption row shows the correct brand-product pairing without storing duplicate text fields.

Use WPS Office as a Free Microsoft Office Alternative

Use Word, Excel, and PPT for FREE

Free Download

WPS Office cannot repair Microsoft Access lookup-field design inside an ACCDB database. Complete that Microsoft-specific step in the original Microsoft application or account.

For everyday local files, WPS Office combines Writer, Spreadsheets, Presentation, PDF editing, and WPS AI in a free, lightweight interface. Test macros, add-ins, protected files, and cloud-only integrations before replacing a critical workflow.

Fix Lookup Field Design Problems in Microsoft Access FAQs

Why does Access show the brand ID instead of the brand name in a calculated field?

A table lookup field stores the key value, not the display text, so a calculated field often resolves the numeric ID. Build the combined text in a query, form control, or combo box row source instead.

Should the Consumption table ever store both BrandID and ProductID?

Only if your model truly requires a composite brand-product key through a junction table. If each product belongs to one brand, store only ProductID in Consumption and derive the brand through Products.

How do I verify that invalid brand-product combinations are blocked?

Open Database Tools > Relationships and confirm referential integrity is enforced on the relevant joins. Then test data entry on the form and confirm that only valid products appear for the selected brand or valid pair.

What is the safest way to let users see both brand and product during entry?

Use a form with a multi-column list box or combo box, or use an unbound brand combo that filters a bound product combo. This keeps the display clear while storing only the correct key.