Select Distinct not selecting distinct

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!
combatkoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lauszCommented:
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
combatkoAuthor Commented:
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
BillAn1Commented:
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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

combatkoAuthor Commented:
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
lauszCommented:
Can you post an example ... to clarify what you need ?
0
BillAn1Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
combatkoAuthor Commented:
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
BillAn1Commented:
??? 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
combatkoAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.