Results 1 to 6 of 6
  1. #1
    Luke is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    72

    Exclamation Reviews Past Due

    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

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    From what you have posted, it appears that you only want the most recent review to be listed. To do that, you have to alter the query on which the report is based to find the most recent review date for each customer. You will need a query that uses the Max() on the review data field and group by customer.

  3. #3
    Luke is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    72
    Yes I want only the most recent review to be listed. Which field should I use the Max() function on the Last Review Date or my Dateadd expression on the Next Review Date? Thanks.

    -Luke

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I would use the review date.

    SELECT customerID, MAX(reviewdate) as MostRecentReview
    FROM yourtable
    GROUP BY customerID

    You will probably have to join the query above back to your table (where the review dates are held). You will need to join by both customerID (tablename.customerID to query.customerID) and the review date (tablename.Reviewdate to query.MostRecentReview) then you will be able to select other fields. You would put your calculated field (next review date) in this query.

  5. #5
    Luke is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    72
    Thanks a lot, I figured it out. I actually just grouped my Last Review Date and Next Review Date with the Total:Max function in my query and it works. Thanks!

    -Luke

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Glad you got it worked out. Good luck with your project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Past Due Query
    By sai_rlaf in forum Queries
    Replies: 4
    Last Post: 07-06-2011, 01:53 PM
  2. Replies: 1
    Last Post: 05-23-2011, 02:15 AM
  3. Show past records for same user
    By l3111 in forum Database Design
    Replies: 3
    Last Post: 03-03-2011, 10:57 AM
  4. 12 Zeros past decimal
    By WhatnThe in forum Access
    Replies: 24
    Last Post: 01-03-2010, 09:53 PM
  5. Date Past Function
    By Laney in forum Access
    Replies: 4
    Last Post: 05-21-2008, 07:19 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums