Re: Loads of errors when querying MS SQL Server Express 2005 from Access 2007
- From: Bob Quintal <rquintal@xxxxxxxxxxxxx>
- Date: Fri, 30 May 2008 16:45:57 -0400
uberyes@xxxxxxxxx wrote in
news:b820ad4c-f353-4e4d-b7bf-
cb749cd4e96e@xxxxxxxxxxxxxxxxxxxxxxxxxxx
m:
Hi,First, whenever I link to odbc tables i rename them to remove the
I wonder if you could help - I have an SQL server database with
four tables...
I have a 'tblContact':
ContactID,FirstName,LastName
101,Peter,Smith
102,John,Crow
103,Ed,Bye
104,Stuart,Jones
I have a ?tblClient?:
ClientID,CompanyName
201,XYZCom
202,ABCCom
I have a ?bltClientContactLinkTable?:
ContactID,ClientID, PositionID
101,201,301
102,201, 301
103,202, 302
104,202,302
And fiinally a tblPosition:
PositionID,Description
301,Manager
302,Director
The query below (when run through a Query Analyser connected
diretly to the SQL server) displays the first and last name of all
contacts along with the company they work for and the position the
hold. If they don't have a position it returns 'no position'.
SELECT
tblClient.CompanyName,
tblContact.FirstName,
tblContact.LastName,
coalesce(tblPosition.Description, 'no position') as Description
FROM
tblContactClientLinkTable
INNER JOIN tblContact ON (tblContact.ContactID
tblContactClientLinkTable.ContactID) INNER JOIN tblClient ON
(tblClient.ClientID tblContactClientLinkTable.ClientID) LEFT JOIN
tblPosition ON (tblContactClientLinkTable.PositionID
tblPosition.PositionID) GROUP BY
tblClient.CompanyName,
tblContact.FirstName,
tblContact.LastName,
tblPosition.Description
ORDER BY
tblClient.CompanyName
However, If I connect to the server from Access so I can actually
edit the data easily I get problems
Once I'd added all the tables I need (through ODBC linked table),
prefixed each table name with dbo_ (seeings as that's what they're
called when I connect to them and when I look at automatically
generated SQL when I use the
designer instead) and created the SQL query, an error comes up:
Syntax error (missing operator) in query expression
'(dbo_tblContact.ContactID =
dbo_tblContactClientLinkTable.ContactID) INNER JOIN dbo_tblClient
ON (dbo_tblClient.ClientID dbo_tblContactClientLinkTable.ClientID)
INNER JOIN dbo_tblPosition ON
(dbo_tblContactClientLinkTable.PositionID = dbo_tblPosit'.
Any suggestions?
Many thanks,
dbo_.
The problem is that access syntax is slightly different from SQL
server. You have added the comparison operator that Access expects
in two of the three joins, but
INNER JOIN dbo_tblClient ON (dbo_tblClient.ClientID
dbo_tblContactClientLinkTable.ClientID)
seems to be missing an = sign between the two field names.
--
Bob Quintal
PA is y I've altered my email address.
** Posted from http://www.teranews.com **
.
- References:
- Prev by Date: Re: Table setting to disallow new rec's?
- Next by Date: Re: Reset subtotal within a query
- Previous by thread: Re: Loads of errors when querying MS SQL Server Express 2005 from Access 2007
- Next by thread: Re: Loads of errors when querying MS SQL Server Express 2005 from Access 2007
- Index(es):
Relevant Pages
|
