Filter by date in query

B

Blakey300

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
 
K

Ken Snell \(MVP\)

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;
 
B

Blakey300

Sessions table

[Session ID], [Location], [Session Date]

Attendance Table

[Attendance ID], [Student ID], [Session ID]

Session ID is the link field between the above 2 tables then student id is
the link between Attendance and student tables

raskew via AccessMonster.com said:
Dave -

Please describe fields [Sessions] and [Attendance].

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

Blakey300

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
 
J

John Spencer

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
'====================================================
 
B

Blakey300

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

John Spencer said:
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
'====================================================

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

John Spencer

If you are getting prompted for those values then You have no field
[Training Date] and not field named Student in the [Training Attendance]
table.

Perhaps the field is really named StudentID or StudentName.
And TrainingDate is the name of the field.

You can see you tables and fields - we can't.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

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

John Spencer said:
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
'====================================================

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

Blakey300

Hi John

Sorry to be a pain, but have spotted the problem in the sql which refered to
the training date in the attendance table rather then the Sessions table so i
have ammended as follows:

SELECT Students.ID, [Training Sessions].[Training Session ID],
(SELECT Max([Training Sessions].[Training Date]) AS A
FROM [Training Sessions]
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] INNER JOIN [Training
Attendance] ON [Training Sessions].[Training Session ID] = [Training
Attendance].[Training Session ID]) ON Students.ID = [Training
Attendance].Student;

This is returning the following query result

Student 1 Session 1 01/02/2009
Student 2 Session 1 01/02/2009
Student 1 Session 2 02/02/2009
Student 2 Session 3 05/01/2009
Student 1 Session 3 05/02/2009
Student 1 Session 4 01/03/2009
Student 3 Session 1 01/02/2009

What I want returned is the following:

Student 1 Session 4 01/03/2009
Student 2 Session 1 01/02/2009
Student 3 Session 1 01/02/2009

Regards

Dave


John Spencer said:
If you are getting prompted for those values then You have no field
[Training Date] and not field named Student in the [Training Attendance]
table.

Perhaps the field is really named StudentID or StudentName.
And TrainingDate is the name of the field.

You can see you tables and fields - we can't.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

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

John Spencer said:
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
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

You don't need to include the Training Sessions table in the main query:

SELECT Students.ID, [Training Sessions].[Training Session ID],
(SELECT Max([Training Sessions].[Training Date]) AS A
FROM [Training Sessions]
WHERE [Training Attendance].[Training Session ID] =
[Training Sessions].[Training Session ID]
AND [Training Attendance].Student = Students.ID) AS LastDateAttended
FROM Students INNER JOIN [Training Attendance]
ON Students.ID = [Training Attendance].Student;

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Blakey300 said:
Hi John

Sorry to be a pain, but have spotted the problem in the sql which refered
to
the training date in the attendance table rather then the Sessions table
so i
have ammended as follows:

SELECT Students.ID, [Training Sessions].[Training Session ID],
(SELECT Max([Training Sessions].[Training Date]) AS A
FROM [Training Sessions]
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] INNER JOIN [Training
Attendance] ON [Training Sessions].[Training Session ID] = [Training
Attendance].[Training Session ID]) ON Students.ID = [Training
Attendance].Student;

This is returning the following query result

Student 1 Session 1 01/02/2009
Student 2 Session 1 01/02/2009
Student 1 Session 2 02/02/2009
Student 2 Session 3 05/01/2009
Student 1 Session 3 05/02/2009
Student 1 Session 4 01/03/2009
Student 3 Session 1 01/02/2009

What I want returned is the following:

Student 1 Session 4 01/03/2009
Student 2 Session 1 01/02/2009
Student 3 Session 1 01/02/2009

Regards

Dave


John Spencer said:
If you are getting prompted for those values then You have no field
[Training Date] and not field named Student in the [Training Attendance]
table.

Perhaps the field is really named StudentID or StudentName.
And TrainingDate is the name of the field.

You can see you tables and fields - we can't.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

last date attended query 1
Count query 3
Syntax error in form 4
How do I count non-duplicated records in a query in Access? 1
Query with Criteria Not Working 7
Date Function 6
Query lacks Specific Criteria 1
Return Records That Don't Meet Criteria 4

Top