Help with a Query and Table

Hello,

I am fairly new to Access, but have worked w/ databases
for a long time.

I using Access to access the our SQL server tables from MS
Great Plains using ODBC links. I am trying to get a
commissions report based on multiple criteria and have
tried everything, but keep getting a variety of errors.
It works just fine, except this month I needed to add a
criteria based on customer class which made me add a new
table link. I added the table to the query and it says
that "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 join in your
SQL statement." I created a query using just this new
table and it works fine. Then I tried to add the query to
my query or SQL statement to the query and I don't know
what I'm doing. I couldn't get the expression builder to
create a new field using the new query it asks me
to "enter parameter value". Then I tried to copy the SQL
statement into the query and it says "check the subquery's
syntax and enclose the subquery in parenthesis". I add
parenthesis and I get this "You may have entered an
operand without an operator" Now what should I try?

Here is the SQL statement from the query w/ the new table
for customer class.

SELECT [dbo_RM00101].[CUSTNMBR], [dbo_RM00101].[CUSTNAME],
[dbo_RM00101].[CUSTCLAS]
FROM dbo_RM00101, dbo_SOP10101 INNER JOIN (dbo_RM00102
INNER JOIN dbo_SOP30200 ON [dbo_RM00102].[CUSTNMBR]=
[dbo_SOP30200].[CUSTNMBR]) ON [dbo_SOP10101].[SOPNUMBE]=
[dbo_SOP30200].[SOPNUMBE]
WHERE ((([dbo_RM00101].[CUSTCLAS])="VISION SOURCE" Or
([dbo_RM00101].[CUSTCLAS])="RETAIL"));

If I use the above SQL statement in the query where should
I add it (Field or Criteria".

Thank you in advance.

Lisa

Dear Lisa:

The Access Jet database engine is touchy about this.

It seems you may be familiar with queries to other database engines.
Perhaps you are already comfortable with SQL Server syntax. If so,
write your query in Query Analyzer and get it working on your SQL
Server database. Then use that query in Access as a pass-through
query. You will then not be bothered by the Jet syntax requirements,
which can be quite non-standard.

Does that help?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 3 Aug 2004 14:06:26 -0700, "Lisa" wrote:

Hello,

I am fairly new to Access, but have worked w/ databases
for a long time.

I using Access to access the our SQL server tables from MS
Great Plains using ODBC links. I am trying to get a
commissions report based on multiple criteria and have
tried everything, but keep getting a variety of errors.
It works just fine, except this month I needed to add a
criteria based on customer class which made me add a new
table link. I added the table to the query and it says
that "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 join in your
SQL statement." I created a query using just this new
table and it works fine. Then I tried to add the query to
my query or SQL statement to the query and I don't know
what I'm doing. I couldn't get the expression builder to
create a new field using the new query it asks me
to "enter parameter value". Then I tried to copy the SQL
statement into the query and it says "check the subquery's
syntax and enclose the subquery in parenthesis". I add
parenthesis and I get this "You may have entered an
operand without an operator" Now what should I try?

Here is the SQL statement from the query w/ the new table
for customer class.

SELECT [dbo_RM00101].[CUSTNMBR], [dbo_RM00101].[CUSTNAME],
[dbo_RM00101].[CUSTCLAS]
FROM dbo_RM00101, dbo_SOP10101 INNER JOIN (dbo_RM00102
INNER JOIN dbo_SOP30200 ON [dbo_RM00102].[CUSTNMBR]=
[dbo_SOP30200].[CUSTNMBR]) ON [dbo_SOP10101].[SOPNUMBE]=
[dbo_SOP30200].[SOPNUMBE]
WHERE ((([dbo_RM00101].[CUSTCLAS])="VISION SOURCE" Or
([dbo_RM00101].[CUSTCLAS])="RETAIL"));

If I use the above SQL statement in the query where should
I add it (Field or Criteria".

Thank you in advance.

Lisa

I am not very familiar with SQL Server syntax (I've used
FileMaker and some custom databases in the past). If I
use Query Analyzer do I just copy the SQL statement into
Access?

I created a link between a different table in my query and
I don't get the previous errors now, but I am not getting
the correct results to my query now. It is looking up the
customer class that I was trying to add to my existing
query, but the date lookup is now not working. I am sure
that it has something to do with the table that I added
that caused the "ambiguous outer joins" error previously.

Lisa



-----Original Message-----
Dear Lisa:

The Access Jet database engine is touchy about this.

It seems you may be familiar with queries to other
database engines.
Perhaps you are already comfortable with SQL Server
syntax. If so,
write your query in Query Analyzer and get it working on
your SQL
Server database. Then use that query in Access as a pass-
through
query. You will then not be bothered by the Jet syntax
requirements,
which can be quite non-standard.

Does that help?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 3 Aug 2004 14:06:26 -0700, "Lisa"
wrote:

Hello,

I am fairly new to Access, but have worked w/ databases
for a long time.

I using Access to access the our SQL server tables from
MS
Great Plains using ODBC links. I am trying to get a
commissions report based on multiple criteria and have
tried everything, but keep getting a variety of errors.
It works just fine, except this month I needed to add a
criteria based on customer class which made me add a new
table link. I added the table to the query and it says
that "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 join in
your
SQL statement." I created a query using just this new
table and it works fine. Then I tried to add the query
to
my query or SQL statement to the query and I don't know
what I'm doing. I couldn't get the expression builder
to
create a new field using the new query it asks me
to "enter parameter value". Then I tried to copy the
SQL
statement into the query and it says "check the
subquery's
syntax and enclose the subquery in parenthesis". I add
parenthesis and I get this "You may have entered an
operand without an operator" Now what should I try?

Here is the SQL statement from the query w/ the new
table
for customer class.

SELECT [dbo_RM00101].[CUSTNMBR], [dbo_RM00101].
[CUSTNAME],
[dbo_RM00101].[CUSTCLAS]
FROM dbo_RM00101, dbo_SOP10101 INNER JOIN (dbo_RM00102
INNER JOIN dbo_SOP30200 ON [dbo_RM00102].[CUSTNMBR]=
[dbo_SOP30200].[CUSTNMBR]) ON [dbo_SOP10101].[SOPNUMBE]=
[dbo_SOP30200].[SOPNUMBE]
WHERE ((([dbo_RM00101].[CUSTCLAS])="VISION SOURCE" Or
([dbo_RM00101].[CUSTCLAS])="RETAIL"));

If I use the above SQL statement in the query where
should
I add it (Field or Criteria".

Thank you in advance.

Lisa


.