prioritize duplicates

[medic61]

I thought I posted this yesterday, but don't see my thread, sorry if it's a dup.

I have a table of address's from multiple sources. I have a query to pull the info I need, but there are many duplicates. I can't seem to find how to display the results on a priority basis. My source field is called "Co", what i want to do is if the duplicate is Co=9 show it only, then if it's in Co=5, then Co=2 etc.

Can I do this as a subquery or make my original query a sub of this? All I can find is First/Last/Max/Min type info. Thanks for your help.

Shane

 

[traingamer]

I don't understand the question.

Is Co a priority field that is separate from the address field?

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[medic61]

Yes, Co would be the field i want to use to prioritize the output. so if I had this:

John Doe555-1212 Co=1
Mike Fits 555-1212 Co=9
Julie Adams 555-1212 Co=2

Then it should output

Mike Fits 555-1212

 

[lameid]

So assuming your field names are Name, ID and CO in Table1 and you want the priortiy based on ID being highest matching the ID field, a soltuion...

Select [Name], ID
From Table1 as X
Where CO IN(Select Max(Y.CO)
            From Table1 As Y
            Where Y.ID = X.ID)

 

[medic61]

So here's my current sql:

SELECT Main.Name, Trim([Other]&" "&[Address] & " " & [City] & " " & [Zip]) AS Expr1, Main.Phone
FROM Main
ORDER BY Main.Sort, Main.Lines;

Here's what I changed it to:

SELECT Main.Name, Trim([Other] & " " & [Address] & " " & [City] & " " & [Zip]) AS Expr1, Main.Phone
FROM Main AS X
WHERE Co IN(Select Max(Y.Co) From Main As Y Where Y.ID = X.ID)
ORDER BY Main.Sort, Main.Lines;


When I change it to use that code, It wan't me to do 3 inputs and then shows the Name as "9" if that's what I put in the popup box. I'm missing something.

 

[lameid]

I don't see CO in your existing query.

Your sample data did not have field names so I made up sample column names. You would have to subsitute the field names in your query.

It would also appear that your query will not return your sample data...

 

[medic61]

My original query just returned every record, sorted. The CO was not in the original query because I didn't know how to use it. I don't need it to display, I just need it to find the duplicates. Does this make sense?

 

[BigRed1212]

It doesn't make sense to me.

Perhaps you could provide a slightly larger sample data set that included some duplicates about which you are concerned. The three record set previously provided does not seem to have duplicate records and, for me, does not clarify the problem nor demonstrate the desired solution.

Data like:
Mike Fits 555-1212 Co=1
Mike Fits 555-1212 Co=9
Mike Fits 555-1212 Co=5

would only require use of max(Co).

 

[medic61]

BigRed, max(Co) may work.....never used it before that's why I'm asking. The sample does have dups, 3 numbers all 555-1212. That's what I want to have it look for and if there are dups, then pick the "Co" that has the largest number to display in my query, my query just shows everything. My original query just pulled everything out of the table and sorted it by a field called "sort" then by "lines; therfore, I would have all three names all with the same number.

 

[lameid]

Post the SQL of the results you want to see with duplicates and your 'CO' field, the associated sample data and the desired result.

 

[medic61]

Ok, let me start over. Here is a sample of the data:

Name Address City Zip Co
John Doe 123 Main St 12345 555-1212 1
Mike Fits 542 Joy Ave 12345 555-1212 9
Julie Adams 111 Ellen St 12346 555-1212 2
Tim Eliot 12 Center St 12346 555-2323 9
Mike Mills 5 Main St 12346 555-1213 5

Here's my current Query to export this data:

SELECT Main.Name, Trim([Other]&" "&[Address] & " " & [City] & " " & [Zip]) AS Expr1, Main.Phone
FROM Main
ORDER BY Main.Sort, Main.Lines;

Here's the result I need (since there are 3 phone numbers 555-1212 then only display the one with the largest Co Number):

Tim Eliot 12 Center St 12346 555-2323
Mike Fits 542 Joy Ave 12345 555-1212
Mike Mills 5 Main St 12346 555-1213

Notice: The Co is not to be displayed, only use to remove duplicates.

I don't know the sql to make this happen. I'm assuming, based on responses already, that the "Max" function to needs be placed in my query, but I don't know where or the correct syntax.

Thanks again!

 

[randy700]

I'm kind of surprised this worked, but....

SELECT Max(Main.FullName) AS MaxOfFullName, Max(Trim([Address] & " " & [zip])) AS Expr1, Main.Phone, Max(Main.CO) AS MaxOfCO
FROM Main
GROUP BY Main.Phone

Randy

 

[lameid]

If Randy's solution work it is by luck not design....

So Main.Phone is your column equivalent to my ID example,
Main is the table equivalent to Table1 and lastly you had one column which I called [Name] but you have several, substituting...

Select Name, Trim([Other]&" "&[Address] & " " & [City] & " " & [Zip]) AS Expr1, Phone
From Main as X
Where CO IN(Select Max(Y.CO)
            From Main As Y
            Where Y.Phone = X.Phone)

 

[BigRed1212]

lameid, I like your query lots better (no offense randy!) but I think yours misses straight out duplicates in which everything in the record is exactly the same. I think adding DISTINCT fixes that.

I am likely wrong.

 

[randy700]

If Randy's solution work it is by luck not design

I agree. With further testing (more records) it fails miserably.

Randy