SELECT
    ...
    xyz.code
    , Sum(
    IIf([ABSENCE_TYPE01] In ("L","S","Z"),1,0)
    +IIf([ABSENCE_TYPE02] In ("L","S","Z"),1,0)
    +IIf([ABSENCE_TYPE03] In ("L","S","Z"),1,0)
    +IIf([ABSENCE_TYPE04] In ("L","S","Z"),1,0)) AS Explnd
    ....
FROM xyz
WHERE ...
GROUP BY
  xyz.code;

, Sum(
    IIf([ABSENCE_TYPE01] In (SELECT Unexp FROM tblAbsenceCodes),1,0)
    ...