Design a Microsoft Access Database for Multiple Vendor Price Lists
Question details
The user needs a database design for managing and comparing price lists from more than 20 vendors.

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

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.

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.




