Joins

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
AccessJenny said:
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
Click to expand...

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.
 
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????
 
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.
 
Back
Top