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 < 1110809 AND OM01U1.OM01068<>'S'