By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,687 Members | 2,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,687 IT Pros & Developers. It's quick & easy.

Designing query to only show unique records

P: n/a
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.

Jun 28 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Jun 28, 1:44 pm, kdubble <khaw...@gmail.comwrote:
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

Jun 28 '07 #2

P: n/a
On Jun 28, 2:29 pm, ManningFan <manning...@gmail.comwrote:
On Jun 28, 1:44 pm, kdubble <khaw...@gmail.comwrote:
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.

Jun 28 '07 #3

P: n/a
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
Jun 28 '07 #4

P: n/a
On Jun 28, 4:16 pm, kdubble <khaw...@gmail.comwrote:
On Jun 28, 2:29 pm, ManningFan <manning...@gmail.comwrote:


On Jun 28, 1:44 pm, kdubble <khaw...@gmail.comwrote:
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.

Jun 28 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.