MS Access ODBC error

[kiridad]

Trying to get Access query linking 3 (large) Teradata tables and 2 (small) Access tables to extract data from Teradata tables. Keep getting 'ODBC call failed' error message. Simpler query successfully pulled specified data. Working with Teradata ODBC driver 2.06.00.03. Will break query down to smaller bits if necessary, but hope to get it to run successfully. Query script follows

PARAMETERS [Account Number] IEEEDouble, [Process Month] IEEEDouble;
SELECT DISTINCTROW CLAIM.OUTLETNR, Count(FVISS.FVADJTYCD) AS CountOfFVADJTYCD, FVADJTY.FVADJTYDE, Sum(FVISS.FITTLAMT) AS SumOfFITTLAMT, Count(FIISS.FIADJTYCD) AS CountOfFIADJTYCD, FIADJTY.FIADJTYDE, Sum(FIISS.[FICLMAMT]-FIISS.[FIPDAMT]) AS FIDIFF
FROM (CLAIM INNER JOIN (FVADJTY INNER JOIN FVISS ON FVADJTY.FVADJTYCD = FVISS.FVADJTYCD) ON (CLAIM.CLAIMNR = FVISS.CLAIMNR) AND (CLAIM.PROMO = FVISS.PROMO)) INNER JOIN (FIADJTY INNER JOIN FIISS ON FIADJTY.FIADJTYCD = FIISS.FIADJTYCD) ON FVISS.FV = FIISS.FV
WHERE (((CLAIM.ACCOUNTNR)=[Account Number]) AND ((CLAIM.PROMO)=[Process Month]))
GROUP BY CLAIM.OUTLETNR, FVADJTY.FVADJTYDE, FIADJTY.FIADJTYDE;

 

[tlbroadbent]

I suggest some query simplification and removal of the Distinctrow clause. Try the following query.

SELECT
CLAIM.OUTLETNR,
Count(FVISS.FVADJTYCD) AS CountOfFVADJTYCD,
FVADJTY.FVADJTYDE,
Sum(FVISS.FITTLAMT) AS SumOfFITTLAMT,
Count(FIISS.FIADJTYCD) AS CountOfFIADJTYCD,
FIADJTY.FIADJTYDE,
Sum(FIISS.[FICLMAMT]-FIISS.[FIPDAMT]) AS FIDIFF

FROM FVISS
INNER JOIN CLAIM ON FVISS.CLAIMNR = CLAIM.CLAIMNR AND FVISS.PROMO = CLAIM.PROMO
INNER JOIN FVADJTY ON FVISS.FVADJTYCD = FVADJTY.FVADJTYCD
INNER JOIN FIISS ON FVISS.FV = FIISS.FV
INNER JOIN FIADJTY ON FIISS.FIADJTYCD = FIADJTY.FIADJTYCD

WHERE CLAIM.ACCOUNTNR=[Account Number]
AND CLAIM.PROMO=[Process Month]

GROUP BY CLAIM.OUTLETNR, FVADJTY.FVADJTYDE, FIADJTY.FIADJTYDE;
Terry
------------------------------------
People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker