How to Design an Access Database for Expenses from Multiple Supplier Types
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.

- 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 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).
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.
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.
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.
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.
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.

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. Download WPS Office: Visit the official WPS website and download the free installation package for your device.
- 2. Open a Spreadsheet: Launch WPS Spreadsheets and create a new blank workbook to serve as your expense tracker.
- 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.

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.




