Yet another mental block or caffine overload: [NB: posted on Access forum too]

I have to use MS Access to get & process ORACLE DB data (as this forms a subset of a MS Access based report)

I have an ORACLE DB of devices [CELL_NAME](text) that get variable accesses [TCH_CH_AVAIL] [TMP_CHGR] [TMP_SDCCH ] (number)throught the month.

I need to get the max number of access [TCH_CH_AVAIL] and the date it occured for each device as well as the other access types for that date [TMP_CHGR] [TMP_SDCCH ].

Each set of devices sits off a hub [SWIT_CAT_CODE] and there is a check that that device is hubbed as planned.

[A pole is taken for each day, each device may or may not be on line every day, so I need to get the last week's data and find the max access)

The ACCESS pass through query is:


SELECT DISTINCT
d.SWIT_CAT_CODE BSC,
d.TMP_CELL CELL,
f.TMP_CHGR CHGR,
f.TMP_SDCCH SDCCH,
w.TCH_CH_AVAIL TCH,
d.TMP_XRANGE XRANGE,
w.summary_date

FROM RLDEP d, WD_CELL_S w, RLCFP f

WHERE (w.BSC_NAME=d.SWIT_CAT_CODE)
AND (w.CELL_NAME=d.TMP_CELL)
AND (w.SUMMARY_DATE >= ADD_MONTHS(LAST_DAY(SYSDATE)-4, -1))AND(w.SUMMARY_DATE <= ADD_MONTHS(LAST_DAY(SYSDATE), -1))
AND (f.SWIT_CAT_CODE=w.BSC_NAME)
AND (f.TMP_CELL=w.CELL_NAME)
ORDER BY
d.TMP_XRANGE,
d.SWIT_CAT_CODE,
d.TMP_CELL