I have a Report with the following Fields: "Entity Name, Review Cycle, Last Review Date, and Next Review Date". The "Next Review Date" is based off of an expression in a query. The expression is a DateAdd function based off of Monthly, Quarterly, and Annual Review Cycles. "Expression: DateAdd([ReviewCycle],1,[ReviewDate])".
My Boss wants to be able to do 2 things with this Report:
1) He wants to show "Reviews Past Due". For example if my "Next Review Date" is past today's date he wants to be able to see which companies he's missed reviewing. I've used conditional formatting for my Next Review Date field to highlight the Reviews Past Due but my main problem is below:
2) My current Report, called "Next Review by Date", sorts the Next Review Date from "Oldest to Newest" so it looks like this:
Next Review Date
8/10/2010
9/4/2010
10/5/2011
12/3/2011
7/9/2012
etc.
Here's my problem: Let's say "CompanyA" has a Monthly Review Cycle. My boss writes a Review on 6/1/2011. That would make the Next Review Date in the Report 7/1/2011 (a month later). Then on 7/1/2011 he writes another Review, making the Next Review Date 8/1/2011. The problem is that each time he writes a new Review, the previous "Next Review Date" is still there. I want there to only be ONE "Next Review Date" per company based off of the Last Review Date, not all of them. Right now it looks like this:
Next Review Date..........Review Date............Entity.............ReviewCycle
7/1/2011......................6/1/2011................CompanyA.......Quarterly
8/1/2011......................7/1/2011................CompanyA.......Quarterly
9/1/2011......................8/1/2011................CompanyA.......Quarterly
I want it to look like this:
Next Review Date..........Review Date............Entity.............ReviewCycle
9/1/2011......................8/1/2011................CompanyA.......Quarterly
Does anybody know how I can do this? I have a feeling it has something to do with Conditional formatting which I'm not too familiar with. Thank you.
-Luke