Solved

Adding LEFT JOIN to existing LEFT JOINs in Access

Posted on 2014-01-23
8
453 Views
Last Modified: 2014-02-21
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
  • 4
  • 2
  • 2
8 Comments
 
LVL 84
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 30

Accepted Solution

by:
hnasr earned 250 total points
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:raymurphy
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now