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!

group by function to eliminate duplicates in union query 2

Status
Not open for further replies.

aonefun

Technical User
May 21, 2007
79
US
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)<>"")));
 
Code:
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
 
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 <> ''
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top