473,695 Members | 2,733 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Designing query to only show unique records

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_stu dents 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
4 31253
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_stu dents 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
On Jun 28, 2:29 pm, ManningFan <manning...@gma il.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_stu dents 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.St udent_Record, Student_Info.Gr ade,
Student_Info.Sc hoolName, Session_Informa tion.Counselor,
Session_Informa tion.Date, Student_Info.[Student Last]
FROM Student_Info INNER JOIN (Session_Inform ation INNER JOIN
Services_To_Stu dents ON Session_Informa tion.[Session#] =
Services_To_Stu dents.[Session#]) ON Student_Info.St udent_Record =
Services_To_Stu dents.Student_R ecord
GROUP BY Student_Info.St udent_Record, Student_Info.Gr ade,
Student_Info.Sc hoolName, Session_Informa tion.Counselor,
Session_Informa tion.Date, Student_Info.[Student Last]
HAVING (((Session_Info rmation.Counsel or)<>"All Counselors" And
(Session_Inform ation.Counselor )<>"Paid Tutor/counselor") AND
((Session_Infor mation.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
kdubble wrote:
That didn't work:

here's my SQL view

SELECT DISTINCT Student_Info.St udent_Record, Student_Info.Gr ade,
Student_Info.Sc hoolName, Session_Informa tion.Counselor,
Session_Informa tion.Date, Student_Info.[Student Last]
FROM Student_Info INNER JOIN (Session_Inform ation INNER JOIN
Services_To_Stu dents ON Session_Informa tion.[Session#] =
Services_To_Stu dents.[Session#]) ON Student_Info.St udent_Record =
Services_To_Stu dents.Student_R ecord
GROUP BY Student_Info.St udent_Record, Student_Info.Gr ade,
Student_Info.Sc hoolName, Session_Informa tion.Counselor,
Session_Informa tion.Date, Student_Info.[Student Last]
HAVING (((Session_Info rmation.Counsel or)<>"All Counselors" And
(Session_Inform ation.Counselor )<>"Paid Tutor/counselor") AND
((Session_Infor mation.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_Informa tion table in your output.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jun 28 '07 #4
On Jun 28, 4:16 pm, kdubble <khaw...@gmail. comwrote:
On Jun 28, 2:29 pm, ManningFan <manning...@gma il.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_stu dents 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.St udent_Record, Student_Info.Gr ade,
Student_Info.Sc hoolName, Session_Informa tion.Counselor,
Session_Informa tion.Date, Student_Info.[Student Last]
FROM Student_Info INNER JOIN (Session_Inform ation INNER JOIN
Services_To_Stu dents ON Session_Informa tion.[Session#] =
Services_To_Stu dents.[Session#]) ON Student_Info.St udent_Record =
Services_To_Stu dents.Student_R ecord
GROUP BY Student_Info.St udent_Record, Student_Info.Gr ade,
Student_Info.Sc hoolName, Session_Informa tion.Counselor,
Session_Informa tion.Date, Student_Info.[Student Last]
HAVING (((Session_Info rmation.Counsel or)<>"All Counselors" And
(Session_Inform ation.Counselor )<>"Paid Tutor/counselor") AND
((Session_Infor mation.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.Gr ade. 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.Cou nselor)<>"All Counselors"
And (Session_Inform ation.Counselor )<>"Paid Tutor/counselor")
AND ((Session_Infor mation.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
8995
by: Beat Scheidiger | last post by:
I do not quite understand this property. Everything is seems clear to me, when I read the corresponding help text. But in practice I have a question: Why are there 3 identical records in the following query (with property 'Unique records' = Yes)? SELECT DISTINCTROW tblCustomer., tblCustomer., tblCustomer., tblOrders., tblOrders., tblOrders.Artikel FROM tblCustomer INNER JOIN tblOrders ON tblCustomer. = tblOrders.;
2
2239
by: John Baker | last post by:
Hi: I have a sub form which i would like to show all the records selected from a table. There may well be over 100 of them. For reasons I cant fathom, the form will only show six, and then requires that you move using the record selection box to see any records below that. I would like the whole record set to be available using the vertical slider, since this is much more convenient for the user. Cab someone suggest what is wrong. The...
5
8298
by: Terri | last post by:
I have a form with a multi-select combo. I dynamically build a SELECT statement, open a report, and set the recordsource to my dynamic SELECT statement. I count the records returned in the report by setting a text box to =Count(*). This works fine. Now I want to count the unique records in my report. I can dynamically create a SELECT statement that counts unique records. My statement looks like this: SELECT DISTINCT...
1
2991
by: snOOp | last post by:
I am trying to combine the data from two similar tables into one query, but I need for all of the records from both tables to show up and I want the ones that have matching 'emplid' to be combined into one record showing both the 'empstatus' and 'strole' fields. The following query works, but does not combine the matching records: SELECT givenname, sn, empstatus, emplid, ssn FROM dbo_EmpData WHERE empstatus='A' UNION ALL SELECT...
8
9084
by: Gem | last post by:
Hi I'm struggling with a query returning too many records. I have 3 related tables. tblPatients tblOperations tblTargets Each patient in tblPatients can have more than one operation (one-many) Each Operation in tblOperations can have many targets (many-many)
2
3891
by: banderson | last post by:
Hello, I have a data entry form for a table with information about buildings and am having a problem making a combo box do what I want. I would like to make the combo box show a list of unique bldg mgmt company names and then to open a building management company form to show all records with this name, so the user can find the correct branch location to select. Then, upon closing the building management company form, the active/selected record...
2
4444
by: srusskinyon | last post by:
I need some help getting unique records from our database! I work for a small non-profit homeless shelter. We keep track of guest information as well as what services we have offered for statistical purposes. I've been using Here's the situation: I have two main tables:
1
1568
by: jeff8676 | last post by:
I'm very new at Access, so if this is a lame question I'm sorry. Anyways, I'm trying to make a query that returns unique entries from a table, but when it finds two entries where everything matches except for in this case the feild "Ammount" it returns the entry unique but adds the differing ammount entries. Bassically I'm trying to get the sum of ammounts for entries that are otherwise duplicates.
3
1571
by: pixiedust | last post by:
I have a database based on fiscal years: i.e. 2007/2008 2008/2009 etc. Is there a way to only see the current fiscal (2008/2009) and make the prior fiscal years hidden (invisible)? The field is a text box to house the fiscal years. Thanks! Pixie
0
8640
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8582
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9122
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9001
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8860
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8832
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6498
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4348
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
1984
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.