Joins

[AccessJenny]

I'm trying to get this to work right but for some reason can't get the bracketing right. Anyone have any ideas on why I'm getting the following error when I try to execute this SQL Statemen

Error Ms

Join expression not supported

SQL Code
SELECT TR.ContactID, TR.TaskID, TR.task_completed, C.ContactID,
T.TaskID, T.task_name, T.task_owner, Ow.task_Ownernam
FROM ((Tasks T LEFT JOIN Transactions TR ON T.TaskID = TR.TaskID)
INNER JOIN Contacts C ON C.ContactID = TR.ContactID)
INNER JOIN Owner Ow ON T.task_owner = Ow.task_ownerI
WHERE C.ContactID = 8

Any insight would be greatly appreciated

 

[Dirk Goldgar]

I'm trying to get this to work right but for some reason can't get
the bracketing right. Anyone have any ideas on why I'm getting the
following error when I try to execute this SQL Statement

Error Msg

Join expression not supported.

SQL Code:
SELECT TR.ContactID, TR.TaskID, TR.task_completed, C.ContactID,
T.TaskID, T.task_name, T.task_owner, Ow.task_Ownername
FROM ((Tasks T LEFT JOIN Transactions TR ON T.TaskID = TR.TaskID)
INNER JOIN Contacts C ON C.ContactID = TR.ContactID)
INNER JOIN Owner Ow ON T.task_owner = Ow.task_ownerID
WHERE C.ContactID = 81

Any insight would be greatly appreciated

I'm not sure offhand, but I notice that you're left-joining Tasks to
Transactions, but then inner-joining the result set to Contacts on a
field from Transactions. Since C.ContactID will never find a match on
TR.ContactID unless Tasks finds a match on Transactions, it seems to me
that you may as well make that LEFT JOIN be an INNER JOIN. See if that
gets rid of the problem.

 

[AccessJenny]

Dirk

Thanks for the suggestion but I can't change it to an inner join. I currently have a task list (Task Table) containing approx 15 tasks. Each task has an owner (Owner Table). The entire task lisk has to be completed for each contact (Contact Table). Once a task is completed, The Contact ID and the Task ID are inserted into the Transactions Table

For Display purposes then, I want to have all of the tasks listed with checkboxes and then only those that have been completed would be checked(TR.task_completed (Yes/No)). Hence the need for the Left Join

Any ideas????

 

[John Spencer (MVP)]

Since Owner is a reserved word, I might try surrounding it with brackets to make
sure that Access/Jet understands that you are referring to a table and not an
Owner of a table. Other that that I don't see any reason that this would fail.
See my change in Line 5 of the SQL

SELECT TR.ContactID, TR.TaskID, TR.task_completed, C.ContactID,
T.TaskID, T.task_name, T.task_owner, Ow.task_Ownername
FROM ((Tasks T LEFT JOIN Transactions TR ON T.TaskID = TR.TaskID)
INNER JOIN Contacts C ON C.ContactID = TR.ContactID)
INNER JOIN [Owner] Ow ON T.task_owner = Ow.task_ownerID
WHERE C.ContactID = 81

If that fails try temporarily removing one table from the query and see if that
works. Keep trying combinations until you isolate the culprit.