Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2013

    Unanswered: Date Difference between consecutive rows - Help Request

    Student_id ReviewDate
    123 14/04/2013
    123 null
    123 12/03/2013
    123 18/02/2013
    123 21/01/2013
    123 20/01/2013
    124 12/04/2013
    124 07/04/2013
    450 14/02/2013
    450 12/02/2013

    I have above student records I am trying to find the student who have not had a ReviewDate and these students who are due for ReviewDate

    Every student Must have a ReviewDate after 7 days from last ReviewDate

    ie student_id 450 is due for ReviewDate as is more than seven days since they had last ReviewDate. This is based on Today Date. 14/04/2013
    Stuedent_id 121 they are ok as had last review date on 14/04/2013

    I am using Microsoft Access 2007
    I would like to get a report of these students whose ReviewDate is due based on todays date: or date which report was executed
    I would like the report also to show the numbers of days since they had last review date(date difference)

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Assuming that you want all students to be reported, not just those who have prior reviews you will need something like:
    SELECT Student_id
       FROM Students
          FROM Student_Reviews
          WHERE  Student_Reviews.Student_id = Students.Student_id
             AND DateAdd(day, -7, Now()) < Student_Reviews.ReviewDate)
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2013
    Hi Pat Phelan,

    Sorry for late reply I will try this and I will come back to you if I get stuck.
    Once again thanks so much for your time and effort much appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts