query by only overdue dates

[stevec6832]

Hi all, I have an access front end DB with SQL backend. To explain what I am trying to achieve. I have staff who work with students and they must see the student at least every month. I have a main table which stores all the students details. I have other tables but in particular one table that is used for tracking when a member of staff meets with a student. The tables are linked and work fine.
What I am trying to achieve here is that I want to perform a query where by if a member of staff does not meet with a student after 60 days it will inform me. I am happy with datediff functions and everything else but the problem is the tracking table will have multiple entries for a student over various dates. I am trying to find a way for it to look at the last most recent meeting date only and then using datediff (if that is the best way) count >60 from the date based on that last meeting date based on todays date() and for it to return -1 where it has exceeded 60 days. I have this part working but it will also bring up all previous meeting dates as well.
How can I query this so that it will only look at the row with the last most recent meeting date.

I hope I have explained this ok.

[zmbd]

Please post the SQL you're using...
Click on the [CODE/] button to the left in the format bar...
You will get a pair of: [CODE] [CODE/] tags
Select your SQL and paste it between the tags.

[stevec6832]

SELECT dbo_TransedMasterR.IDMain, dbo_TransedMasterR.Status, dbo_CandidateTracking.CandidateID, dbo_TransedMasterR.[Student Number ], dbo_TransedMasterR.[First Name], dbo_TransedMasterR.[Last Name], dbo_CandidateTracking.ContactDate, dbo_CandidateTracking.NatureOfContact, dbo_CandidateTracking.AssessorNotes, dbo_CandidateTracking.LearningAchieved, dbo_CandidateTracking.TargetsSet, dbo_CandidateTracking.NextMeetingDate, dbo_CandidateTracking.AssessorName, DateDiff('d',[contactdate],Date())>60 AS sc
FROM dbo_TransedMasterR LEFT JOIN dbo_CandidateTracking ON dbo_TransedMasterR.IDMain = dbo_CandidateTracking.CandidateID
WHERE (((dbo_TransedMasterR.Status)='on programme'));

Hope this helps, so as mentioned the AS sc col now gives a 0 or -1 where based on the datediff and the criteria of >60 days against todays date. Happy so far with that but what I need to do is somehow drop all other records except the record with the latest contact date. Obviously the records are based on the candidateID field.

Just as a point with the AS sc col it outputs either a 0 0r -1 which is great but is there anyway to change these values to say something else such as yes or no. I know this can be done with another query but just as a point of interest and knowledge I wonder if anyone has ever done this.

[stevec6832]

Apologies in advance if you reply and I don't get back to you until tomorrow.

[Rabbit]

Use a aggregate query to return the max date per student and do the datediff on that.

[stevec6832]

Hi Rabbit thank you I was attempting just what you mention there and if I can get it to work will post back with the code, I have used aggregate functions in a lot of my queries for this database but I bit stuck on this one (must be my age lol). I just love the challenges though.

[stevec6832]

Ok have it working to a point as Rabbit mentioned, this works by bringing back the last date on the table. I might be acting really dumb here but have tried to group by the result by the candidate ID but to no effect. How can I expand this query to now show the result where it groups the result by candidateID and not just the max contactdate for all records.

SELECT dbo_CandidateTracking.idtracking, dbo_CandidateTracking.CandidateID, dbo_CandidateTracking.ContactDate
FROM dbo_CandidateTracking
WHERE (((dbo_CandidateTracking.ContactDate) In (SELECT MAX(ContactDate) FROM dbo_CandidateTracking)));

[stevec6832]

Ok have it running for all following I created my first query as

SELECT Last(dbo_CandidateTracking.ContactDate) AS LastOfContactDate, dbo_CandidateTracking.CandidateID
FROM dbo_CandidateTracking
GROUP BY dbo_CandidateTracking.CandidateID;

I then run a separate query as follows

SELECT DISTINCT qry_Last_Contact_Date.LastOfContactDate, qry_Last_Contact_Date.CandidateID, dbo_CandidateTracking.idtracking, dbo_CandidateTracking.NatureOfContact, dbo_CandidateTracking.AssessorNotes, dbo_CandidateTracking.AssessorName
FROM dbo_CandidateTracking INNER JOIN qry_Last_Contact_Date ON dbo_CandidateTracking.ContactDate = qry_Last_Contact_Date.LastOfContactDate;

I can now as Rabbit mentioned add my datediff function to the second query.

Thanks rabbit you coaxed me in the right direction

[Rabbit]

I'm glad you got it working. Good luck on the rest of your project!