Need help with OLEDB query expression

[Terry Olsen]

I need to get information from 3 tables in an MDB file.

I need all the columns in the first table.
I need 2 columns in the 2nd table where it's primary key matches a
column in the first table.
I need several columns in the 3rd table where it's primary key matches a
column in the 2nd table.

I tried this:

SELECT calPackage.*,ca lShipment.m_shi pDateTime,
calShipment.m_f oreignKey00,cal PkgAgent.m_resi dential,
calPkgAgent.m_S treetAddress,ca lPkgAgent.m_Cit y,
calPkgAgent.m_S tateProv,calPkg Agent.m_Country ,
calPkgAgent.m_P ostalCode,calPk gAgent.m_RoomFl oor,
calPkgAgent.m_D epartment,calPk gAgent.m_Attent ion,
calPkgAgent.Sm_ businessName
FROM calPackage
INNER JOIN calShipment
ON calShipment.m_p rimaryKey=calPa ckage.m_foreign Key
INNER JOIN calPkgAgent
ON calPkgAgent.m_p rimaryKey=calSh ipment.m_foreig nKey00

but when I tried to execute the command, I get:

Syntax error (missing operator) in query expression
'calShipment.m_ primaryKey=calP ackage.m_foreig nKey INNER JOIN calPkgAgent
ON calPkgAgent.m_p rimaryKey=calSh ipment.m_foreig nKey00'.

I'm looking for any help I can get. Thanks!

*** Sent via Developersdex http://www.developersdex.com ***

[Saber]

I think you'll get your answer by posting your question here:
microsoft.publi c.access.querie s

"Terry Olsen" <to******@hotma il.com> wrote in message
news:uk******** ******@tk2msftn gp13.phx.gbl...

I need to get information from 3 tables in an MDB file.

I need all the columns in the first table.
I need 2 columns in the 2nd table where it's primary key matches a
column in the first table.
I need several columns in the 3rd table where it's primary key matches a
column in the 2nd table.

I tried this:

SELECT calPackage.*,ca lShipment.m_shi pDateTime,
calShipment.m_f oreignKey00,cal PkgAgent.m_resi dential,
calPkgAgent.m_S treetAddress,ca lPkgAgent.m_Cit y,
calPkgAgent.m_S tateProv,calPkg Agent.m_Country ,
calPkgAgent.m_P ostalCode,calPk gAgent.m_RoomFl oor,
calPkgAgent.m_D epartment,calPk gAgent.m_Attent ion,
calPkgAgent.Sm_ businessName
FROM calPackage
INNER JOIN calShipment
ON calShipment.m_p rimaryKey=calPa ckage.m_foreign Key
INNER JOIN calPkgAgent
ON calPkgAgent.m_p rimaryKey=calSh ipment.m_foreig nKey00

but when I tried to execute the command, I get:

Syntax error (missing operator) in query expression
'calShipment.m_ primaryKey=calP ackage.m_foreig nKey INNER JOIN calPkgAgent
ON calPkgAgent.m_p rimaryKey=calSh ipment.m_foreig nKey00'.

I'm looking for any help I can get. Thanks!

*** Sent via Developersdex http://www.developersdex.com ***

[George Shubin]

You need to put the first join within a set of parenthesis:

SELECT calPackage.*,ca lShipment.m_shi pDateTime,
calShipment.m_f oreignKey00,cal PkgAgent.m_resi dential,
calPkgAgent.m_S treetAddress,ca lPkgAgent.m_Cit y,
calPkgAgent.m_S tateProv,calPkg Agent.m_Country ,
calPkgAgent.m_P ostalCode,calPk gAgent.m_RoomFl oor,
calPkgAgent.m_D epartment,calPk gAgent.m_Attent ion,
calPkgAgent.Sm_ businessName
FROM
(calPackage
INNER JOIN calShipment
ON calShipment.m_p rimaryKey=calPa ckage.m_foreign Key)

INNER JOIN calPkgAgent
ON calPkgAgent.m_p rimaryKey=calSh ipment.m_foreig nKey00

--
------------------------------------------------------------------------
George Shubin Custom Software Development
dX Software Systems Database Applications
Ph: 503-981-6806 Fax: [phone]
www.dxonline.com ge****@dxonline .com
------------------------------------------------------------------------

"The sum which two married people owe to one another defies calculation. It
is an infinite debt, which can only be discharged through all eternity." --
Goethe

"Terry Olsen" <to******@hotma il.com> wrote in message
news:uk******** ******@tk2msftn gp13.phx.gbl...

I need to get information from 3 tables in an MDB file.

I need all the columns in the first table.
I need 2 columns in the 2nd table where it's primary key matches a
column in the first table.
I need several columns in the 3rd table where it's primary key matches a
column in the 2nd table.

I tried this:

SELECT calPackage.*,ca lShipment.m_shi pDateTime,
calShipment.m_f oreignKey00,cal PkgAgent.m_resi dential,
calPkgAgent.m_S treetAddress,ca lPkgAgent.m_Cit y,
calPkgAgent.m_S tateProv,calPkg Agent.m_Country ,
calPkgAgent.m_P ostalCode,calPk gAgent.m_RoomFl oor,
calPkgAgent.m_D epartment,calPk gAgent.m_Attent ion,
calPkgAgent.Sm_ businessName
FROM calPackage
INNER JOIN calShipment
ON calShipment.m_p rimaryKey=calPa ckage.m_foreign Key
INNER JOIN calPkgAgent
ON calPkgAgent.m_p rimaryKey=calSh ipment.m_foreig nKey00

but when I tried to execute the command, I get:

Syntax error (missing operator) in query expression
'calShipment.m_ primaryKey=calP ackage.m_foreig nKey INNER JOIN calPkgAgent
ON calPkgAgent.m_p rimaryKey=calSh ipment.m_foreig nKey00'.

I'm looking for any help I can get. Thanks!

*** Sent via Developersdex http://www.developersdex.com ***