Solved

Adding LEFT JOIN to existing LEFT JOINs in Access

Posted on 2014-01-23
8
458 Views
Last Modified: 2016-12-08
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
Comment
Question by:raymurphy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 84
ID: 39803395
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
 
LVL 30

Expert Comment

by:hnasr
ID: 39803739
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
 

Author Comment

by:raymurphy
ID: 39806622
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Accepted Solution

by:
hnasr earned 250 total points
ID: 39807411
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
 

Author Comment

by:raymurphy
ID: 39835276
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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 39835345
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
 

Author Comment

by:raymurphy
ID: 39855537
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
 

Author Closing Comment

by:raymurphy
ID: 39876996
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question