Problem with Access SQL Query

[ahillyer]

Hi folks,

I have the following schema and am using Access 2003:

Enrollments Table
Student (lookup field referencing the Name field in the Students table)
Session (lookup field referencing the ID field in the Sessions table)
ClassesAttended
... (some more fields unnecessary to this discussion)

Students table:
Name
NOFDate
....

Sessions Table
ID
...


Here is my original query...

PARAMETERS NOF Short;
SELECT DISTINCT Students.Name
FROM Students INNER JOIN Enrollments ON Students.Name=Enrollments.Student
WHERE (((Enrollments.ClassesAttended)>=1) AND (Students.NOFDate=[NOF]));

Which does what I need it to by showing unique students by NOFDate.
I'm getting 58 records for one of my sample NOF Dates and that is exactly how many I should have.

However, I need to show those by session as well.
If I add in Enrollments.Session in the SELECT line so it reads...
SELECT DISTINCT Students.Name, Enrollments.Session

Now it doesn't work. I now get all 78 records listed for that NOF Date which gives me duplicate students. What I want is for each Student to only be counted 1x during each NOFDate regardless of how many enrollments are listed.

Please help!!!

[MMcCarthy]

Once you add Sessions you will get the Student Name appearing for each session they attended.

Try this out of curiousity to see what happens.

PARAMETERS NOF Short;
SELECT Students.Name, Enrollments.Session
FROM Students INNER JOIN Enrollments ON Students.Name=Enrollments.Student
WHERE (((Enrollments.ClassesAttended)>=1) AND (Students.NOFDate=[NOF]))
GROUP BY Students.Name, Enrollments.Session;