current date less 28 days

From: Gerald Stanley (gcstanley_at_d-s-l.pipex.comREMOVE-)
Date: 05/28/04 

Date: Fri, 28 May 2004 15:32:41 -0700

Try
SELECT Students.StudentID AS Students_StudentID,
Students.DateAllocated, Students.StudentChristianName,
Students.StudentSurname, Students.StudentemailAddress,
Students.WelcomeLetter, Students.InductionFeedback,
Students.[Advice & Guidance], Students.ILP,
Students.Status, Students.CollegeID, Students.CourseID,
Students.InductionID, Feedback.FeedbackID, Feedback.Date,
Feedback.Time, Feedback.ContactCategory,
Feedback.ContactTime, Feedback.NonTeachingTime,
Feedback.TypeofContact, Feedback.TypeOfFeedback,
Feedback.Activity, Feedback.ID, Feedback.Situation,
Feedback.Reference, Feedback.NextMove, Feedback.StudentID
AS Feedback_StudentID
FROM Students INNER JOIN Feedback ON Students.StudentID =
Feedback.StudentID
WHERE (((Students.Status)="Active") AND ((Feedback.Date)
<Date()-28)) AND Feedback.Date IN (SELECT Max(F1.[Date])
FROM Feedback As F1 WHERE F1.studentId = Students.studentId)
ORDER BY Feedback.Date DESC;

Hope This Helps
Gerald Stanley MCSD
>-----Original Message-----
>I support online learners and in this particular query I
>am using two tables one for the student information and
>one containing all the contact I have had with the
>student.
>
>I am trying to write a query to return students that I
>have not had any contact with for 28 days.
>
>I have tried <Date()-28 in the Feedback date field but
>this looks at any date in the record not the most recent
>date out of the list. I have also tried the Top 1
>statement as suggestion in a previous answer, but it is
>not returning the correct information.
>
>How can I set the query parameters to look at the newest
>date in the list? I attach my SQL in case this will help
>
>SELECT TOP 1 Students.StudentID AS Students_StudentID,
>Students.DateAllocated, Students.StudentChristianName,
>Students.StudentSurname, Students.StudentemailAddress,
>Students.WelcomeLetter, Students.InductionFeedback,
>Students.[Advice & Guidance], Students.ILP,
>Students.Status, Students.CollegeID, Students.CourseID,
>Students.InductionID, Feedback.FeedbackID, Feedback.Date,
>Feedback.Time, Feedback.ContactCategory,
>Feedback.ContactTime, Feedback.NonTeachingTime,
>Feedback.TypeofContact, Feedback.TypeOfFeedback,
>Feedback.Activity, Feedback.ID, Feedback.Situation,
>Feedback.Reference, Feedback.NextMove, Feedback.StudentID
>AS Feedback_StudentID
>FROM Students INNER JOIN Feedback ON Students.StudentID =
>Feedback.StudentID
>WHERE (((Students.Status)="Active") AND ((Feedback.Date)
><Date()-28))
>ORDER BY Feedback.Date DESC;
>
>Any suggestions gratefully recieved
>
>Many thanks
>
>Marion
>.
>

Relevant Pages

  • Re: current date less 28 days
    ... FROM Students INNER JOIN Feedback ON Students.StudentID = Feedback.StudentID ... > I support online learners and in this particular query I ...
    (microsoft.public.access.queries)
  • Re: Date comparison query
    ... John Spencer wrote: ... I didn't mention January because the query ... Then I made a few changes to account for a time field in my dates: ... FROM Students INNER JOIN DatePass ON Students.StudNum = ...
    (microsoft.public.access.queries)
  • current date less 28 days
    ... I support online learners and in this particular query I ... am using two tables one for the student information and ... FROM Students INNER JOIN Feedback ON Students.StudentID = ...
    (microsoft.public.access.queries)