Problem with a Parameter Query

  • Thread starter Thread starter Mike Webb
  • Start date Start date
M

Mike Webb

First off, this is my first attempt at a parameter query. I am using A2K2
on WinXP.

I am trying to modify a sample DB I found called ParaQuerySelect2K.mdb to
use values from 2 tables joined by a 3rd. However, I get this error:
"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 join and then include that query in your SQL
statement."

The SQL is:
SELECT tblAddressList.TitlePrefix, tblAddressList.LastName,
tblAddressList.FirstName, tblAddressList.MiddleInitialName,
tblAddressList.TitleSuffix, tblAddressList.Address, tblAddressList.City,
tblAddressList.ZipCode, tblAddressListCategories.Description
FROM tblAddressList LEFT JOIN (tblAddressListCategories LEFT JOIN
tblJoinContactsAndCategories ON tblAddressListCategories.SubCategory_ID =
tblJoinContactsAndCategories.SubCategory_ID) ON tblAddressList.ID =
tblJoinContactsAndCategories.ContactAddress_ID
WHERE (((tblAddressList.LastName)=[forms]![frmSelection]![cboLastName])) OR
(((tblAddressListCategories.Description)=[forms]![frmSelection]![cboDescript
ion]));

tblAddressList contains all info on a contact.
tblAddressListCategories contains the categories each contact is assigned
to, such as "donor", "volunteer", "USFWS", etc.
tblJoinContactsAndCategories has the PK's from the first 2 tables in it as
FK's. Each of the first two tables is joined to the last by a one2many
relationship.

The purpose of frmSelection is to allow the user to select a last name or a
category description and then get a report.

I created the query and form using the Design view with the sample DB open
side-by-side so I could emulate it insofar as possible. The SQL above I got
by a right-click in the query design view.

What did I do wrong?

TIA,
Mike
 
First off, this is my first attempt at a parameter query. I am using A2K2
on WinXP.

I am trying to modify a sample DB I found called ParaQuerySelect2K.mdb to
use values from 2 tables joined by a 3rd. However, I get this error:
"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 join and then include that query in your SQL
statement."

The SQL is:
SELECT tblAddressList.TitlePrefix, tblAddressList.LastName,
tblAddressList.FirstName, tblAddressList.MiddleInitialName,
tblAddressList.TitleSuffix, tblAddressList.Address, tblAddressList.City,
tblAddressList.ZipCode, tblAddressListCategories.Description
FROM tblAddressList LEFT JOIN (tblAddressListCategories LEFT JOIN
tblJoinContactsAndCategories ON tblAddressListCategories.SubCategory_ID =
tblJoinContactsAndCategories.SubCategory_ID) ON tblAddressList.ID =
tblJoinContactsAndCategories.ContactAddress_ID
WHERE (((tblAddressList.LastName)=[forms]![frmSelection]![cboLastName])) OR
(((tblAddressListCategories.Description)=[forms]![frmSelection]![cboDescript
ion]));
Click to expand...

The error arises because you have a Left Join embedded in a Left Join.
Since there might not be any record in tblAddressListCategories, it's
ambiguous how you would link tblJoinContactsAndCategories.

The getaround is to first create a query joining
tblAddressListCategories to tblJoinContactsAndCategories by a left
join; save this query; and left join *that query* to tblAddressList.

It appears that you're not actually using tblJoinContactsAndCategories
- could you perhaps simply drop this table from the query?
 
Thanks. I'll give it a try.

Mike
John Vinson said:
First off, this is my first attempt at a parameter query. I am using A2K2
on WinXP.

I am trying to modify a sample DB I found called ParaQuerySelect2K.mdb to
use values from 2 tables joined by a 3rd. However, I get this error:
"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 join and then include that query in your SQL
statement."

The SQL is:
SELECT tblAddressList.TitlePrefix, tblAddressList.LastName,
tblAddressList.FirstName, tblAddressList.MiddleInitialName,
tblAddressList.TitleSuffix, tblAddressList.Address, tblAddressList.City,
tblAddressList.ZipCode, tblAddressListCategories.Description
FROM tblAddressList LEFT JOIN (tblAddressListCategories LEFT JOIN
tblJoinContactsAndCategories ON tblAddressListCategories.SubCategory_ID =
tblJoinContactsAndCategories.SubCategory_ID) ON tblAddressList.ID =
tblJoinContactsAndCategories.ContactAddress_ID
WHERE (((tblAddressList.LastName)=[forms]![frmSelection]![cboLastName]))
Click to expand...
OR
(((tblAddressListCategories.Description)=[forms]![frmSelection]![cboDescrip
Click to expand...
t
ion]));
Click to expand...

The error arises because you have a Left Join embedded in a Left Join.
Since there might not be any record in tblAddressListCategories, it's
ambiguous how you would link tblJoinContactsAndCategories.

The getaround is to first create a query joining
tblAddressListCategories to tblJoinContactsAndCategories by a left
join; save this query; and left join *that query* to tblAddressList.

It appears that you're not actually using tblJoinContactsAndCategories
- could you perhaps simply drop this table from the query?
Click to expand...
 
Back
Top