Designing query to only show unique records

[kdubble]

Hi

I am trying to get the results of a query to show only unique student
records (not duplicates). Is there a simple way to make the criteria
field do this? I am not too familiar with SQL.

I have a student_info table, a session_info table, and a
services_to_students table.
What I'd like to do is query the tables to get a list of all students
served between certain dates. Now, of course, between those dates,
any given student has been served more than once, so without
eliminating duplicates, a given student (tracked by a unique
student_record) will show up repeatedly.
Any ideas? Again, I know it's pretty simple, but I can't seem to do
it.
Any help is appreciated.

[ManningFan]

On Jun 28, 1:44 pm, kdubble <[e-mail]:

Hi

I am trying to get the results of a query to show only unique student
records (not duplicates). Is there a simple way to make the criteria
field do this? I am not too familiar with SQL.

I have a student_info table, a session_info table, and a
services_to_students table.
What I'd like to do is query the tables to get a list of all students
served between certain dates. Now, of course, between those dates,
any given student has been served more than once, so without
eliminating duplicates, a given student (tracked by a unique
student_record) will show up repeatedly.

Any ideas? Again, I know it's pretty simple, but I can't seem to do
it.
Any help is appreciated.

Put the word DISTINCT directly after the word Select.

i.e. Select DISTINCT Student from tblStudentInfo

[kdubble]

On Jun 28, 2:29 pm, ManningFan <[e-mail]:

On Jun 28, 1:44 pm, kdubble <[e-mail]:

Hi

I am trying to get the results of a query to show only unique student
records (not duplicates). Is there a simple way to make the criteria
field do this? I am not too familiar with SQL.

I have a student_info table, a session_info table, and a
services_to_students table.
What I'd like to do is query the tables to get a list of all students
served between certain dates. Now, of course, between those dates,
any given student has been served more than once, so without
eliminating duplicates, a given student (tracked by a unique
student_record) will show up repeatedly.

Any ideas? Again, I know it's pretty simple, but I can't seem to do
it.
Any help is appreciated.

Put the word DISTINCT directly after the word Select.

i.e. Select DISTINCT Student from tblStudentInfo

That didn't work:

here's my SQL view

SELECT DISTINCT Student_Info.Student_Record, Student_Info.Grade,
Student_Info.SchoolName, Session_Information.Counselor,
Session_Information.Date, Student_Info.[Student Last]
FROM Student_Info INNER JOIN (Session_Information INNER JOIN
Services_To_Students ON Session_Information.[Session#] =
Services_To_Students.[Session#]) ON Student_Info.Student_Record =
Services_To_Students.Student_Record
GROUP BY Student_Info.Student_Record, Student_Info.Grade,
Student_Info.SchoolName, Session_Information.Counselor,
Session_Information.Date, Student_Info.[Student Last]
HAVING (((Session_Information.Counselor)<>"All Counselors" And
(Session_Information.Counselor)<>"Paid Tutor/counselor") AND
((Session_Information.Date) Between #7/1/2006# And #6/27/2007#));

my results show each individual service that a given student_record
receives. I just want to show how many students got serviced in a
date range.

[Rick Brandt]

kdubble wrote:

That didn't work:

here's my SQL view

SELECT DISTINCT Student_Info.Student_Record, Student_Info.Grade,
Student_Info.SchoolName, Session_Information.Counselor,
Session_Information.Date, Student_Info.[Student Last]
FROM Student_Info INNER JOIN (Session_Information INNER JOIN
Services_To_Students ON Session_Information.[Session#] =
Services_To_Students.[Session#]) ON Student_Info.Student_Record =
Services_To_Students.Student_Record
GROUP BY Student_Info.Student_Record, Student_Info.Grade,
Student_Info.SchoolName, Session_Information.Counselor,
Session_Information.Date, Student_Info.[Student Last]
HAVING (((Session_Information.Counselor)<>"All Counselors" And
(Session_Information.Counselor)<>"Paid Tutor/counselor") AND
((Session_Information.Date) Between #7/1/2006# And #6/27/2007#));

my results show each individual service that a given student_record
receives. I just want to show how many students got serviced in a
date range.

DISTINCT will work if you don't include any fields from the
Session_Information table in your output.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[ManningFan]

On Jun 28, 4:16 pm, kdubble <[e-mail]:

On Jun 28, 2:29 pm, ManningFan <[e-mail]:

On Jun 28, 1:44 pm, kdubble <[e-mail]:

Hi

I am trying to get the results of a query to show only unique student
records (not duplicates). Is there a simple way to make the criteria
field do this? I am not too familiar with SQL.

I have a student_info table, a session_info table, and a
services_to_students table.
What I'd like to do is query the tables to get a list of all students
served between certain dates. Now, of course, between those dates,
any given student has been served more than once, so without
eliminating duplicates, a given student (tracked by a unique
student_record) will show up repeatedly.

Any ideas? Again, I know it's pretty simple, but I can't seem to do
it.
Any help is appreciated.

Put the word DISTINCT directly after the word Select.

i.e. Select DISTINCT Student from tblStudentInfo

That didn't work:

here's my SQL view

SELECT DISTINCT Student_Info.Student_Record, Student_Info.Grade,
Student_Info.SchoolName, Session_Information.Counselor,
Session_Information.Date, Student_Info.[Student Last]
FROM Student_Info INNER JOIN (Session_Information INNER JOIN
Services_To_Students ON Session_Information.[Session#] =
Services_To_Students.[Session#]) ON Student_Info.Student_Record =
Services_To_Students.Student_Record
GROUP BY Student_Info.Student_Record, Student_Info.Grade,
Student_Info.SchoolName, Session_Information.Counselor,
Session_Information.Date, Student_Info.[Student Last]
HAVING (((Session_Information.Counselor)<>"All Counselors" And
(Session_Information.Counselor)<>"Paid Tutor/counselor") AND
((Session_Information.Date) Between #7/1/2006# And #6/27/2007#));

my results show each individual service that a given student_record
receives. I just want to show how many students got serviced in a
date range.- Hide quoted text -

- Show quoted text -

You have to limit what you're pulling. It might require 2 queries to
do what you're trying to do.

Just remember that every record you're pulling is unique. For
instance, you're pulling Student_Info.Grade. if you have 1 student
with 2 different grades, you're going to be pulling that student more
than once. Pull the least amount of info necessary to ensure you
don't get dupes.

It sounds like you should probably set up 1 query that has a field
like
SERVICED: iif((((Session_Information.Counselor)<>"All Counselors"
And (Session_Information.Counselor)<>"Paid Tutor/counselor")
AND ((Session_Information.Date) Between #7/1/2006# And #6/27/2007#)),
1, 0)

Also pull in the student's name into the same query (so it will just
have the 2 fields).

You're going to need something better than Last Name to pull, unless
you are really lucky and only have 1 student with a given Last Name.

You should end up with something like:
NAME SERVICED
Davis 1
Lyons 0
Green 1
Murphy 0

You can set the Criteria of the field SERVICED to 1, so that you only
pull students you need.

Use this query as a pointer, and join this query back to Student_Info
(link by Last Name) to pick up the extra information you need (grade,
school name, counsellor, etc...)

Did that make any sense to you? I know it seems a bit murky, but once
you wrap your head around it then it should make perfect sense.