SELECT
T1.SITE_ID
,T1.COMPNY
,T1.CUSTOMER_NO
,T1.LAT
,T1.[LONG]
,R1.ZIP_GROUP
,R1.LAT_AVG
FROM (
SELECT T1.SITE_ID, T1.COMPNY, T1.CUSTOMER_NO, T1.ZIP_GROUP, T1.LAT, T.LONG
FROM
SELECT Q.SITE_ID, Q.COMPNY, Q.CUSTOMER_NO, Q.ZIP_GROUP, T.LAT, T.LONG
FROM MAS_ACCTtbl AS Q
INNER JOIN LAT_LONG_FINAL AS T
ON (Q.CUSTOMER_NO =T.CUSTOMER_NO) AND (Q.COMPNY =T.COMPANY) AND (Q.SITE_ID = T.SITE_ID)
GROUP BY Q.SITE_ID, Q.COMPNY, Q.CUSTOMER_NO, Q.ZIP_GROUP, T.LAT, T.LONG ) AS T1
INNER JOIN (
SELECT R1.SITE_ID,
R1.ZIP_GROUP , R1.LAT , R1.[LONG]
FROM(
SELECT R.SITE_ID , R.ZIP_GROUP , AVG(R.LAT) AS LAT_AVG , AVG(R.LONG) AS LONG_AVG
FROM (
SELECT Q.SITE_ID, Q.COMPNY, Q.CUSTOMER_NO, Q.ZIP_GROUP, T.LAT, T.LONG
FROM MAS_ACCTtbl AS Q INNER JOIN LAT_LONG_FINAL AS T ON (Q.CUSTOMER_NO =T.CUSTOMER_NO) AND (Q.COMPNY =T.COMPANY) AND (Q.SITE_ID = T.SITE_ID)
GROUP BY Q.SITE_ID, Q.COMPNY, Q.CUSTOMER_NO, Q.ZIP_GROUP, T.LAT, T.LONG) AS R
GROUP BY R.SITE_ID , R.ZIP_GROUP )) AS R1
ON T1.SITE_ID = R1.SITE_ID AND T1.ZIP_GROUP = R1.ZIP_GROUP
can't sort out what access is looking for in the way of ( ). this is pretty straightforward in oracle or ibm. any ideas- rwhite7