June 4, 2013 at 9:00 am
Hi everybody,
I thought I had a well laid out query but I keep getting the "JOIN expression not supported" error. Any help you could provide would be greatly appreciated.
Thank you.
momba
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 distintct * 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]
)
) supertable
LEFT JOIN xxxSTAFF on (supertable.[Lname, Fname] = (xxxSTAFF.[Last] &", " &xxxSTAFF.[first] &" " &xxxSTAFF.[file]))
)
LEFT JOIN xxxAbsenceReasons on (supertable.Reason = xxxAbsenceReasons.Reason AND xxxSTAFF.Bank = xxxAbsenceReasons.Bank)
)
LEFT JOIN xxxBanks on xxxSTAFF.Bank = xxxBanks.Bank
)
LEFT JOIN xxxBanks on xxxSTAFF.Bank = xxxBank.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
June 4, 2013 at 9:37 am
You are joining on an expression on the right side, while the left side appears to be the two name fields separately. Create the same expression on the left side, and it should work.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
June 4, 2013 at 10:31 am
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
June 4, 2013 at 10:37 am
i had to add one more alias near the end to get this to parse correctly in SQL:
(also several columns are using reserved word names: xxxSTAFF.[File],xxxBanks.[Desc], xxxPayrollBankGroups.[Group])
...
FROM [xxxOES Absence 2006 - 2007]
) AnotherAlias
) allabsences
LEFT JOIN xxxSTAFF
...
Lowell
June 4, 2013 at 10:56 am
same syntax error...
select
supertable.Date
, supertable.[Lname, Fname]
, xxxSTAFF.Last
, xxxSTAFF.First
, xxxSTAFF.File AS FileNum
, xxxSTAFF.Title
, xxxSTAFF.PayrollTitle
, xxxSTAFF.Bank
, xxxSTAFF.[Active Staff] AS Active
, supertable.Reason
, xxxAbsenceReasons.ReasonDesc
, xxxAbsenceReasons.DOECode
, supertable.Substitute
, supertable.Comment
, supertable.OP198
, supertable.OP201
, supertable.EIS
, xxxBanks.Desc AS BankTitle
, xxxPayrollBankGroups.Group AS GroupNum
, [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
) dstabsences
LEFT JOIN xxxSTAFF on (dstabsences.[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
June 4, 2013 at 2:00 pm
I figured it out, I figured it out, I FIGURED IT OUT!!!!!
select
supertable.Date
, supertable.[Lname, Fname]
, supertable.Last
, supertable.First
, supertable.File AS FileNum
, supertable.Title
, supertable.PayrollTitle
, supertable.Bank
, supertable.[Active Staff] AS Active
, supertable.Reason
, xxxAbsenceReasons.ReasonDesc
, xxxAbsenceReasons.DOECode
, supertable.Substitute
, supertable.Comment
, supertable.OP198
, supertable.OP201
, supertable.EIS
, xxxBanks.Desc AS BankTitle
, xxxPayrollBankGroups.Group AS GroupNum
, [xxxOES Sites].Principal
, [xxxOES Sites].AsstPrin
, [xxxOES Sites].Site
, supertable.[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
) dstabsences
LEFT JOIN xxxSTAFF on (dstabsences.[Lname, Fname] = (xxxSTAFF.[Last] &", "& xxxSTAFF.[first] &" "& xxxSTAFF.[file]))
) supertable
LEFT JOIN xxxAbsenceReasons on (supertable.BankReason = (xxxAbsenceReasons.Bank & xxxAbsenceReasons.Reason))
)
LEFT JOIN xxxBanks on supertable.Bank = xxxBanks.Bank
)
LEFT JOIN xxxPayrollBankGroups ON xxxBanks.Bank = xxxPayrollBankGroups.Bank
)
LEFT JOIN [xxxOES Sites] ON supertable.[Official Class] = [xxxOES Sites].[Official Class]
WHERE [xxxOES Sites].[CLOSED SITE]=No
Thanks for your help!
June 4, 2013 at 3:52 pm
FWIW, if you are a relative novice at creating SQL statements in Access, the query designer will do a very nice job for you and make troubleshooting such statements much easier. (I even use the SQL Server query designer on occasion.)
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply