Others

Design a Microsoft Access Database for Products, Orders, and Customers

Algirdas JasaitisAlgirdas Jasaitis Nov 20, 2026 870 views

Question details

Design a normalized database for a school uniform business that tracks customers, products, variations, inventory, and order line items.

Design a Microsoft Access Database for Products, Orders, and Customers
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the main tables

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.

2
Define product variations

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.

3
Add order line items

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.

4
Set relationships

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.

5
Build the order entry form

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.

6
Check data entry

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.

Check your business rules: If stock must be tracked through purchases and sales, consider a separate inventory transaction table rather than relying only on a stock quantity field.
Free Microsoft Office alternative

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.

Organize uniform products, sizes, colors, and order lists in spreadsheetsOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar interface to work with existing Office spreadsheetsGet a free, lightweight office suite
microsoft office alternative - wps office

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.