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,

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

StackExchange Database Administrators Q#22410, answer score: 5