current date less 28 days

M

marion

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
 
G

Gerald Stanley

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
 
S

Steve Schapel

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
 
V

Van T. Dinh

Your Query won't show Students that never had any feddback and it will
return 1 student even if there are more than 1 Student that has not had any
contact in the last 28 days or never had any contact.

Try something like: (***untested***)

SELECT S.StudentID
FROM Students AS S LEFT JOIN
FeedBack F On S.StudentID = F.frg_StudentID
WHERE ( F.[Date] BETWEEN (Date() - 28) AND Date() ) AND
( F.frg_StudentID Is Null )

This will (I hope) give you the list of StudentIDs for those students that
have never had any contact and those who have not had any contact in the
last 28 days.

You can then use the above in another Query (using GROUP BY clause) to get
the students' details and related info from Table FeedBack. It is likely
that you can combine all into one Query but it will be complicated.
 
M

Marion

Thank you for your suggestion, but I am having problems
getting the syntax to work. I attach my SQL so you can
see where I have gone wrong. I have obviously
misunderstood some of your abbreviations. I look forward
to hearing from you again

SELECT Students.StudentID
FROM Students AS Students LEFT JOIN
FeedBack F On Students.StudentID =
Feedback.frg_StudentID
WHERE (Feedback.Date < (Date()-28))


ps: There will always be a feedback date because I
always send out a welcome message on registering new
students, so the field should never be null.

-----Original Message-----
Your Query won't show Students that never had any feddback and it will
return 1 student even if there are more than 1 Student that has not had any
contact in the last 28 days or never had any contact.

Try something like: (***untested***)

SELECT S.StudentID
FROM Students AS S LEFT JOIN
FeedBack F On S.StudentID = F.frg_StudentID
WHERE ( F.[Date] BETWEEN (Date() - 28) AND Date() ) AND
( F.frg_StudentID Is Null )

This will (I hope) give you the list of StudentIDs for those students that
have never had any contact and those who have not had any contact in the
last 28 days.

You can then use the above in another Query (using GROUP BY clause) to get
the students' details and related info from Table FeedBack. It is likely
that you can combine all into one Query but it will be complicated.

--
HTH
Van T. Dinh
MVP (Access)



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
Click to expand...


.
Click to expand...
 
V

Van T. Dinh

You need to substitute the phantom names I used with the
names in your database.

Try this (using your Field names so you can simply copy
and paste):

SELECT Students.StudentID
FROM Students LEFT JOIN
FeedBack On Students.StudentID = Feedback.StudentID
WHERE ( Feedback.[Date] >= (Date() - 28) ) AND
( Feedback.StudentID Is Null )

The 2nd criterion refers to the Field StudentID in the
Feedback Table, NOT the [Date].

HTH
Van T. Dinh
MVP (Access)
 
Top