Thanks for your reply John however it is prompting me to enter search
criteria and it also wont let me save the query, if i enter the
criteria
manually it still dosen't return what I want, I do apoligise I may not
of
been very clear of my requirements.
I am trying to get a query that will list every student in the database
but
only show the last training date for every student. This sql prompts
me to
enter the [training date] and also [Training Attendance].[Student]
So what I want returned is similar to this:
Student Last Training Date
Student A 15/01/2009
Student B 21/01/2009
etc
Regards
Dave
:
Since you have spaces in your table and field names you are going to
need square brackets around each. The brackets are optional for the
other field and table names. Names of fields and tables should
consist
of only Letters, Numbers, and the underscore characters. If that is
not
possible, then you must surround the names with square brackets.
Also,
avoid reserved words like Date, Time, Sum, etc.
SELECT Students.ID, [Training Sessions].[Training Session ID],
(SELECT Max([Training Attendance].[Training Date]) AS A
FROM [Training Attendance]
WHERE [Training Attendance].[Training Session ID] =
[Training Sessions].[Training Session ID]
AND [Training Attendance].Student = Students.ID) AS
LastDateAttended
FROM Students INNER JOIN [Training Sessions]
ON Students.ID = [Training Sessions].Student;
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
Blakey300 wrote:
Hi Ken
Thanks for your reply, I have modified your sql query to reflect my
field
names but I am getting a missing operator syntax error on Training
Sessions.Training Session ID.
I am sure its me being thick but any furter advice would be
appricated.
My current SQL looks like this:
SELECT Students.ID, Training Sessions.Training Session ID,
(SELECT Max(Training Attendance.Training Date) AS A
FROM Training Attendance
WHERE Training Attendance.Training Session ID = Training
Sessions.Training
Session ID
AND Training Attendance.Student = Students.ID) AS LastDateAttended
FROM Students INNER JOIN Training Sessions
ON Students.ID = Training Sessions.Student;
Regards
Dave
:
Something like this:
SELECT Students.StudentID, Sessions.SessionID,
(SELECT Max(Attendance.DateAttended) AS A
FROM Attendance
WHERE Attendance.SessionID = Sessions.SessionID
AND Attendance.StudentID = Students.StudentID) AS LastDateAttended
FROM Students INNER JOIN Sessions
ON Students.StudentID = Sessions.StudentID;
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Hi
I am using access 2007 and I have the following scenario
I have the following table structure: Students, Sessions &
Attendance
I want to create a query which will list all students and the LAST
date
that
they attended a session.
Is this possible
Regards
Dave