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.
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.