How to Design an Access Database for Items with Multiple Characteristics
Question details
The user needs to classify historical items using categories and subcategories where items can belong to multiple subcategories, and choices depend on the selected category.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Designing a relational database schema and user interface to handle many-to-many relationships and cascading category selections.
- Observed behavior
- To successfully classify items without data redundancy, implement dependent combo boxes, and properly display data in continuous subforms.
Map out your exact hierarchy of categories and subcategories on paper before building your tables to ensure your many-to-many relationships are structured correctly.
Set Up Junction Tables and Correlated Combo Boxes
Implement a many-to-many relationship model using a junction table and build a continuous subform with filtered combo boxes for data entry.
In a tree-structured hierarchy, storing both Category and Subcategory in the Items table creates data redundancy and violates the Third Normal Form. You only need to store the lowest level of the hierarchy.
Because an item can relate to multiple subcategories, and each subcategory can relate to multiple items, this represents a many-to-many relationship requiring a junction table.
Create a new junction table containing both an ItemID and a SubcategoryID. Set both of these foreign keys together as a composite primary key.
Add an 'N/A' subcategory for each main category to handle instances where an item is classified only by a broader category without a specific subcategory.
Create a single-item main form. Inside it, insert a subform configured in continuous-form view that is bound to your new junction table.
In the subform, add an unbound Category combo box and a bound SubcategoryID combo box. Configure the Row Source of the Subcategory combo box to filter its list based on the selection made in the Category combo box.
To prevent data from being hidden in rows other than the current one in the continuous subform, superimpose a bound text box over each combo box to create a hybrid control.
Manage Your Data and Documents efficiently with WPS Office
While Microsoft Access is a specialized database tool, you can handle extensive data tracking, inventory management, and reporting with WPS Office. As a free, lightweight, and highly compatible alternative, WPS Office provides powerful Spreadsheets, Writer, and Presentation applications that seamlessly replace most Microsoft Office daily workflows.
- 1. Download and Install WPS Office: Visit the official WPS Office website to download the free suite. Install it on your Windows, Mac, or Linux device.
- 2. Import Your Data: Open WPS Spreadsheets and easily import your existing CSV, XLS, or XLSX data files previously exported from your databases.
- 3. Organize and Filter: Utilize advanced formulas, pivot tables, and data validation features in WPS Spreadsheets to categorize, correlate, and manage your items efficiently.
Frequently Asked Questions
What is a junction table in Microsoft Access?
A junction table is used to resolve a many-to-many relationship between two tables in a relational database. It contains foreign keys from both related tables, which together form a composite primary key to uniquely identify each relationship.
Why do my combo boxes appear empty in a continuous subform?
Because the subform is in continuous-form view, a correlated combo box might appear empty in rows other than the currently active row. The data is still there but hidden. You can fix this by using a hybrid control, which involves placing a bound text box directly over the combo box.
What is the Third Normal Form in database design?
Third Normal Form (3NF) is a database design principle aimed at reducing data duplication. In a category hierarchy, it means storing only the lowest-level subcategory in your main records table, since storing both the broad category and subcategory creates redundant information.
How do I create a correlated or cascading combo box?
You can create a cascading combo box by setting the Row Source property of your secondary combo box (e.g., Subcategory) to an SQL query that filters its results based on the current value selected in the primary combo box (e.g., Category).
