Design a Microsoft Access Database for Products, Orders, and Customers
Question details
Design a normalized database for a school uniform business that tracks customers, products, variations, inventory, and order line items.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- The business needs to record customer orders for products that may come in different colors and sizes.
- Observed behavior
- The desired database should link each order to multiple product variations and let users select colors and sizes through editable forms.
List the product variations you sell and decide whether stock and current prices are tracked separately for each color and size. Identify the customer and order information you need before creating the tables.
Create normalized tables and editable order forms in Access
Use this design to connect customers and orders to specific product variations without repeating product details on every order.
In Access, use Create > Table Design to create Customers, Orders, Products, Colors, and Sizes. Give each table a primary key, and add CustomerID as a foreign key in Orders.
Create a ProductVariants table with VariantID as its primary key and ProductID, ColorID, and SizeID as foreign keys. Add the current price and stock quantity here if they differ by variation. Use an appropriate unique index to prevent duplicate product, color, and size combinations.
Create an OrderDetails table with OrderDetailID as its primary key and OrderID and VariantID as foreign keys. Add Quantity and UnitPrice fields; store the price charged on each line so later price changes do not alter past orders.
Open Database Tools > Relationships, add the tables, and link each primary key to its corresponding foreign key. Enable referential integrity where appropriate. Orders and ProductVariants should each have a one-to-many relationship with OrderDetails.
Use Create > Form Wizard to build an Orders form with an OrderDetails subform. Link the form and subform through OrderID, and add a combo box to the subform that displays product, color, and size while storing VariantID.
Open the form in Form view and try adding an order and a line item. If editing is blocked, inspect the form and subform properties for Allow Additions and Allow Edits, and check that their record sources are updatable.
Track uniform products and orders in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar interface and Microsoft Office format compatibility, making it easy to move spreadsheet data from Office. WPS Spreadsheets can organize product and order lists, but it does not replace the Access relational database, forms, or relationships.

Frequently Asked Questions
Why do I need an OrderDetails table?
An order can contain multiple product variations, and a variation can appear in multiple orders. OrderDetails resolves that many-to-many relationship by storing one record per line with OrderID, VariantID, Quantity, and UnitPrice.
How can users select a size and color without entering IDs?
Add a combo box to the OrderDetails subform. Set its row source to display the product, color, and size, and bind it to VariantID so Access stores the matching key.
Why can't I add or edit records in my subform?
In Design view, check the subform's Allow Additions and Allow Edits properties and verify that its record source is updatable. Also check the parent-child link fields and required foreign keys.
Should the order line use the product's current price?
Use the current variation price to help enter a new line, but save the actual price charged in OrderDetails.UnitPrice. This preserves the order price if the current price changes later.




