Others

How to Create a Microsoft Access Database for Materials and Quotes

Olivia Miller Oct 21, 2026 868 views

Question details

The user needs to migrate a legacy Lotus Approach database to Microsoft Access, aiming to efficiently manage customers, materials, suppliers, quotes, markups, and automatically identify the lowest current supplier prices.

Building a Microsoft Access Database for Materials, Suppliers, and Quotes

Product: Microsoft Access
Device & OS: not provided
Scenario: Migrating a legacy database to a modern relational system to streamline material pricing, standardize data entry, and improve vendor quoting processes.
Observed behavior: Structuring related tables, designing forms with combo boxes for standardized entry, and writing SQL queries to retrieve the minimum material price based on effective dates.

Before you start

Before beginning the setup in Microsoft Access, export all existing records from your Lotus Approach database into a standard format like CSV or Excel to ensure a smooth data import process.

Solution 1Recommended

Set Up Related Tables for Materials, Suppliers, and Prices

Create a solid relational foundation by separating entities into dedicated tables connected by primary and foreign keys.

A well-structured relational database avoids data duplication. By creating separate tables for your materials, suppliers (vendors), and prices, you can manage complex quoting rules much more efficiently.

Create Entity Tables

Open Microsoft Access and create separate tables: tblSuppliers (VendorID as Primary Key), tblMaterials (MaterialID as Primary Key), and tblCustomers.

Create a Pricing Table

Create a table named tblPrices. Add VendorID and MaterialID as foreign keys, along with a Price field and an EffDate (Effective Date) field to track historical pricing.

Establish Relationships

Go to Database Tools > Relationships. Drag and drop the ID fields between the tables to enforce referential integrity.

Set Up Related Tables for Materials, Suppliers, and Prices

Best Practice: Always use an AutoNumber data type for your Primary Key IDs to ensure each record is completely unique.

Solution 2

Use SQL Queries to Find the Lowest Current Supplier Price

Implement a custom nested SQL query to automatically filter and display the vendor offering the lowest price for a specific material.

Solution 3

Standardize Data Entry Using Forms and Lookup Tables

Use lookup tables and combo boxes on your forms to prevent data entry errors and speed up the quoting process.

Free Microsoft Office alternative

Manage Your Supplier Data with WPS Spreadsheet

While Microsoft Access is a paid database tool, you can manage robust material lists, track supplier pricing, and calculate lowest quotes entirely for free using WPS Spreadsheet. Enjoy a familiar interface and seamless compatibility with Microsoft Excel formats.

1. Export Your Data: Export your Lotus Approach data as a CSV or Excel file.
2. Open in WPS Spreadsheet: Launch WPS Office and open your exported CSV to view your supplier and material data.
3. Apply Formulas: Use the MINIFS function to instantly identify the lowest current price for any given material ID.

Free, lightweight, and easy to use without complex database programmingFully compatible with Microsoft Excel (.xlsx and .csv) formats for easy migrationUse advanced formulas like MINIFS and VLOOKUP to easily find the lowest supplier pricesCreate PivotTables to analyze markups and customer quote margins effortlessly

microsoft office alternative - wps office

Frequently Asked Questions

How do I import my Lotus Approach data into Microsoft Access?

First, export your Lotus Approach data to a compatible format such as DBASE (.dbf), Excel (.xlsx), or CSV. Then, open Microsoft Access, go to the External Data tab, select New Data Source, and follow the import wizard for your chosen file type.

What is the best way to handle multiple prices from the same supplier over time?

Use a dedicated pricing table (e.g., tblPrices) that includes the Supplier ID, Material ID, Price, and an Effective Date. This structure allows you to track historical pricing while using MAX(EffDate) queries to pull only the most recent price.

Can I use combo boxes to hide ID numbers on Access forms?

Yes. When setting up a combo box on a form, you can adjust the Column Widths property (for example, setting the first column width to 0) to hide the primary key ID while still displaying the readable vendor or material name to the user.

Why should I use lookup tables for material categories?

Lookup tables enforce data integrity by restricting user input to a predefined list of categories. This prevents typos and inconsistencies, making your queries and reports much more accurate.

Office Quick Solutions