Multi-condition JOIN

L

larrynator

I have the following TSQL query and am trying to convert it to MS
Access. It doesn't seem to like the fact that I have multiple
conditions in my join statement. I've experimented with the syntax
quite a bit but can't make Access happy. Any suggestions?

select
RPT_LITI_MTTER_FCT.CO_NM,
ManagingAtty.FULL_NM AS 'MNGNG_ATTY_NM',
LegalAsst.FULL_NM AS 'LEGAL_ASST_NM'
FROM
RPT_LITI_MTTER_FCT
LEFT OUTER JOIN RPT_MTTER_ASGNE_FCT AS ManagingAtty ON
(ManagingAtty.MTTER_NUMB_TXT = RPT_LITI_MTTER_FCT.MTTER_NUMB_TXT AND
ManagingAtty.ASGNE_TYPE_TXT = 'Managing Atty')
LEFT OUTER JOIN RPT_MTTER_ASGNE_FCT AS LegalAsst ON
(LegalAsst.MTTER_NUMB_TXT = RPT_LITI_MTTER_FCT.MTTER_NUMB_TXT AND
LegalAsst.ASGNE_TYPE_TXT = 'Legal Asst')
WHERE
(RPT_LITI_MTTER_FCT.MTTER_NUMB_TXT = 'L983057')


The RPT_MTTER_ASGNE_FCT table can contain multiple rows indicating the
people associated with the matter. For example, matter "x" could have
a Managing Attorney and a Legal Assistant associated with it.
 
J

John Spencer

Access (Jet) is very particular about parentheses in the FROM clause. I
think that the following may work for you, but ...

SELECT
RPT_LITI_MTTER_FCT.CO_NM,
ManagingAtty.FULL_NM AS 'MNGNG_ATTY_NM',
LegalAsst.FULL_NM AS 'LEGAL_ASST_NM'
FROM
(RPT_LITI_MTTER_FCT
LEFT JOIN RPT_MTTER_ASGNE_FCT AS ManagingAtty
ON ((ManagingAtty.MTTER_NUMB_TXT = RPT_LITI_MTTER_FCT.MTTER_NUMB_TXT) AND
(ManagingAtty.ASGNE_TYPE_TXT = 'Managing Atty')))
LEFT JOIN RPT_MTTER_ASGNE_FCT AS LegalAsst
ON ((LegalAsst.MTTER_NUMB_TXT = RPT_LITI_MTTER_FCT.MTTER_NUMB_TXT) AND
(LegalAsst.ASGNE_TYPE_TXT = 'Legal Asst'))
WHERE
(RPT_LITI_MTTER_FCT.MTTER_NUMB_TXT = 'L983057')


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
L

larrynator

Thanks, John. That doesn't give a syntax error but Access give the
generic "Microsoft Office Access has encountered a problem and needs
to close." Seems to happen on multiple machines.
 
J

John Spencer

I missed the aliases being set off with ' marks in the SELECT clause.
SELECT
RPT_LITI_MTTER_FCT.CO_NM,
ManagingAtty.FULL_NM AS MNGNG_ATTY_NM,
LegalAsst.FULL_NM AS LEGAL_ASST_NM
FROM (RPT_LITI_MTTER_FCT
LEFT JOIN RPT_MTTER_ASGNE_FCT AS ManagingAtty
ON ((ManagingAtty.MTTER_NUMB_TXT = RPT_LITI_MTTER_FCT.MTTER_NUMB_TXT) AND
(ManagingAtty.ASGNE_TYPE_TXT = 'Managing Atty')))
LEFT JOIN RPT_MTTER_ASGNE_FCT AS LegalAsst
ON ((LegalAsst.MTTER_NUMB_TXT = RPT_LITI_MTTER_FCT.MTTER_NUMB_TXT) AND
(LegalAsst.ASGNE_TYPE_TXT = 'Legal Asst'))
WHERE RPT_LITI_MTTER_FCT.MTTER_NUMB_TXT = 'L983057'

OR, you might try using sub-queries to get the desired result.

SELECT
RPT_LITI_MTTER_FCT.CO_NM,

(SELECT First(Full_NM)
FROM RPT_MTTER_ASGNE_FCT as R
WHERE R.MTTER_NUMB_TXT = RPT_LITI_MTTER_FCT.MTTER_NUMB_TXT
AND R.ASGNE_TYPE_TXT = 'Managing Atty') as MNGNG_ATTY_NM,

(SELECT First(Full_NM)
FROM RPT_MTTER_ASGNE_FCT as R
WHERE R.MTTER_NUMB_TXT = RPT_LITI_MTTER_FCT.MTTER_NUMB_TXT
AND ASGNE_TYPE_TXT = 'Legal Asst' ) as Legal_ASST_NM

FROM RPT_LITI_MTTER_FCT
WHERE RPT_LITI_MTTER_FCT.MTTER_NUMB_TXT = 'L983057'

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Top