remove duplicates by max value of three columns

i have a join of a "customers" table to three related tables: "sales","Parts","service"
the problem is wanting to remove duplicates based on the maximum date value found in any of the three tables. i'm getting output like this..
KAREN      DION      11 MAIN ST      city      zip           state     2310499             
KAREN      DION      11 MAIN ST      city      zip            state       9741774
so, the person has two account codes.(the last field). the good record is the one with the most current date found any of the three related tables.      
here's my sql so far...

SELECT DISTINCT
                       Customers.FirstName, Customers.LastName, Customers.Street, Customers.city, Customers.zip,   Customers.AccountCode, Customers.State, Customers.Store
FROM         Customers LEFT OUTER JOIN
                      Parts ON Customers.AccountCode = Parts.AccountCode LEFT OUTER JOIN
                      Sales ON Customers.AccountCode = Sales.AccountCode LEFT OUTER JOIN
                      Service ON Customers.AccountCode = Service.AccountCode
   
WHERE     (Customers.Store = N'plai') AND (Sales.Unit_1_Date > '01/01/2001') and (Sales.Store = N'plai') and Customers.LastName = 'dion' OR
                      (Customers.Store = N'plai') AND (Service.Ro_Date_1 > '01/01/2001') and (Service.Store = N'plai')and Customers.LastName = 'dion' OR
                  (Customers.Store = N'plai') AND (Parts.Invoice_1_Date > '01/01/2001')and (Parts.Store = N'plai') and Customers.LastName = 'dion'

ugly huh?

jim


    Start Free Trial