Adding frequency of revision to last date revised

[azalealee]

Hello!

I'm developing a query for a document management system that uses tables with some of the following fields:

Tbl_Revision (stores revision details):
Policy_ID
Revision_Number (0,1,2,3rd revision)
Date_Revised....

Tbl_Policy (stores policy details):
Policy_ID
Revision_Frequency_ID...

Tbl_Revision_Frequency (lookup table for frequency of revision):
Revision_Frequency(e.g 6 months, 1 year...5 years)
Revision_Frequency_ID

I'm unsure of how to get a listing of the the next required revision date based on adding the Revision_Frequency to the last record of Date_Revised for each policy.

I think I have to create a new field in the query called "Review_Due_Date", but I don't know how to select the last Date_Revised and add its respective Revision_Frequency based on its Revision_Frequency_ID.


I'd appreciate any help or suggestions with this. Thankyou.

 

[MichaelRed]

You should not need an additional field in hte table(s). Look up DateAdd function in help. Set up a calculated field using the last date revised and the revision interval to calculate the "due date". It will be easier if you convert all of the revision intervals to hte same 'units' (i.e. months or years - not the mixture of some of each).

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RickSpr]

About creating a separate column for revision due date:
Consider what would happen if, after you create such a column, the revision frequency for some policy is changed. The existing revision due dates would not change to correspond with the revised policy. Depending on how policies are applied, it might be that this is what you want, or it might be that the due dates would need to be recalculated. In the first case, you must create a separate column, but in the second you would be better off not to create it, because if you do you must go back and update due dates whenever a revision frequency is changed.

I'm going to assume that Revision_Frequency is a descriptive field used in a list box or combo box. Tbl_Revision_Frequency should have two additional columns that express this frequency period in terms of an interval type ("m" for months, "d" for days, "yyyy" for years, or "ww" for weeks) and an interval size (numeric). I'll use the names Revision_Interval_Type and Revision_Interval, respectively, for these columns.

Here's a query that calculates the revision due date for each row in Tbl_Revision:
SELECT Tbl_Revision.*, DateAdd(Revision_Interval_Type, Revision_Interval,
Date_Revised) AS Revision_Due_Date
FROM (Tbl_Revision INNER JOIN Tbl_Policy ON Tbl_Revision.Policy_ID
= Tbl_Policy.Policy_ID)
INNER JOIN Tbl_Revision_Frequency ON Tbl_Policy.Revision_Frequency_ID
= Tbl_Revision_Frequency.Revision_Frequency_ID

If you decide to create the Due Date column, you could turn this into an Update query that populates the new column. Otherwise, you can use this query to substitute for Tbl_Revision anywhere you need the due date calculated. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[azalealee]

Hi! Firstly, thank you both for your help and sorry for the slow reply (festive holidays).

I've taken your advice to not add another field called Review_Due_Date. Instead, I've deleted Tbl_Revision_Frequency altogether and included the fields Revision_Interval_Type and Revision_Interval into Tbl_Policy. The data stored into these fields is m for months, yyyy for years and an integer respectively.

I've created a query from Tbl_Policy and Tbl_Revision. Using the DateAdd function I am now able to add the correct interval and interval type to the date revised (thanks!). However, I'm still having troubles trying to get the last Date_Revised for each policy.

For example, Tbl_Revision may have the following entries:
Policy_ID Revision_Number Date_Revised
1 1 1/2/01
1 2 1/2/02
2 1 3/4/00
2 2 3/4/01
2 3 3/4/02

My query needs to perform the DateAdd function on the Date_Revised for the latest revision for each policy and produce 1/2/02 and 3/4/02.

I have tried to use DMAX as criteria on Revision_Number, but it only returns one date (3/4/02), not one for each policy.

This is my sql code:

SELECT Tbl_Revision.Policy_ID, Tbl_Revision.Revision_Number, Tbl_Revision.Date_Revised, Tbl_Policy.Revision_Interval_Type, Tbl_Policy.Revision_Interval, DateAdd([Revision_Interval_Type],[Revision_Interval],[Date_Revised]) AS Revision_Due_Date
FROM Tbl_Policy INNER JOIN Tbl_Revision ON Tbl_Policy.Policy_ID = Tbl_Revision.Policy_ID
WHERE (((Tbl_Revision.Revision_Number)=DMax("Revision_Number","Tbl_Revision","Policy_ID&quot));

Thanks again for your help and have a happy New Year.

Azalea.

 

[MichaelRed]

DMax will only retun one "DOMAIN" value. You need a subquery to return the SET of objects with the max(Revision_Number) for each Policy_ID.

You could also do it with a self join query, and other approaches. The (conceptual) process is to isolate the subset as the single record for each policy where the revision is the 'greatest' (or the rev date is the latest), and do the dateadd on that subset.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[azalealee]

Wow! Thanks for the tip! With much searching and reading up on sub queries, I've finally achieved what I wanted. This was the final code for anyone interested:

SELECT Tbl_Revision.Policy_ID, Tbl_Revision.Revision_Number, Tbl_Revision.Date_Revised, Tbl_Policy.Revision_Interval_Type, Tbl_Policy.Revision_Interval, DateAdd([Revision_Interval_Type],[Revision_Interval],[Date_Revised]) AS Revision_Due_Date
FROM Tbl_Policy INNER JOIN Tbl_Revision ON Tbl_Policy.Policy_ID = Tbl_Revision.Policy_ID
WHERE (((Tbl_Revision.Revision_Number) In (SELECT Max(Tbl_Revision.Revision_Number) AS MaxRevision
FROM Tbl_Policy INNER JOIN Tbl_Revision ON Tbl_Policy.Policy_ID = Tbl_Revision.Policy_ID
Group BY Tbl_Revision.Policy_ID));

Thanks again for your help!

Azalea