group by function to eliminate duplicates in union query 2

[aonefun]

How would I alter the following code so that the Group By function eliminates duplicates from the DocumentHeaders.SoldToFax and DocumentHeaders.ShipToFax fields combined (as a result of the UNION query)(In other words, when the UNION query combines all fax numbers from both the SoldTo and ShipTo fax numbers, there are no duplicated fax number entries)?

(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
HAVING (((DocumentHeaders.SoldToFax)<>""))
UNION
SELECT 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)<>"")));

 

[r937]

SELECT SoldToFax AS Fax
     , Min(MSoldToCompany)  AS SoldToCompany
     , Min(MinOfDataSource) AS DataSource
     , Min(MinOfRemove)     AS Remove
     , Min(MinOfcatagoryID) AS catagoryID
  FROM (
       SELECT SoldToFax
            , Min(SoldToCompany) AS MSoldToCompany
            , Min(DataSource)    AS MinOfDataSource
            , Min(Remove)        AS MinOfRemove
            , Min(catagoryID)    AS MinOfcatagoryID
         FROM DocumentHeaders
        WHERE SoldToFax <> ''
       GROUP
           BY SoldToFax
       UNION ALL
       SELECT ShipToFax
            , Min(ShipToCompany) AS MShipToCompany
            , Min(DataSource)    AS MinOfDataSource
            , Min(Remove)        AS MinOfRemove
            , Min(catagoryID)    AS MinOfcatagoryID
         FROM DocumentHeaders
        WHERE ShipToFax <> ''
       GROUP
           BY ShipToFax
       ) AS d
GROUP
    BY SoldToFax

r937.com | rudy.ca

 

[PWise]

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

SELECT SoldToFax As fax
FROM DocumentHeaders
WHERE SoldToFax <> ''
union 
SELECT ShipToFax
FROM DocumentHeaders
WHERE ShipToFax <> ''

 

[aonefun]

In regard to the initial query code provided:

I am creating a web application inwhich someone can remove his fax number from our database by setting the fax number's corresponding faxremove field to equal "yes".

Being that part of the fax list is being fed by a quoting app and duplicate fax numbers will be added whenever a quote is given to an existing customer, how can I be sure that the faxnumber record containing a corresponding faxremove value is always the record that is showing in the design view of the query containing the "group by function"? If not, setting the faxremove value is not accurate.