asked on

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!

lausz

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.

combatko

ASKER

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.

BillAn1

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

combatko

ASKER

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?

lausz

Can you post an example ... to clarify what you need ?

ASKER CERTIFIED SOLUTION

BillAn1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

combatko

ASKER

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.

BillAn1

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

combatko

ASKER

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!