Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

returning distinct values for database field

Status
Not open for further replies.

aonefun

Technical User
May 21, 2007
79
US
How do I set this query to return only distinct values for DocumentHeaders.SoldToFax? (I thought this is accomplished by the GROUP BY function).

SELECT DocumentHeaders.SoldToFax, Min(DocumentHeaders.SoldToCompany) AS MSoldToCompany, Min(DocumentHeaders.DataSource) AS MinOfDataSource, Min(DocumentHeaders.Remove) AS MinOfRemove, Min(DocumentHeaders.catagoryID) AS MinOfcatagoryID
FROM DocumentHeaders
GROUP BY DocumentHeaders.SoldToFax
 
you are absolutely correct, that query will produce one row per SoldToFax

the groups in a GROUP BY query are by definition distinct

r937.com | rudy.ca
 
maybe try DISTINCT:

Code:
SELECT DISTINCT  DocumentHeaders.SoldToFax, Min(DocumentHeaders.SoldToCompany) AS MSoldToCompany, Min(DocumentHeaders.DataSource) AS MinOfDataSource, Min(DocumentHeaders.Remove) AS MinOfRemove, Min(DocumentHeaders.catagoryID) AS MinOfcatagoryID
FROM DocumentHeaders
GROUP BY DocumentHeaders.SoldToFax



Leslie

In an open world there's no need for windows and gates
 
Thanks for your responseS. Here is the full code that still returns duplicate DocumentHeaders.SoldToFax or Document.ShipToFax values:


(SELECT DISTINCT DocumentHeaders.SoldToFax, Min(DocumentHeaders.SoldToCompany) AS MSoldToCompany, Min(DocumentHeaders.DataSource) AS MinOfDataSource, Min(DocumentHeaders.Remove) AS MinOfRemove, Min(DocumentHeaders.catagoryID) AS MinOfcatagoryID
FROM DocumentHeaders
GROUP BY DocumentHeaders.SoldToFax
HAVING (((DocumentHeaders.SoldToFax)<>""))
UNION
SELECT DISTINCT DocumentHeaders.ShipToFax, Min(DocumentHeaders.ShipToCompany) AS MShipToCompany, Min(DocumentHeaders.DataSource) AS MinOfDataSource, Min(DocumentHeaders.Remove) AS MinOfRemove, Min(DocumentHeaders.catagoryID) AS MinOfcatagoryID
FROM DocumentHeaders
GROUP BY DocumentHeaders.ShipToFax
HAVING (((DocumentHeaders.ShipToFax)<>"")))
UNION (SELECT faxnumbers.SoldToFax, faxnumbers.MSoldToCompany, faxnumbers.DataSource, faxnumbers.Remove, faxnumbers.CatagoryID
FROM faxnumbers);
 
aonefun, if you show query 1 and get an answer, please do not expect the answer to apply also to query 2 which is substantially different

also, please don't keep creating new threads for essentially the same problem


leslie, the groups in a GROUP BY query are distinct by definition

there's no need to add DISTINCT

r937.com | rudy.ca
 
I thought that having all those mins and grouping by the number should only return a single record for each one, but figured, what the hay!

Glad to know that I was right in my original thinking!!

les
 
if all that you want is a distinct list of all fax numbers
from soldtofax and shiptofax all that you need is


Code:
SELECT SoldToFax As fax
FROM DocumentHeaders
WHERE SoldToFax <> ''
union 
SELECT ShipToFax
FROM DocumentHeaders
WHERE ShipToFax <> '' 
UNION 
SELECT SoldToFax FROM faxnumbers;


MS-Access Help said:
By default, no duplicate records are returned when you use a UNION operation; however, you can include the ALL predicate to ensure that all records are returned. This also makes the query run faster.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top