changing data only after a certain date

[pat67]

Here is my dilemma. Right now in tracking on time delivery, we use a
lead time field to determine if we gave a vendor enough time. I have
historical data that includes this lead time. The issue i have is that
if we change the lead time, it changes even for orders we already
received with a previous lead time. Example

PO 123 was received on 7/1/2009. The lead time table shows a lead time
of 10 days.

PO Part Entered Due Received Late
123 abc 6/1/2009 6/15/2009 7/1/2009 Yes

I have a query that asks if the due date was within the lead time. If
i were to change the lead time for part abc to 45 days, that query
would say we didn't give the vendor enogh time and mark the delivery
on time. That is my issue. One solution I came up with is to create a
new table and then append it each time i run the query. That way only
new data is affected. Is there another way to do what I need within a
query. I tried using a date field on the lead time table whenever it
is updated and then asking the query to check if the date entered for
the PO is before or after that date. but if there are multiple
results, I get bad data.

 

[KARL DEWEY]

Do you want lead time based upon vendor or product delivery history?

How complex do you want it to be? Maybe not a table but calculated each
time based on last X orders or calendar days.

Take the days difference from order to delivery over the period (last X
orders or calendar days) that fall Between (10 percent greater than minimum)
AND (10 percent less than maximum), average that and add 10 percent.

This tosses out the slowest and fastest.