Calculation of dynamic and specific time frames

[kiwiora]

I have records for various clients. Clients have specific deadline criteria.

Lets say I have Client's A, B and C.

I have deadlines of i, ii, and iii

I have a trigger date - for deadlines i and ii only

Deadline i

Client A = 3 days

Client B = 5 days

Client B = 3 days

Deadline ii

Client A = 14 days

Client B = 28 days

Client B = 16 days

Deadline iii

Client A = deadline ii + 7 days

Client B = deadline ii + 28 days

Client B = deadline ii + 16 days

Ok so fairly easy, on my initial table are fields (example data):

client (A)

Trigger Date (01/06/2006)

Deadline i (Trigger Date + Standard:Deadline1)

Deadline ii (Trigger Date + Standard:Deadline2)

Deadline iii (Deadline ii + Standard:Deadline3)

In another table (Standards) I have

client - (A)

Deadline i - (3)

Deadline ii - (7)

So my question is this...

I need a mix of lookup and calculated. i.e When creating a new record in my initial table I should use a lookup as down the line, the standard days may vary. The problem with this however is whilst Deadline i and ii need to be updated so that each new record created is the "new" time frame (i.e lookup), the Deadline iii may become retrospective (i.e for all the existing records need to be updated so this should really be a calc - but not necessarily all the time).

So the only thing I can come up with is make them all look-up's but add a feature where if they change the number in the standards, they can update their Deadline iii in their found set.

Does that all make sense and the right way to approach it?

The only other idea is to have some sort of field that uses either a look-up, or the calc'd one instead. (i.e a radio for either and it uses whichever to do the calc on each record individually)