Others

How to Design a Microsoft Access Database for Tiered Sales Commissions

Khadija KhanKhadija Khan Nov 13, 2026 869 views

Question details

The user needs a normalized Microsoft Access database design to calculate tiered sales commissions for main distributors, distributors, and resellers using percentage-based or fixed commission rates across products.

How to Design a Microsoft Access Database for Tiered Sales Commissions
Product
Microsoft Access
Device & OS
not provided
Scenario
Designing a relational Access database schema for multi-level, multi-role sales commission calculation.
Observed behavior
A normalized database that stores each marketer involved in a transaction and calculates the applicable commission by product, role, quantity, and sale value.
Before you start

Before building the tables, list the commission roles (main distributor, distributor, reseller), product commission types (percentage or fixed), and the transaction fields you must report on. Then sketch how one marketer can have different roles in different transactions.

Solution 1Recommended

Design the Normalized Access Tables and Relationships for Tiered Commissions

Use this as the core setup when building a flexible commission database that supports multiple marketer roles per transaction and both percentage and fixed commission rates.

A normalized structure separates Product, Marketer, MarketerType, Transaction, TransactionMarketer, and Commission tables. This avoids repeating commission rules in every sale and lets a marketer appear in different roles in different transactions.

1
Create the supporting tables

In Microsoft Access, click File > New > Blank desktop database. Create Product, Marketer, MarketerType, Transaction, TransactionMarketer, and Commission tables in Table Design View.

2
Define primary keys and relationships

Open Database Tools > Relationships, add the tables, and link ProductID, MarketerID, MarketerTypeID, TransactionID, and TransactionMarketerID with referential integrity.

3
Store transaction and marketer roles

In Transaction, add TransactionID, ProductID, UnitPrice, Quantity, and OrderID. In TransactionMarketer, add TransMkterID, TransactionID, MarketerID, and MarketerTypeID so each marketer's role is stored per transaction.

4
Add commission fields

In Commission, add CommissionID, TransactionID, MarketerTypeID, and Commission. Use a query or calculated field to compute percentage-based or fixed commission by product, role, quantity, and sale value.

5
Test with a multi-role sale

Create a form or query and enter a transaction where a main distributor also acts as the reseller to verify that the database returns both commission amounts correctly.

Design the Normalized Access Tables and Relationships for Tiered Commissions
Flexible role handling: Normalizing the transaction-marketer relationship lets you add new commission roles without changing the transaction table.
Free Microsoft Office alternative

Use WPS Office as a Free Spreadsheet Alternative for Commission Data

WPS Office cannot open Access .accdb files or replace the Access database designer, but it is a free, lightweight Microsoft Office alternative for spreadsheet-based commission tracking. It supports .xlsx and .csv exports from Access and offers a familiar ribbon interface for a smooth migration.

  1. 1. Download WPS Office: Go to the official WPS Office download page and click Download WPS Office Free.
  2. 2. Install and launch WPS Spreadsheets: Run the installer, then open WPS Office and click Spreadsheets to create a new workbook.
  3. 3. Open or import commission data: Use Menu > Open to open your .xlsx or .csv commission file, then use formulas, filters, and pivot tables to summarize commissions.
Free, lightweight Office suite for building and editing Excel-compatible commission workbooks.Opens and saves .xlsx, .xls, and .csv files, the common Microsoft Office spreadsheet formats.Familiar ribbon interface reduces migration time from Microsoft Office.Lets you filter, sort, and summarize commission data with formulas and pivot tables in WPS Spreadsheets.
QA img-9

Frequently Asked Questions

How do I model a marketer who acts as both main distributor and reseller in the same transaction?

Add one row in the TransactionMarketer junction table for each role, using the same TransactionID and MarketerID but different MarketerTypeID values. The Commission table can then store or calculate the amount per role.

Can the same Access database handle both percentage-based and fixed commissions?

Yes. Store the commission type and rate or fixed amount in the Product or Commission table, then use an expression or VBA to apply the appropriate calculation by product and role.

What relationships should I create between the transaction and marketer tables?

Create a one-to-many relationship from Transaction to TransactionMarketer on TransactionID, and from Marketer and MarketerType to TransactionMarketer. Link Product to Transaction and Commission to Transaction or MarketerType as needed.

Why not store all commission rates in one Transaction table?

Storing all rates in one table causes repeated data and does not support a marketer having different roles per transaction. Normalized Product, MarketerType, TransactionMarketer, and Commission tables keep commission rules flexible.

Can I open an Access .accdb file in WPS Office?

No, WPS Office does not open or edit .accdb Access database files. You can use it to open and analyze exported .xlsx or .csv commission data in WPS Spreadsheets.