Solved

Select Distinct not selecting distinct

Posted on 2004-08-09
9
997 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
  • 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
 

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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 215 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now