Solved

Select Distinct not selecting distinct

Posted on 2004-08-09
9
1,031 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
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.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
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…

831 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