If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 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 |
#2
|
|||
|
|||
current date less 28 days
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 . |
#3
|
|||
|
|||
current date less 28 days
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 |
#4
|
|||
|
|||
current date less 28 days
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) "marion" wrote in 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 |
#5
|
|||
|
|||
current date less 28 days
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) "marion" wrote in 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 . |
#6
|
|||
|
|||
current date less 28 days
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) -----Original Message----- 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. |
Thread Tools | |
Display Modes | |
|
|