×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

MS Access ODBC error

MS Access ODBC error

MS Access ODBC error

(OP)
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;

RE: MS Access ODBC error


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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close