How to Fix Error SQL Statement - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

How to Fix Error SQL Statement

John Muir

4 years ago

My query was working fine. I added some fields to the backend. Opened he query on the front end - message said too many fields. I eliminated the fields recently added. Also eliminated on the BE. Relinked the tables. Closed the query. When opening again I got this message:

The SQL statement could not be executed because it contains ambiguous outer joins. to force one of the joins to be performed first, create a separate query that performs the first drawing and then include that query in your SQL statement

However, I didn't add any outer joins and don't see any...could you help?

Thanks,

John

	Alex Hedley @Reply 4 years ago
Revert to the backup before you made any changes?

	John Muir OP @Reply 4 years ago
Thanks Alex. Unfortunately, I forgot to back up. Any other ideas?

	Alex Hedley @Reply 4 years ago
Try rewriting the query from scratch

	Alex Hedley @Reply 4 years ago
Could the SQL in the underlying query have been modified Have you checked what it looks like?

	John Muir OP @Reply 4 years ago
Possibly. Where do i look and what do i look for? The query is huge...i know bad design. Thanks

	Kevin Robertson @Reply 4 years ago

John Muir OP @Reply

4 years ago

here is the "join" info: Does this help?

FROM tContacts AS tContacts_7, ((((((((((((tNewCases LEFT JOIN tContacts ON tNewCases.PlaintiffAttyId = tContacts.[Client ID]) LEFT JOIN tContacts AS tContacts_1 ON tNewCases.DefenseAttyId = tContacts_1.[Client ID]) LEFT JOIN tContacts AS tContacts_2 ON tNewCases.ClientId = tContacts_2.[Client ID]) LEFT JOIN tContacts AS tContacts_3 ON tNewCases.Referredbyid = tContacts_3.[Client ID]) LEFT JOIN tContacts AS tContacts_4 ON tNewCases.JudgeId = tContacts_4.[Client ID]) LEFT JOIN tContacts AS tContacts_5 ON tNewCases.CoBrokerId = tContacts_5.[Client ID]) LEFT JOIN [ASSIGNOR-UNDERWRITING COMPANY LIST] ON tNewCases.AssignorID = [ASSIGNOR-UNDERWRITING COMPANY LIST].assignorID) LEFT JOIN LifeCoT ON tNewCases.LifeCoID = LifeCoT.LifeCoID) LEFT JOIN ProfAdminT ON tNewCases.ProfAdminID = ProfAdminT.ProfAdminID) LEFT JOIN LifeCoAssigmentCo ON tNewCases.LifeCoAssigmentCoID = LifeCoAssigmentCo.LifeCoAssigmentCoID) LEFT JOIN tContacts AS DefenseBroker2 ON tNew

	John Muir OP @Reply 4 years ago
I found it! Thanks, your tip sent me in the right direction. the culprit was "FROM tContacts AS tContacts_7, ". i deleted it fronm the SQL stmt and all is working.

	Richard Rost @Reply 4 years ago
Always ALWAYS make backups!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View