Link to home
Start Free TrialLog in
Avatar of raymurphy
raymurphy

asked on

Adding LEFT JOIN to existing LEFT JOINs in Access

Completely new to Access development (coming from SQL Server development) and have been dragged into looking at an Access problem.

An Access front-end report uses the following code which works OK :

SELECT [Report: GetAuthNumber].STRINGVALUE AS Auth, jiraissue.pkey AS [Jira Issue], jiraissue.SUMMARY, worklog.CREATED, worklog.AUTHOR AS Name, [timeworked]/3600 AS [Time]
FROM worklog LEFT JOIN (jiraissue LEFT JOIN [Report: GetAuthNumber] ON jiraissue.pkey = [Report: GetAuthNumber].pkey)
ON worklog.issueid = jiraissue.ID
WHERE (((worklog.CREATED) Between [Start Date] And [End Date]))
ORDER BY [Report: GetAuthNumber].STRINGVALUE, jiraissue.pkey;

I need to anend that report to include an additional LEFT JOIN against the jiraissue table, and to pick up an additional column in the SELECT column list.

So the new column to be picked up is :

CONCAT_WS('-',project_key.project_key, issuenum) AS 'Jira Issue'

and the additional LEFT JOIN is :

LEFT JOIN (project_key ON jiraissue.project = project_key.project_id)

I therefore amended the code so that it now reads :

SELECT [Report: GetAuthNumber].STRINGVALUE AS Auth, jiraissue.pkey AS [Jira Issue], CONCAT_WS('-',project_key.project_key, issuenum) AS 'Jira Issue', jiraissue.SUMMARY, worklog.CREATED, worklog.AUTHOR AS Name, [timeworked]/3600 AS [Time]
FROM worklog LEFT JOIN (jiraissue LEFT JOIN [Report: GetAuthNumber] ON jiraissue.pkey = [Report: GetAuthNumber].pkey)
LEFT JOIN (project_key ON jiraissue.project = project_key.project_id)
ON worklog.issueid = jiraissue.ID
WHERE (((worklog.CREATED) Between [Start Date] And [End Date]))
ORDER BY [Report: GetAuthNumber].STRINGVALUE, jiraissue.pkey;

But on saving the report I get 'Syntax error in FROM Clause'.

Please pardon my ignorance on Access syntax, but can anyone please advise on what I'm doing wrong ?

Thanks

Ray
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Concat_WS is a MySQL Function, I believe, and isn't recognized by Access. Access does not have a inbuilt Concat function (except in the new 2013 version), but patrickmatthews has provided one here:

https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

There are quite a few examples included with it, so use one of those for a roadmap on replacing Concat_WS.

Also: You have two columns named "Jira Issue":

jiraissue.pkey AS [Jira Issue]
CONCAT_WS('-',project_key.project_key, issuenum) AS 'Jira Issue'

When you fix the Concat, you should rename one.

The Join syntax should work, although Access would write it like this:

LEFT JOIN jiraissue ON worklog.issueid = jiraissue.ID
LEFT JOIN [Report: GetAuthNumber] ON jiraissue.pkey = [Report: GetAuthNumber].pkey)
LEFT JOIN (project_key ON jiraissue.project = project_key.project_id)
In general: From entity left join entity
                   From (entity left join entity) Left Join entity
Or:              From entity Left Join (entity left join entity)

Select t1.f1, t2.f2, t3.f3, t4.f4
FROM (( t1 Left Join t2 On t1.x = t2.x)
                   Left Join t3 On t2.x = t3.x)
                   Left Join t4 On t3.x = t4.x
Where ...
The whole expression after From behaves as one entity.
Number of opening braces = no of tables - 2
closing braces are inserted around the previous entry, The left join is between the new table and the previous entity.

To add a a table t5 left joined to above sql
Select t5.x, t1.f1, t2.f2, t3.f3, t4.f4
FROM t5 left Join ((( t1 Left Join t2 On t1.x = t2.x)
                   Left Join t3 On t2.x = t3.x)
                   Left Join t4 On t3.x = t4.x)   On t5.x =  t1.x
....

Notice adding one extra table requires extra braces to previous entity.

To have the previous entity left joined to t5:
Select t5.x, t1.f1, t2.f2, t3.f3, t4.f4
FROM  ((( t1 Left Join t2 On t1.x = t2.x)
                   Left Join t3 On t2.x = t3.x)
                   Left Join t4 On t3.x = t4.x)   t5 left Join On t5.x =  t4.x ' can be t1.x
....
Check this article dealing with Inner Join.
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3597-INNER-JOIN-a-Number-Of-Tables.html
Avatar of raymurphy
raymurphy

ASKER

Thanks for both replies, very useful information .....

Have now got this version of the code which almost works :

SELECT [Report: GetAuthNumber].STRINGVALUE AS Auth,
        [project_key].[project_key] & '-' & [jiraissue].[issuenum] AS JiraIssueNo,
        [jiraissue].SUMMARY,
        worklog.CREATED, worklog.AUTHOR AS Name, [timeworked]/3600 AS [Time]
 FROM
 (
 worklog
 LEFT JOIN (jiraissue LEFT JOIN [Report: GetAuthNumber]
ON jiraissue.pkey = [Report: GetAuthNumber].pkey)
ON worklog.issueid = jiraissue.ID
 )
LEFT JOIN project_key ON jiraissue.PROJECT = project_key.PROJECT_ID
WHERE (((worklog.CREATED) Between [Start Date] And [End Date]))
ORDER BY [Report: GetAuthNumber].STRINGVALUE, jiraissue.pkey;

So I'm happy with that syntax. Where I say 'almost' works, please note the line :

        [project_key].[project_key] & '-' & [jiraissue].[issuenum] AS JiraIssueNo,

With that line, I get an Access error (shown on attached screenshot) saying that
"specified field [jiraissue].[issuenum] could refer to more than one table listed in the FROM clause" ??

If I change the issuenum reference to any other column on the jiraissue table, the report will actually execute, although obviously the output doesn;t make sense - eg I can replace issuenum with description, and the report will actually run.

Can't see why this error should be coming up, as I'm only referencing
 [jiraissue].[issuenum]  once in the query ?
SNAG-357.gif
ASKER CERTIFIED SOLUTION
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Please accept my apologies for not getting back regarding this issue. Comments from hnasr are noted and appreciated, but as per my last comment (24 Jan) as far as I can see the code is only making one reference to this 'issuenum' column and that column is not held in any of the other tables used in the query (and [jiraissue].[issuenum] is being named explicitly).

Also, as mentioned the query will work if 'issuenum' is replaced with any other column from the  jiraissue table - so I;m completely stumped as to why I get the error when specifying the [jiraissue].[issuenum] column ?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for following this up, and sorry for taking so long to get back ... Will get back to you once I've done further checks ...
Still get the same issue where specifying the [jiraissue].[issuenum] column  fives the error message about "specified field [jiraissue].[issuenum] could refer to more than one table listed in the FROM clause".

Most puzzling thing is that the query will work if 'issuenum' is replaced with any other column from the  jiraissue table it works ......

Anyway, I've hit a brick wall with this, so I'll just have to wrap it here as issue, and split some points with you guys for giving some great and helpful advice - thanks ...