Problem with a Parameter Query

[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

 

[John Vinson]

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]));

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?

 

[Mike Webb]

Thanks. I'll give it a try.

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]![cboDescrip

t

ion]));

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?