Others

Design a Microsoft Access Database for Multiple Vendor Price Lists

Steve KSteve K Nov 14, 2026 868 views

Question details

The user needs a database design for managing and comparing price lists from more than 20 vendors.

Design a Microsoft Access Database for Multiple Vendor Price Lists
Product
Microsoft Access
Device & OS
not provided
Scenario
Store vendor prices in a structure that supports SKU searches, price comparisons, updates, and reporting.
Observed behavior
A shared pricing table linked to vendor and item records would allow prices to be queried without searching separate tables for each vendor.
Before you start

List the vendor identifiers, SKUs, prices, and any effective dates available in your existing price lists. Decide whether you need only the current price or a history of price changes for each vendor and SKU.

Solution 1Recommended

Create linked Vendor, Item, and Pricing tables in Access

Use one normalized pricing table to store prices from all vendors and make comparisons easier.

The Pricing table connects vendors and items: a vendor can price many items, and an item can have prices from many vendors. Store each vendor-SKU price as a row rather than creating a separate table for each vendor.

For current prices only, make VendorID and SKU a composite primary key. If you keep price history, include EffectiveDate in the composite key so that a vendor and SKU can have prices on different dates.

1
Create the vendor and item tables

In Access, use Create > Table Design to create a Vendor table with VendorID as its primary key and a VendorName field. Create an Item table with SKU as its primary key and fields such as ItemName.

2
Create the pricing table

Use Create > Table Design to create a Pricing table with VendorID, SKU, and UnitPrice fields. Match the VendorID and SKU data types to their corresponding fields in the Vendor and Item tables, and use the Currency data type for UnitPrice.

3
Set a composite primary key

In Pricing table Design View, select the VendorID and SKU rows, then click Primary Key. If you need price history, add an EffectiveDate field and select all three rows before clicking Primary Key.

4
Define the relationships

Open Database Tools > Relationships, add the three tables, and link Vendor.VendorID to Pricing.VendorID and Item.SKU to Pricing.SKU. Select Enforce Referential Integrity when creating each relationship.

5
Consolidate existing prices

Use Create > Query Design to build append queries that map each existing vendor price list into the Pricing fields. Check vendor identifiers, SKUs, and duplicate key values before running each append query.

6
Build a comparison query

Select Create > Query Design and add Vendor and Pricing. Include VendorName, SKU, and UnitPrice, enter a SKU or [Enter SKU:] in the SKU Criteria row, and sort UnitPrice in ascending order.

Create linked Vendor, Item, and Pricing tables in Access
Comparing historical prices: If Pricing includes EffectiveDate, a basic SKU query can return older prices as well as current ones. Add date criteria or a query that selects the latest applicable date for each vendor and SKU.
Free Microsoft Office alternative

Review Vendor Price Lists in WPS Spreadsheets

WPS Office is a free, lightweight alternative for working with Office documents in a familiar interface. Open compatible Excel price lists to review and organize vendor data as you migrate, but use Access or another database system for relational tables and queries.

Review and organize vendor price list data in spreadsheets.Open and edit Microsoft Excel formats, including XLS and XLSX.Use a familiar spreadsheet interface with tools for sorting and filtering.Work with a free, lightweight Office alternative.
microsoft office alternative - wps office

Frequently Asked Questions

Why should I use one pricing table instead of a table for each vendor?

A shared Pricing table stores the vendor identifier alongside each SKU and price, so one Access query can search or compare prices across vendors. Adding a vendor then requires records rather than a new table.

What should the primary key be if prices change over time?

If you keep price history, use VendorID, SKU, and EffectiveDate as a composite primary key. In table Design View, select those three field rows and click Primary Key.

How can I find the lowest vendor price for a SKU?

Create a query with VendorName, SKU, and UnitPrice, filter the SKU field, and sort UnitPrice in ascending order. If you store price history, restrict the results to the latest applicable price for each vendor and SKU first.

Can WPS Spreadsheets replace the Access database in this design?

WPS Spreadsheets can open and organize Excel price lists, but it does not replace the Access relational tables, referential integrity, and database queries described here.