freezing data at a certain date

[pat67]

I am using Access to track on time delivery. I have a query set up to
use the table of received orders I imported and a table of lead times.
The problem was if a lead time changed from say 60 days to 120 days,
it was globally affected. I since set up a new table with updated lead
times and a date in which they were updated. I then used an if
statement to look at the updated lead time only if the order was
placed after the corresponding date. I recently updated the lead times
again and now some of the parts occur twice in the updated table and
therefore I am getting duplicates. How can I resolve this issue?

 

[Piet Linden]

I am using Access to track on time delivery. I have a query set up to
use the table of received orders I imported and a table of lead times.

The problem was if a lead time changed from say 60 days to 120 days,
it was globally affected.

I since set up a new table with updated lead

times and a date in which they were updated. I then used an if
statement to look at the updated lead time only if the order was
placed after the corresponding date. I recently updated the lead times
again and now some of the parts occur twice in the updated table and
therefore I am getting duplicates. How can I resolve this issue?

Is there a reason you don't just store the lead time in the orders
table, if it's not stable in another table (because of changing
rules)? Then the calculation is simple. It seems that the value is
derived, but since the function that calculates it is changing, that
makes storing the derived value reasonable in my opinion. Much less
hassle, and you can change the value on a record-by-record basis if
you need to.

 

[pat67]

 I since set up a new table with updated lead


Is there a reason you don't just store the lead time in the orders
table, if it's not stable in another table (because of changing
rules)?  Then the calculation is simple.  It seems that the value is
derived, but since the function that calculates it is changing, that
makes storing the derived value reasonable in my opinion.  Much less
hassle, and you can change the value on a record-by-record basis if
you need to.

Not sure I follow you but here goes. You are saying to store the lead
time value on in the received orders table. So I would need to add a
cloumn for lead time in the table and update it? The reason I have 2
tables is because the lead time is not in the orders file that I
import. Let me see if I can clarify the issue. I have the order table
and a lead time table. I have an updated table that says on 2/17/2009
the lead time for part ABC went from 30 to 60 days. Then another line
that says on 6/17/2009 the lead time for part ABC went from 60 to 120
days. What I need is for the quesry to recognize any order placed
before 2/17/2009 shows 30 days lead time, between 2/17 and 6/16 shows
60 days and any order after 6/17 shows 120 days as the lead time.