Problems with Query - Duplicate Companies

[crhodus]

Hi! Here is the query that I have written.

SELECT DISTINCT company.co_number, company.name, company.county, products.product_code
FROM company
INNER JOIN products ON
COMPANY.co_number = products.co_number
WHERE company.county IN ('LAFAYETTE', 'LEAKE', 'LEE')
ORDER BY company.county, company.name ;

Here is an example of what the query is returning.

331 Acme LAFAYETTE A123
331 Acme LAFAYETTE T456
331 Acme LAFAYETTE E789
177 Allgood Lee J123
879 TBasic Lee P987
879 TBasic Lee X654

What I want is the query to return data to me like this:

331 Acme LAFAYETTE A123
177 Allgood Lee J123
879 TBasic Lee X654

I don't care which product code is returned, just as long as I have one for each company. I've written a query in the past that has returned this type of results, but I can't remember what I did. I think I might have used a MAX statement and another SELECT statement to produce the desired results.

Cany anyone help?

 

[pbaldy]

Try changing it to a totals query (the little icon that looks like an E, or View/Totals), with Group By on the first 3 fields in the SELECT and Max on the last.

 

[crhodus]

Thanks for your help. That seemes to have resolved my problem.

SELECT DISTINCT company.co_number, company.name, company.county, MAX(products.product_code)
FROM company
INNER JOIN products ON
COMPANY.co_number = products.co_number
WHERE company.county IN ('LAFAYETTE', 'LEAKE', 'LEE')
GROUP BY company.co_number, company.name, company.county
ORDER BY company.county, company.name ;