Unmatched Records Over Time in a Single Table

[EBKB]

I went into the forums and found this discussion of making nested queries using SQL
https://www.accessforums.net/access/...ble-13535.html
and believe I have followed the instructions. However, I'm receiving an error message that the ODBC call failed. I assume I'm doing something wrong that is related to the use of linked tables in another data-source, but am not sure where to go from here. Any help is appreciated. Here's the details of the query-

I'm linked through an ODBC link to a set of tables stored in a church database system called Shelby. I regularly use these linked tables to report out information in a more useful format than the church package allows. I'm not trying to find the list of the people who pledged to the church in a user-input prior year, and have not yet pledged for a user-input current year. I'm able to make the individual queries work, generating a list of those who have pledged in a given year, but now I want to run an unmatched records query on the two results. I copied and pasted the SQL code generated by query design, and added the elements listed in the discussion mentioned above. When I try to run the code, I am able to answer the user-input questions, and move on to the next user-input, but then am told the ODBC call failed. Here's the SQL code I'm using.

SELECT LateYear.Shelby_NANames.FamNu, LateYear.Shelby_NANames.FirstMiddle, LateYear.Shelby_NANames.LastName
FROM


(SELECT Shelby_NANames.FamNu, Shelby_NANames.FirstMiddle, Shelby_NANames.LastName, Left([Shelby_CNPur]![Descr],4) AS PledgeYear, Right([Shelby_CNPur]![Purpose],4) AS OperatingContributions
FROM Shelby_CNPur INNER JOIN (Shelby_CNPlg INNER JOIN Shelby_NANames ON Shelby_CNPlg.NameCounter = Shelby_NANames.NameCounter) ON Shelby_CNPur.Counter = Shelby_CNPlg.PurCounter
WHERE (((Left([Shelby_CNPur]![Descr],4))=[For which pledge cycle do you want a list of prior pledgers who have not yet pledged?]) AND ((Right([Shelby_CNPur]![Purpose],4))="OPER")))
As LateYear




LEFT JOIN


(SELECT Shelby_NANames.FamNu, Shelby_NANames.FirstMiddle, Shelby_NANames.LastName, Left([Shelby_CNPur]![Descr],4) AS PledgeYear, Right([Shelby_CNPur]![Purpose],4) AS OperatingContributions
FROM Shelby_CNPur INNER JOIN (Shelby_CNPlg INNER JOIN Shelby_NANames ON Shelby_CNPlg.NameCounter = Shelby_NANames.NameCounter) ON Shelby_CNPur.Counter = Shelby_CNPlg.PurCounter
WHERE (((Left([Shelby_CNPur]![Descr],4))=[For which prior pledge cycle do you want a list of households that pledged who have not yet pledged?]) AND ((Right([Shelby_CNPur]![Purpose],4))="OPER")))
As EarlyYear




ON (EarlyYear.Shelby_NANames.FamNu =LateYear.Shelby_NANames.FamNu)
WHERE ((LateYear.Shelby_NANames.FamNu) IS NULL);

[rpeare]

So your two sub queries work fine but when you try to link them you're having a problem?

in other words this:

[code](SELECT Shelby_NANames.FamNu, Shelby_NANames.FirstMiddle, Shelby_NANames.LastName, Left([Shelby_CNPur]![Descr],4) AS PledgeYear, Right([Shelby_CNPur]![Purpose],4) AS OperatingContributions
FROM Shelby_CNPur INNER JOIN (Shelby_CNPlg INNER JOIN Shelby_NANames ON Shelby_CNPlg.NameCounter = Shelby_NANames.NameCounter) ON Shelby_CNPur.Counter = Shelby_CNPlg.PurCounter
WHERE (((Left([Shelby_CNPur]![Descr],4))=[For which pledge cycle do you want a list of prior pledgers who have not yet pledged?]) AND ((Right([Shelby_CNPur]![Purpose],4))="OPER")))
As LateYear[code]

and this:

(SELECT Shelby_NANames.FamNu, Shelby_NANames.FirstMiddle,  Shelby_NANames.LastName, Left([Shelby_CNPur]![Descr],4) AS PledgeYear,  Right([Shelby_CNPur]![Purpose],4) AS OperatingContributions
FROM Shelby_CNPur INNER JOIN (Shelby_CNPlg INNER JOIN Shelby_NANames ON  Shelby_CNPlg.NameCounter = Shelby_NANames.NameCounter) ON  Shelby_CNPur.Counter = Shelby_CNPlg.PurCounter
WHERE (((Left([Shelby_CNPur]![Descr],4))=[For which prior pledge cycle  do you want a list of households that pledged who have not yet  pledged?]) AND ((Right([Shelby_CNPur]![Purpose],4))="OPER")))
As EarlyYear

will produce results with no problem?

If they do save them as two distinct queries then create a third query that links your two sub queries to get the result you want. Once you've got that query working the way you want then you can substitute back in the SQL statements for the query names if you really want to.

[EBKB]

Well, actually, that's how I originally created it. And I got the same ODBC call failed error. So I went digging, thinking that perhaps the problem was that they needed to be in the same query, but no dice. Sorry I didn't mention that originally.

[rpeare]

the ODBC call means that it can't see one of the tables, if these are linked from an outside source, can you open all the tables involved in the query/queries? I have only ever seen the ODBC error fail when it can't read a table. Double check your ODBC connections AND see if you can open all the tables in your access db. If you can do both, compact/repair your database and try it again or re-link all of your tables.

[EBKB]

Well, I'm not sure what's going on, but compacting it didn't fix the problem either. Creating a form with a button and converting the two original queries to MakeTable queries does, though I don't think it's an ideal solution, because it's creating table bloat. It definitely reads the ODBC linked table, because I can run all the other queries, so it must have something to do with the temporary storage of data in the sub-queries is all I can figure. Thanks for helping me try.

[rpeare]

Did you try opening all the tables that the queries are referencing to double check?

If you create two queries do those two queries run ok, it's only when you link them that the problem occurs?

How large are the tables we're talking about and is the ODBC error you're getting a timeout error? If it's a timeout error you can change how long access will run before it determines the process isn't working.

[EBKB]

Yep. all the tables open fine. And the form built button to use them as make table queries runs fine, too. It asks which years I want returned, I enter it and it generates the appropriate tables. it's only when I link the two that things break down. The underlying tables being accessed are moderately large. One is 11K records, another has 14K records, the other 346. I wish I knew if it was a timeout error, but the information it returns isn't specific. It just says "ODBC Call Failed, is this information helpful." Well, no, it's not helpful, but I can't tell if it's a timeout error. When I run the queries as make tables, it only takes a fraction of a second to run, but I don't know what the standard timeout setting is.

[rpeare]

Hrm..

With that small a number of records the timeout shouldn't be an issue, particularly if you're not doing a lot of heavy calculations.

Here are some other things to try

When you run your query run it with all the fields from one table and none from the other and see what happens.

If there are any calculated fields in your 'final' query take them out and see if it will run.


What I'd really like to know is this: If you import the tables rather than link them, does your query work?

What is the source for the external tables, is it a text file, a SQL table? a MS access back end?