SQL Query with ODBC, Joins and Group Bys I am having issues with the statement below.  The main purpose of the query is to pull a list of customers into an Access db from ODBC (our customers are stored in DB2 on IBM i).  The query is intended to look for a customer (OM01U1.OM01015) that has not had a delivery (RS2101F.DELDATE) in over 13 months but is not already suppressed (OM01U1.OM01068).  I do need all of the columns in order to research the equipment and financial records so I can't remove them.  The statement is pulling from several tables though.  I believe that the issue may be with the "Group By".  I am also getting duplicate outlets (OM01U1.OM01015) when I do receive results.  I only need to see the outlet one time with it's last delivery date (RS2101F.DELDATE). Please don't beat me up too bad, I did not create this query I just sort of inherited it.  I know it is way off and there is probably a better way for me to get the information I need.  I appreciate any help guys!

SELECT
    OM01U1.OM01041 as Loc,
    OM01U1.OM01015 AS Outlet, 
    OM01U1.OM01945 AS OLTyp,
    OM01U1.OM01052 AS Outlet_Name,
    OM01U1.OM01054A AS Street_Number, 
    OM01U1.OM01054C AS Street_Name, 
    OM01U1.OM01055A AS City, 
    OM01U1.OM01055B AS State, 
    OM01U1.OM01106 AS Zip, 
    OM01U1.OM01058A AS Area_Code, 
    OM01U1.OM01058C AS Phone, 
    OM01U1.OM01037 AS Channel, 
    OM01U1.OM01926 AS USA_Type,
    OM01U1.OM01078 AS Key_Acct, 
    OM01U1.OM01110 AS TRDGRP,
    OM01U1.OM01034 AS Trade_Name, 
    OM01U1.OM01248 AS DTC,
    OM01U1.OM01073 AS Sales_Route, 
    OM01U1.OM01068 AS Sup_CDE, 
    OM01U1.OM01982 AS Sup_Reason, 
    OM01U1.OM01067 AS Sup_Date, 
    OM01U1.OM01065 AS Creation_DT, 
    OM01U1.OM01066 AS Update_DT, 
    OM01U1.OM01141 AS CAN_NUM,
    CF30_CNSTYP AS CF_CONTYP, 
    EC01_EQUNUM AS EC_EQUIP,
    EC01_PRMSLR AS EC_SUPPLIER, 
    EC01_EQUOWN AS EC_OWN_CD,
    EC01_EQUACY AS EC_ACCESSORY,
    EC01_EQUSPP AS EC_COMPONENT,
    EC01_SYSSTA AS EC_STATUS,
    EC01_ISTDAT AS EC_INSTALL_DT, 
    EC01_RSPPRN AS EC_PERSON, 
    EC01_LASMTCDAT AS EC_LAST_MNT_DT, 
    OC02_RNTCTCNUM AS OC_CONT_NUM, 
    OC02_RNTCTCSTA AS OC_CONT_STATUS, 
    OC02_BTONUM AS OC_BILL_TO, 
    RS2101F.DELDATE AS RS_LAST_DEL_DT
  FROM 
    CF30, EC01, OC02, OM01U1, RS2101F
  WHERE 
    OM01U1.OM01015 = RS2101F.OUTNUM

  GROUP BY
    RS2101F.DELDATE,
    OC02_BTONUM,
    OC02_RNTCTCSTA,
    OC02_RNTCTCNUM,
    OC02_OUTNUM,
    EC01_LASMTCDAT,
    EC01_RSPPRN,
    EC01_ISTDAT,
    EC01_EQUOWN,
    EC01_EQUACY,
    EC01_EQUSPP,
    EC01_SYSSTA,
    EC01_PRMSLR,
    EC01_EQUNUM,
    CF30_CNSTYP,
    OM01U1.OM01141,
    OM01U1.OM01066,
    OM01U1.OM01065,
    OM01U1.OM01067,
    OM01U1.OM01982,
    OM01U1.OM01068,
    OM01U1.OM01073,
    OM01U1.OM01248,
    OM01U1.OM01034,
    OM01U1.OM01110,
    OM01U1.OM01078,
    OM01U1.OM01926,
    OM01U1.OM01037,
    OM01U1.OM01058C,
    OM01U1.OM01058A,
    OM01U1.OM01106,
    OM01U1.OM01055B,
    OM01U1.OM01055A,
    OM01U1.OM01054C,
    OM01U1.OM01054A,
    OM01U1.OM01052,
    OM01U1.OM01945,
    OM01U1.OM01015,
    OM01U1.OM01041
  HAVING  RS2101F.DELDATE &lt; 1110809
      AND OM01U1.OM01068&lt;&gt;'S'
You have a working query, but you are selecting:

FROM CF30, EC01, OC02, OM01U1, RS2101F 


With no explicit joins and only one implicit join:

WHERE OM01U1.OM01015 = RS2101F.OUTNUM


This is going to lead to problems. Can you find which fields (columns) match to which in each table? You could then say:

FROM OM01U1 
INNER JOIN RS2101F
ON OM01U1.OM01015 = RS2101F.OUTNUM


For example.

You may also find it useful to add a derived table:

SELECT OUTNUM, Max(RS2101F.DELDATE) FROM RS2101F


So that would then read:

FROM OM01U1 
INNER JOIN (
     SELECT OUTNUM, Max(RS2101F.DELDATE) 
     FROM RS2101F
     GROUP BY OUTNUM) As t
ON OM01U1.OM01015 = t.OUTNUM