Join expression not supported in join query Access

[atzdgreat]

Hi i would like to ask for your assistance why i'm getting error. "JOIN expression not supported."

Code:

SELECT
    t2.KPICode1 AS OrigCode,
    t0.KPITask AS OrigTask,
    t2.ID AS TimID,
    t2.Score AS OrigScore,
    t3.ID AS AccID,
    t3.Score AS DepScore,
    t1.KPICode AS DepCode,
    t1.KPITask AS DepTask,
    IIF(ISNULL(t4.RevCount), 0, t4.RevCount) AS RevCount
FROM
    (((KPI_qryMain AS t0
    LEFT JOIN KPI_qryMain AS t1 ON t1.KPICode = t0.ChildKPICode)
    LEFT JOIN KPI AS t2 ON t2.KPICode = t0.KPICode)
    LEFT JOIN KPI AS t3 ON t3.KPICode = t0.ChildKPICode AND t3.UserID = t2.UserID)
    LEFT JOIN
    (SELECT COUNT(*) AS RevCount, RefNum
     FROM KPI_tblLogs
     WHERE Title = 'Daily Task'
     AND LogDetails LIKE '%For revision%'
     GROUP BY RefNum) AS t4 ON t4.RefNum = CSTR(t2.ID)
WHERE
    t2.DueDate = #8/12/2024 6:00:00 PM#
    AND t3.DueDate = #8/12/2024 6:00:00 PM#
    AND t0.KPICode = 'SRSMRT03'
    AND t2.UserID = 'XXX000165'

 

[MajP]

It is probably in the where clause and my bet it is Title, but any place you repeat fields names in two tables of the join you have to specify the table name..

 

[atzdgreat]

HI @MajP thank you for your reply. i'm not quite sure but i already specify the table which is KPI_qryMain as t0.

 

[Eugene-LS]

i'm getting error. "The specified field 't0.UserID' could refer to more than one table

Maybe the "KPI_qryMain" query outputs the "UserID" field twice
Check it out

 

[atzdgreat]

i tried to change the my query to

LEFT JOIN KPI AS t3 ON (t3.UserID = t1.UserID) AND (t3.KPICode = t1.KPICode))

but still gets the same error. the specified field 't1.UserID' could refer to more thn one table.

@Eugene-LS thank you for your reply. i'm not sure how the query outputs 'UserID' field twice but when i remove t3.UserID = t1.UserID, it works.

 

[plog]

Not relevant to your issue, but from a logic standpoint t2 and t3 are not in a LEFT JOIN, they are effectively in an INNER JOIN.

... AND t2.UserID = 'XXX000165'

The only way that can be true if a record in t2 matches a record in t0.
You negate the LEFT JOIN when you place one of that tables fields in the WHERE.

 

[The_Doc_Man]

It shouldn't need it, but maybe you need to qualify the fields in the sub-query based on KPI_tblLogs. Make it From "KPI_tblLogs As KT" - which would let you qualify KT.Title and KT.LogDetails - that type of thing. I don't see an obvious ambiguity - but then there is the question of what KPI_queryMain looks like. Is it also a JOIN query?

 

[atzdgreat]

Hi @plog and @The_Doc_Man thank you for reply.

so this is a sample of my raw data

KPI_qryMain

KPICode	KPICode1	KPITask	ChildKPICode	ChildKPICode1
SRSMRT01	SRSMRT01	Washing Plates	SRSMRT02	SRSMRT02
SRSMRT02	SRSMRT02	Cleaning Bedroom
SRSMRT03	SRSMRT03	Laundry

KPI

UserID	KPICode	DueDate	Score
XXX000165	SRSMRT01	[td width="93pt"] 8/12/2024 18:00​	1
XXX000165	SRSMRT02	[td width="93pt"] 8/12/2024 18:00​	2

​

[/td]

​

[/td]

​

RESULT TO ACHIEVE

UserID	OrigCode	OrigTask	OrigScore	DepCode	DepTask	DepScore
XXX000165	SRSMRT01	Washing Plates	1	SRSMRT02	Cleaning Bedroom	2

if the user will view their KPI like SRSMRT01, this will search in KPI_qryMain. once KPI has dependency (which is the ChildKPICode "SRSMRT02") both KPI will be display as result to achieve.

 

[CJ_London]

You’ve probably got a SELECT * in your main query