If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Help with a Query and Table
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 |
#3
|
|||
|
|||
Help with a Query and Table
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 . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
how can i make query forever read whatever table is currently in u | access_confused | Running & Setting Up Queries | 1 | August 2nd, 2004 09:49 PM |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |
Merge a table and query into new table? | Shyguy | Running & Setting Up Queries | 5 | June 28th, 2004 02:30 AM |
Update another table with a Max record query | Ngan | Running & Setting Up Queries | 2 | June 22nd, 2004 05:01 PM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |