?
Solved

Select Distinct not selecting distinct

Posted on 2004-08-09
9
Medium Priority
?
1,065 Views
Last Modified: 2010-08-05
I'm trying to pull employee information for a VB6 database (Access 2000 back-end).  I thought my Select statements were up to par, but I guess not...

I'm using ADO controls on the form and changing the recordsource based on what User chooses on previous menus.  Works fine, but when I ask it for Distinct, I still get the same list with duplicate names.  Now, these duplicate names are valid entries (the dates are all different), but I will eventually need to pull an "Agent Average" report, which means I must be able to pull every agent independently and I had hoped to pull them all together for a master list of agents, and then pull them individually.  And if anyone has any other ideas on how that could be accomplished I'm listening...  But my main problem is: Why won't this select distinct?

Dim strQry As String

strQry = "Select Distinct AgentName, Date, AgentID, Supervisor, CallsA, Duration, Ready, CNX, Typing, Idle, Payments, (Left(Duration,2)) as DurationHour, (Mid(Duration,4,2)) as DurationMin, (Right(Duration,2)) as DurationSec, (Round(((DurationHour*3600)+(DurationMin*60)+DurationSec)/3600,2)) as DurationTotal, (Left(Ready,2)) as ReadyHour, (Mid(Ready,4,2)) as ReadyMin, (Right(Ready,2)) as ReadySec, (Round(((ReadyHour*3600)+(ReadyMin*60)+ReadySec)/3600,2)) as ReadyTotal, (Left(CNX,2)) as CNXHour, (Mid(CNX,4,2)) as CNXMin, (Right(CNX,2)) as CNXSec, (Round(((CNXHour*3600)+(CNXMin*60)+CNXSec)/3600,2)) as CNXTotal, (Left(Typing,2)) as TypingHour, (Mid(Typing,4,2)) as TypingMin, (Right(Typing,2)) as TypingSec, (Round(((TypingHour*3600)+(TypingMin*60)+TypingSec)/3600,2)) as TypingTotal, (Left(Idle,2)) as IdleHour, (Mid(Idle,4,2)) as IdleMin, (Right(Idle,2)) as IdleSec, (Round(((IdleHour*3600)+(IdleMin*60)+IdleSec)/3600,2)) as IdleTotal, (Round(ReadyTotal/DurationTotal,4)*100 & '%') as ReadyPer, (Round(CNXTotal/DurationTotal,4)*100 & '%') as CNXPer, (Round(TypingTotal/DurationTotal,4)*100 & '%') as TypingPer, (Round(IdleTotal/DurationTotal,4)*100 & '%') as IdlePer, (Round(Payments/CallsA,4)*100 & '%') as PayPer, (Round(CallsA/DurationTotal,2)) as CPH From qryDialer "

Select Case strSearch
    Case Is = "Supervisor"
        Select Case strSort
            Case Is = "AgentName"
                strQry = strQry & "Where Supervisor = '" & strSearch2 & "' And Date Between " & dateFrom & " And " & dateTo & " Order by " & strSort & " " & strOrder & ", Date"
                adoDisplay.RecordSource = strQry
            Case Is = "Duration"
                strQry = strQry & "Where Supervisor = '" & strSearch2 & "' And Date Between " & dateFrom & " And " & dateTo & " Order by " & strSort & " " & strOrder & ", AgentName"
                adoDisplay.RecordSource = strQry
            Case Is = "Date"
                strQry = strQry & "Where Supervisor = '" & strSearch2 & "' And Date Between " & dateFrom & " And " & dateTo & " Order by " & strSort & " " & strOrder & ", AgentName"
                adoDisplay.RecordSource = strQry
            Case Is = "CNX"
                strQry = strQry & "Where Supervisor = '" & strSearch2 & "' And Date Between " & dateFrom & " And " & dateTo & " Order by " & strSort & " " & strOrder & ", AgentName"
                adoDisplay.RecordSource = strQry
            Case Is = "Ready"
                strQry = strQry & "Where Supervisor = '" & strSearch2 & "' And Date Between " & dateFrom & " And " & dateTo & " Order by " & strSort & " " & strOrder & ", AgentName"
                adoDisplay.RecordSource = strQry
            Case Is = "AgentID"
                strQry = strQry & "Where Supervisor = '" & strSearch2 & "' And Date Between " & dateFrom & " And " & dateTo & " Order by " & strSort & " " & strOrder & ", Date"
                adoDisplay.RecordSource = strQry
            Case Is = "Typing"
                strQry = strQry & "Where Supervisor = '" & strSearch2 & "' And Date Between " & dateFrom & " And " & dateTo & " Order by " & strSort & " " & strOrder & ", AgentName"
                adoDisplay.RecordSource = strQry
            Case Is = "CallsA"
                strQry = strQry & "Where Supervisor = '" & strSearch2 & "' And Date Between " & dateFrom & " And " & dateTo & " Order by " & strSort & " " & strOrder & ", AgentName"
                adoDisplay.RecordSource = strQry
            Case Is = "Idle"
                strQry = strQry & "Where Supervisor = '" & strSearch2 & "' And Date Between " & dateFrom & " And " & dateTo & " Order by " & strSort & " " & strOrder & ", AgentName"
                adoDisplay.RecordSource = strQry
            Case Is = "Payments"
                strQry = strQry & "Where Supervisor = '" & strSearch2 & "' And Date Between " & dateFrom & " And " & dateTo & " Order by " & strSort & " " & strOrder & ", AgentName"
                adoDisplay.RecordSource = strQry
        End Select...

...ad nauseum, eventually, adoDisplay.Refresh

Probably put more than needed, but I don't want the mistake missed.  Please keep in mind, this program is otherwise fully functioning.

Here's all the points I have.  Thank you!
0
Comment
Question by:combatko
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 6

Expert Comment

by:lausz
ID: 11756313
When you use DISTINCT in the select statement... all the fields you are selecting in it must be different.. if only one is different in the complete select you will have more than one line.
0
 

Author Comment

by:combatko
ID: 11756381
Um, the only field that would have a duplicate would be Date...  The rest are so random that matching it would take an act of Dog.  Maybe I just don't understand exactly what you mean.
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11756386
AS lausz says, all the columns must be distinct, e.g. you have the records -
AgentName   Date
Joe               2004/01/01
Joe              2004/02/01
Fred            2004/03/01


SELECT DISTINCT AgentName FROM MYTABLE
will return
Joe
Fred

But,
SELECT DISTINCT AgentName, Date FROM MyTable
will return
Joe 2004/01/01
Joe 2004/02/01
Fred 2004/03/01

Think about it, if it were to only return one row for joe, what date should it return?

If you want, you could put an aggregate function on date - e.g. MAX

SELECT DISTINCT AgentName, MAX(Date) FROM MyTable GROUP BY AgentName

will return

Joe 2004/02/01
Fred 2004/03/01
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

Author Comment

by:combatko
ID: 11756440
Honestly, I don't care which record it returns for Joe as long his name is there, and only there once.  Of course I do see the need to clarify (now), and thank you.  Are there any ideas on making this work with such a large Select statement?
0
 
LVL 6

Expert Comment

by:lausz
ID: 11756513
Can you post an example ... to clarify what you need ?
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 860 total points
ID: 11756535
If all you want is the name, then just select that :
SELECT Distinct AgentName FROM qryDialer

for all the other columns, you have to decide what is it you want when there are multiple rows - do you want tte database to average them out, select the biggest, the smallest, the sum etc.
 If you really don;t care, I suggest you just do MIN() for each of the other columns you have, or as you say in your initial question you want an "Agent Average" report - maybe what you want is AVG()
Remember though, avg() won't work for a text field, although min and max will.
0
 

Author Comment

by:combatko
ID: 11756618
BillAn1 - "If all you want is the name, then just select that : SELECT Distinct AgentName FROM qryDialer" - Man, I wish....

Wait a minute.  Why not?  Then go back and For Each or something...  Eegah!  I apologize for being so obtuse and bothersome to such wise and benevelent personages such as yourselves!  Please excuse this poor excuse for a programmer - I beat my chest in shame!  May the fleas of a thousand space monkeys infest my armpits...

No really, thanks.  I can't believe I didn't think of if that way!  You got 'em.
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11756777
??? If you go back and do "for each", you are still going to get multiple rows for each agent. You won't really have benefited.
What you really need to do is decide ultimately, if you only want one row, how you wnat the database to decide which of the many to give you, or alternatively how to 'merge' the multiple ones together if the columns have different values.
One other way of course, if you really don;t care which record is to do SELECT TOP 1 ...........
and if you do a SORT BY at the end, you cen decide which column to use to pick the 1 record on.....
0
 

Author Comment

by:combatko
ID: 11757020
Great!  Thanks again.  Really, I was just thinking out loud.  I meant more of an "For each listing that I have for each name, I can run something that will merge them into the average numbers that I need for each associate."  I'm just used to the For Each...  Yeah, the only problem I'll have is that most of the fields have been saved as text, but I've been using left() and right() to pull out what I need.

Thanks for the follow-up.  Most people might not care, now that they've gotten the points.  You rock!
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question