0

I have two queries, which use tables linked from an ODBC database, both are simple enough and indiviudally work fine.

Query 1:

SELECT ExtraField.FieldName
FROM (ExtraField INNER JOIN ExtraFieldScope ON ExtraField.ExtraFieldID = ExtraFieldScope.ExtraFieldID) INNER JOIN ExtraGroup ON ExtraFieldScope.ExtraGroupID = ExtraGroup.ExtraGroupID
WHERE (((ExtraGroup.GroupDescription)="Client"))
ORDER BY ExtraField.FieldName;

Query 2:

SELECT ExtraField.FieldName
FROM (ExtraField INNER JOIN ExtraFieldScope ON ExtraField.ExtraFieldID = ExtraFieldScope.ExtraFieldID) INNER JOIN ExtraGroup ON ExtraFieldScope.ExtraGroupID = ExtraGroup.ExtraGroupID
WHERE (((ExtraGroup.GroupDescription)="Contact"))
ORDER BY ExtraField.FieldName;

I then tried to join the two queries with a left join, a la:

SELECT [Extra Fields - Client (Part 1)].FieldName AS [Client Field Name]
FROM [Extra Fields - Client (Part 1)] LEFT JOIN [Extra Fields - Client (Part 2)] ON [Extra Fields - Client (Part 1)].FieldName = [Extra Fields - Client (Part 2)].FieldName;

but that query throws up an "ODBC - Call Failed" error. However, this is only when I use an outer (LEFT) join. The following query works fine (but isn't what I want):

SELECT [Extra Fields - Client (Part 1)].FieldName AS [Client Field Name]
FROM [Extra Fields - Client (Part 1)] INNER JOIN [Extra Fields - Client (Part 2)] ON [Extra Fields - Client (Part 1)].FieldName = [Extra Fields - Client (Part 2)].FieldName;

What gives?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Sinister Beard
  • 3,570
  • 12
  • 59
  • 95
  • 1
    Run the query in VBA, trap the error with `On Error ...`, and then loop through the `DAO.Errors` collection to get more information on the underlying problem. (See my recent answer [here](http://stackoverflow.com/a/22105935/2144390) for an example of the error-trapping code.) What, if anything, do the additional error messages say? – Gord Thompson Mar 05 '14 at 09:13
  • I'm afraid that just throws up the same error three times? – Sinister Beard Mar 05 '14 at 09:47
  • 2
    I was able to recreate your issue. The error message I receive is quite lengthy and says things like 'The multi-part identifier "MS1.GroupDescription" could not be bound. (#4104)'. Since the underlying tables are ODBC linked tables can you create views on the server and then create an Access linked table to retrieve the results of the final view? – Gord Thompson Mar 05 '14 at 10:07
  • Thanks - yes, I think I'm going to have to look at this another way, seems to be a bug in Access from other Googling. – Sinister Beard Mar 05 '14 at 10:09
  • re: "bug in Access" - I'm not so sure about that. Another question [here](http://stackoverflow.com/q/14656582/2144390) suggests that the "multi-part identifier" error can occur in SQL Server itself, although Access' additional "meddling" in the process certainly could contribute to the problem. – Gord Thompson Mar 05 '14 at 10:18
  • possible duplicate of [Why is LEFT JOIN causing a "multi-part identifier can't be bound" error when INNER JOIN does not?](http://stackoverflow.com/questions/21550925/why-is-left-join-causing-a-multi-part-identifier-cant-be-bound-error-when-inn) – Gord Thompson Mar 05 '14 at 10:22

0 Answers0