Others

How to Track Historical Product Prices and Effective Dates in Access

Ayan Masood Oct 21, 2026 868 views

Question details

The user needs a method to retain historical product prices and their effective dates in Microsoft Access rather than overwriting existing records when a price changes.

How to Track Historical Product Prices and Effective Dates in Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Updating product pricing in a database while maintaining a complete audit trail of previous prices and the dates they were active.
Observed behavior: The user wishes to implement a relational database structure that inserts new price records instead of overwriting old ones, and dynamically displays the most recent price.

Before you start

Before modifying your database structure, ensure you have backed up your Access database file to prevent accidental data loss while creating new tables and relationships.

Solution 1Recommended

Create a Related Pricing Table and Use DLookup

Separate your product details from the pricing history to maintain a comprehensive record of all price changes over time.

Overwriting prices destroys historical data. By creating a dedicated pricing table, you can store every price change as a new row. To find the current active price, you query the record with the latest effective date using domain aggregate functions.

Create a ProductPrices table

In Access, create a new table named 'ProductPrices' containing the fields ProductID, EffectiveDate, and UnitPrice. Set the combination of ProductID and EffectiveDate as a composite primary key to ensure only one price exists per product per day.

Establish table relationships

Navigate to Database Tools > Relationships. Link your main 'Products' table to the 'ProductPrices' table by establishing a one-to-many relationship using the ProductID field.

Display the current price

On your main form, use the DMax function to find the most recent EffectiveDate for a specific product, and wrap it in a DLookup function to retrieve the corresponding UnitPrice for display.

Create a price entry dialog

Build a separate dialog form specifically for entering new prices. Because queries using aggregate functions like MAX are not updatable, this dedicated form prevents locked record issues.

Insert the new price

Add a save button to the dialog form. Attach VBA code or a macro to the button's OnClick event that executes an SQL INSERT query: INSERT INTO ProductPrices (ProductID, EffectiveDate, UnitPrice) VALUES (...). Finally, requery the main form to reflect the newly inserted price.

Create a Related Pricing Table and Use DLookup

Why MAX Queries are Read-Only: In Microsoft Access, any query that uses aggregate functions (like MAX, MIN, SUM) to group records automatically becomes non-updatable. This is why you must use domain functions like DMax/DLookup for display, and a separate INSERT action for updates.

Free Microsoft Office alternative

Discover WPS Office for Your Data Management Needs

While WPS Office does not feature a relational database tool like Microsoft Access, its powerful Spreadsheet application excels at managing, analyzing, and tracking flat-file data, including pricing histories. It serves as a highly compatible, free, and lightweight alternative for your daily office tasks.

Completely free to download with a lightweight installation that won't slow down your device.Highly compatible with Microsoft Excel formats (.xlsx, .xls) for managing price lists and tabular data.Familiar user interface ensuring a smooth transition with zero learning curve.Includes advanced data filtering, VLOOKUP, and PivotTable features for tracking historical pricing efficiently.

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I just overwrite the old price in my Access table?

Overwriting the price permanently deletes the historical pricing data. This makes it impossible to audit past sales, reprint historical invoices accurately, or analyze pricing trends over time.

How do I fix the 'Recordset is not updatable' error when using MAX for prices?

Queries using aggregate functions like MAX are inherently read-only in Access. To resolve this, display the latest price using DLookup and DMax on your main form, and use a separate pop-up dialog form utilizing an INSERT INTO statement to add new price records.

Can I track price history in a spreadsheet instead of a relational database?

Yes. You can maintain a flat-file database in a spreadsheet like WPS Spreadsheet by keeping a continuous list of products, prices, and effective dates. You can then use PivotTables or lookup functions like XLOOKUP combined with MAXIFS to dynamically extract the most recent price.

Office Quick Solutions