Re: Designing query to only show unique records

On Jun 28, 4:16 pm, kdubble <khaw...@xxxxxxxxx> wrote:
On Jun 28, 2:29 pm, ManningFan <manning...@xxxxxxxxx> wrote:





On Jun 28, 1:44 pm, kdubble <khaw...@xxxxxxxxx> wrote:

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.

.

Relevant Pages

  • Re: Cumulative cost
    ... When you say "running total per student", I believe this means the same ... You can use a Totals query to get a "per student" Sum ... AND calculate something you're calling "cumulative cost". ...
    (microsoft.public.access.forms)
  • Re: sql views for denomalizing
    ... >> with SQL and that was that. ... queries using the query language (compared to using ... >> the field were in the student relation. ... One type of virtual field is strictly a display or representation ...
    (comp.databases.theory)
  • Help! Need to update linked SQL table in Acc07
    ... I have several linked MS SQL SVR tables. ... it contains student records over many years, ... Qry902NonPubList2008 - a query of linked SQL tables producing the values ... the two queries each reference Enrollments table to find this other ...
    (microsoft.public.access.queries)
  • Re: Searching for and editing a record in form view
    ... I've created a query but I was a bit unsure of this, ... different from the StudentID for any other student. ... So I've done what you said, went to the switchboard, highlighted the combo ... This one opens the form to a specific record. ...
    (microsoft.public.access.forms)
  • Re: After School Attendance
    ... run a query for a reporting period, ... attendance somewhere so we can run queries and reports for a given time ... What does the "select student" in the header do? ... and it would very time consuming to create a roster each site. ...
    (microsoft.public.access.tablesdbdesign)