• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 471
  • Last Modified:

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
0
raymurphy
Asked:
raymurphy
  • 4
  • 2
  • 2
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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:

http://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)
0
 
hnasrCommented:
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.
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3597-INNER-JOIN-a-Number-Of-Tables.html
0
 
raymurphyAuthor Commented:
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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
hnasrCommented:
If you have a field f1 in table1 and a field f1 in table2 then:

Select f1 from table1; ' works
Select f1 from table2; 'works
Select f1 from table1, table2; ' Cartesian join. will not work because f1 "could refer to more than one table listed in the FROM clause."
Select table1.f1, table2.f1 from table1, table2; ' works.

Open in new window

0
 
raymurphyAuthor Commented:
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 ?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are any of the "Tables listing in the FROM clause" actually queries?

If so, do any of those queries include that table/field?

Perhaps aliasing jiraissue:

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

Open in new window

0
 
raymurphyAuthor Commented:
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 ...
0
 
raymurphyAuthor Commented:
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 ...
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now