Others

How to Design an Access Database for Expenses from Multiple Supplier Types

Bushra ParveenBushra Parveen Nov 4, 2026 870 views

Question details

The user needs to construct a relational database schema to manage and track expenses originating from diverse supplier categories without duplicating data or creating excessive null fields.

How to Design an Access Database for Expenses from Multiple Supplier Types
Product
Microsoft Access
Device & OS
not provided
Scenario
Tracking expenses from multiple supplier categories like regular vendors, creditors, and one-time suppliers efficiently in a single database.
Observed behavior
Setting up a relational database utilizing a primary supplier supertype table for common attributes and individual subtype tables for category-specific data.
Before you start

Before creating your tables, list out the common attributes shared by all your suppliers (like Name, Phone, and Email) and the specific attributes unique to each category (like Credit Terms for creditors or Tax ID for regular vendors).

Solution 1Recommended

Build a Supertype/Subtype Relational Schema

Create a centralized supplier table for shared data and link it to specialized subtype tables for each specific supplier category using a one-to-one relationship.

By utilizing a supertype and subtype architecture, you prevent a single, bloated table from having too many empty fields. This relational method isolates category-specific data while keeping your primary supplier list centralized and easy to query against your expenses.

1
Create the Supplier Supertype Table

Open your Access database and create a new table named 'Suppliers'. Add a primary key field named 'SupplierID' and set its data type to AutoNumber. Add fields for information common to all suppliers, such as 'SupplierName', 'ContactInfo', and a 'SupplierType' text field to identify the category.

2
Create Subtype Tables for Categories

Create separate tables for 'Vendors', 'Creditors', and 'OneTimeSuppliers'. In each of these subtype tables, add 'SupplierID' as the Primary Key, but set its data type to Number (not AutoNumber). Add the category-specific fields to each respective table.

3
Establish One-to-One Relationships

Navigate to the Database Tools tab and click 'Relationships'. Add all your newly created tables to the canvas. Click and drag the 'SupplierID' field from the main Suppliers table to the 'SupplierID' field in each of your subtype tables to form one-to-one relationships.

4
Configure the Expenses Table

Create an 'Expenses' table to record all transactions. Include an 'ExpenseID' (Primary Key, AutoNumber), 'Amount', 'TransactionDate', and a 'SupplierID' (Number). This 'SupplierID' will act as a foreign key linking every expense directly to the main Suppliers supertype table.

Build a Supertype/Subtype Relational Schema
Data Integrity: When setting up the relationships, check the box for 'Enforce Referential Integrity'. This ensures that no expense or subtype record can exist without a corresponding valid entry in the main Suppliers table.
Free Microsoft Office alternative

Simplify Your Expense Tracking with WPS Office

While Microsoft Access provides robust relational database capabilities, tracking expenses and suppliers can often be managed more intuitively using spreadsheets. WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office, allowing you to organize financial data effortlessly.

  1. 1. Download WPS Office: Visit the official WPS website and download the free installation package for your device.
  2. 2. Open a Spreadsheet: Launch WPS Spreadsheets and create a new blank workbook to serve as your expense tracker.
  3. 3. Structure Your Data: Dedicate one sheet to your supplier master list and a second sheet to your expense log, utilizing the VLOOKUP function to automatically pull supplier details.
Easily manage supplier databases and expense logs using advanced spreadsheet functions.Fully compatible with Microsoft Excel formats (.xlsx, .xls) for seamless data migration.Free, lightweight suite including Writer, Spreadsheet, Presentation, and PDF tools.Familiar user interface makes it easy to transition from Microsoft Office suites.
microsoft office alternative - wps office

Frequently Asked Questions

What is the difference between a supertype and a subtype in an Access database?

A supertype is a primary parent table that holds data attributes common to all records in a logical grouping (like a general Supplier table). Subtypes are secondary child tables that store distinct attributes specific to only certain categories of that grouping (like Creditors or One-Time Suppliers).

Why shouldn't I put all supplier information into a single table?

Placing all fields into one flat table would create numerous blank (null) fields for suppliers that do not fit into every category. Segmenting data into related subtype tables optimizes database storage space, improves query performance, and maintains a cleaner data structure.

How do I link an expense record to a specific type of supplier?

You only need to link your Expenses table to the main Supplier supertype table using the SupplierID. Because the supertype table maintains a one-to-one relationship with its subtypes, the expense is automatically connected to the specific category details through that established relationship.

Can I use Excel or WPS Spreadsheets to track multiple supplier types instead of Access?

Yes. If you do not require complex relational database features, you can easily use a spreadsheet program like WPS Spreadsheets. You can maintain one sheet for expenses and another for your supplier list, using lookup functions to connect the related data.