Latest due dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with a list of items to be verified on 2 month intervals from their completion date:
[Action Items].ID
[Action Items].Item
[Action Items].CloseDate (Date the item was completed)

I also have a table for the verification activity:
[Verification].ID
[Verification].Lookup (number field with a link to the [Action Items].ID)
[Verification].vDate (Date of verification)
[Verification].Result (Pass, Fail, or Close)

I need to be able to create a report that shows all items that need to be verified. I can easily pull the initial verification by looking for Action Items with no history in [Verification]. Now I need to know the best way to have it show all items that were completed 2,4 and 6 months ago and have not been verified after that due date.
Any help would be appreciated.
 
Adam,

How about something like:

SELECT AI.ID, AI.Item, AI.CloseDate, Count(V.ID)
FROM [Action Items] AI
LEFT JOIN [Verification] V
ON AI.ID = V.Lookup
GROUP BY AI.ID, AI.Item, AI.CloseDate
HAVING Count(V.ID) < INT(Datediff("m", AI.CloseDate, Date()) / 2)

This should give you a count of the number of verification records, and if
that number is less than the number of months between Todays date and the
CloseDate, divided by two, then you are missing a verification.

HTH
Dale

AdmSteck said:
I have a table with a list of items to be verified on 2 month intervals from their completion date:
[Action Items].ID
[Action Items].Item
[Action Items].CloseDate (Date the item was completed)

I also have a table for the verification activity:
[Verification].ID
[Verification].Lookup (number field with a link to the [Action Items].ID)
[Verification].vDate (Date of verification)
[Verification].Result (Pass, Fail, or Close)

I need to be able to create a report that shows all items that need to be
Click to expand...
verified. I can easily pull the initial verification by looking for Action
Items with no history in [Verification]. Now I need to know the best way to
have it show all items that were completed 2,4 and 6 months ago and have not
been verified after that due date.
 
Back
Top