As suggested, I revised the code (see below) but now I'm getting the error "Syntax error in JOIN expression". I can't seem to spot the issue...:unsure:
select
supertable.Date
, supertable.[Lname, Fname]
, xxxSTAFF.Last
, xxxSTAFF.First
, xxxSTAFF.File
, xxxSTAFF.Title
, xxxSTAFF.PayrollTitle
, xxxSTAFF.Bank
, xxxSTAFF.[Active Staff] AS Active
, supertable.Reason
, supertable.DOECode
, xxxAbsenceReasons.ReasonDesc
, xxxAbsenceReasons.DOECode
, supertable.Substitute
, supertable.Comment
, supertable.OP198
, supertable.OP201
, supertable.EIS
, xxxBanks.Desc AS BankTitle
, xxxPayrollBankGroups.Group
, [xxxOES Sites].Principal
, [xxxOES Sites].AsstPrin
, [xxxOES Sites].Site
, xxxSTAFF.[Official Class] AS SiteID
FROM
(((
(
Select Bank & Reason AS BankReason, * from
(
select distinct * from
(
SELECT [Lname, Fname], Date , Reason , DOECode, Substitute, Comment, OP198, OP201, EIS
FROM [OES Absence]
union
SELECT [Lname, Fname], Date , Reason , DOECode, Substitute, Comment, OP198, OP201, EIS
FROM [xxxOES Absence 2011 - 2012 thru 20120807]
union
SELECT [Lname, Fname], Date , Reason , DOECode, Substitute, Comment, OP198, OP201, EIS
FROM [xxxOES Absence 2010 - 2011 thru 20120113]
union
SELECT [Lname, Fname], Date , Reason , DOECode, Substitute, Comment, OP198, OP201, EIS
FROM [xxxOES Absence 2009 - 2010 thru 20101209]
union
SELECT [Lname, Fname], Date , Reason , DOECode, Substitute, Comment, OP198, OP201, EIS
FROM [xxxOES Absence 2008 - 2009 thru 20100525]
union
SELECT [Lname, Fname], Date , Reason , DOECode, Substitute, Comment, OP198, OP201, EIS
FROM [xxxOES Absence 2007 - 2008]
union
SELECT [Lname, Fname], Date , Reason , DOECode, Substitute, Comment, OP198, OP201, EIS
FROM [xxxOES Absence 2006 - 2007]
)
) allabsences
LEFT JOIN xxxSTAFF on (allabsences.[Lname, Fname] = (xxxSTAFF.[Last] &", " &xxxSTAFF.[first] &" " &xxxSTAFF.[file]))
) supertable
LEFT JOIN xxxAbsenceReasons on (supertable.BankReason = (xxxAbsenceReasons.Bank & xxxAbsenceReasons.Reason))
)
LEFT JOIN xxxBanks on xxxSTAFF.Bank = xxxBanks.Bank
)
LEFT JOIN xxxPayrollBankGroups ON xxxBanks.Bank = xxxPayrollBankGroups.Bank
)
LEFT JOIN [xxxOES Sites] ON xxxSTAFF.[Official Class] = [xxxOES Sites].[Official Class]
WHERE [xxxOES Sites].[CLOSED SITE]=No