Re: current date less 28 days

From: Steve Schapel (schapel_at_mvps.org.ns)
Date: 05/28/04 

Date: Sat, 29 May 2004 10:36:47 +1200

Marion,

Here is the skeleton of what should work for you...

SELECT Feedback.StudentID, First([StudentChristianName] & " " &
[StudentSurname]), Max(Feedback.[Date])
FROM Students INNER JOIN Feedback ON Students.StudentID = Feedback.StudentID
GROUP BY Feedback.StudentID
HAVING Max(Feedback.[Date])<Date()-28 

-- 
Steve Schapel, Microsoft Access MVP
marion wrote:
> 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

  • current date less 28 days
    ... >I support online learners and in this particular query I ... >FROM Students INNER JOIN Feedback ON Students.StudentID = ...
    (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)