Hi,
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 =91tblClient=92:
ClientID,CompanyName
201,XYZCom
202,ABCCom
I have a =91bltClientContactLinkTable=92:
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 =3D
tblContactClientLinkTable.ContactID)
INNER JOIN tblClient ON (tblClient.ClientID =3D
tblContactClientLinkTable.ClientID)
LEFT JOIN tblPosition ON (tblContactClientLinkTable.PositionID =3D
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 =3D dbo_tblContactClientLinkTable.ContactID)
INNER JOIN dbo_tblClient ON (dbo_tblClient.ClientID =3D
dbo_tblContactClientLinkTable.ClientID)
INNER JOIN dbo_tblPosition ON
(dbo_tblContactClientLinkTable.PositionID =3D dbo_tblPosit'.
Any suggestions?
Many thanks,
|
