trouble preserving data in certain date range

[punking315]

i have a simple database that tracks jobs. The problem I have is that the
materials price for jobs changes frequently. I have a query that is used as a
subform for each job. this subform is where I select the different materials
used that day with price (using a combobox).

As time goes on the materials prices change, and that changes the price of
my previous jobs values, since it is calculated on the fly by my query.

I would like to know how to store or preserve the prices as I go along.


thanks for any help.

 

[Tom van Stiphout]

On Sat, 21 Mar 2009 03:06:08 -0700, punking315

This is a classic problem and much has been written about it. You will
need to change your database design and store the price with each
OrderDetail record.

-Tom.
Microsoft Access MVP

 

[John Spencer]

At least two solutions.

One is to store the price in the table where you are recording the
materials for the job. This involves all data entry being done in a
form so you can use VBA code to get the current price from the materials
table.

Second option, store multiple records in a MaterialPrice table for each
material with a price, and an effective date range. Then you look up
prices using a purchase date and the date range in the table.

Simplest method is the first method. However, you will have to modify
the table you are using for job materials used on a specific job to
include a price field and then you will need to update all the existing
records in that table to hold the correct price.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Ken Sheridan]

You'll find an analogous example in the sample Northwind database which comes
with Access. If you take a look at the Order Details subform you'll see how
the unit price of a product is looked up from the Products table in the
AfterUpdate event procedure of the ProductID combo box, and assigned to a
unit price field in the Order Details table. Having a column for the unit
price in both tables dies not constitute redundancy as, the price changing
over time, each of the two columns is functionally dependent on the key of
its own table. Your situation is the same, with the price per material being
the equivalent of the unit price per product in Northwind.

Ken Sheridan
Stafford, England