Solved

Left join queries

Posted on 2008-06-19
6
208 Views
Last Modified: 2013-11-28
In access I have 4 tables each which has a common change #. I am trying to create a query that will do a left join on one of the tables to add info from the other three, if there is anything to add, some records will have additions and some will not. I get one left join to work but when trying to add other 2 it doesnt work. Is there a limit or is my code wrong? I have posted it below. Thanks

SELECT Time.[Change Number], Time.Summary, Time.[Area], Time.Code, Time.Category, Time.[Start Time], Time.[End Time], Time.[Total Duration], Time.[Service], Time.[Service Start Time], Time.[Service End Time], Time.[Reason], Time.[Group], Time.Service.[Impact Service], Time.Product.[Impact Service], Time.Al, Al.[Impact Application]
FROM Time LEFT JOIN Al ON Time.[Change Number] = Al.[Associated Change Order Number]
LEFT JOIN Service ON Time.[Change Number] ] = Al.[Associated Change Order Number]
LEFT JOIN Service ON Product.[Change Number] ] = Product.[Associated Change Order Number]
WHERE (((Time.[Change Area]) Like "*Amr*"));
0
Comment
Question by:pgmtkl
6 Comments
 

Expert Comment

by:suresh-infotech
ID: 21821604
Hi. try this

SELECT Time.[Change Number], Time.Summary, Time.[Area], Time.Code, Time.Category, Time.[Start Time], Time.[End Time], Time.[Total Duration], Time.[Service], Time.[Service Start Time], Time.[Service End Time], Time.[Reason], Time.[Group], Time.Service.[Impact Service], Time.Product.[Impact Service], Time.Al, Al.[Impact Application]
FROM ((Time LEFT JOIN Al ON Time.[Change Number] = Al.[Associated Change Order Number])
LEFT JOIN Service ON Time.[Change Number] ] = Al.[Associated Change Order Number])
LEFT JOIN Service ON Product.[Change Number] ] = Product.[Associated Change Order Number]
WHERE (((Time.[Change Area]) Like "*Amr*"));
0
 

Author Comment

by:pgmtkl
ID: 21821669
I get join expression not supported. Does that have to do with access?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21827133
pgmtkl,

Perhaps, there are certain join types that Access just has trouble resolving.
Your statement might just be too complex for Access (Jet-SQL).

Here are some good links on the subject:
http://support.microsoft.com/kb/208878
http://support.microsoft.com/kb/103429

Try to combine sets of joins into one query, then join the joined queries.
Make sense?
;-)

JeffCoachman
 
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 19

Accepted Solution

by:
frankytee earned 500 total points
ID: 21828942
you have too many ] brackets in your join, try:
SELECT Time.[Change Number], Time.Summary, Time.[Area], Time.Code, Time.Category, Time.[Start Time], Time.[End Time], Time.[Total Duration], Time.[Service], Time.[Service Start Time], Time.[Service End Time], Time.[Reason], Time.[Group], Time.Service.[Impact Service], Time.Product.[Impact Service], Time.Al, Al.[Impact Application]

FROM Time LEFT JOIN Al ON Time.[Change Number] = Al.[Associated Change Order Number]
LEFT JOIN Service ON Time.[Change Number]  = Al.[Associated Change Order Number]
LEFT JOIN Service ON Product.[Change Number]  = Product.[Associated Change Order Number]

WHERE (Time.[Change Area]) Like "*Amr*");
0
 
LVL 19

Assisted Solution

by:frankytee
frankytee earned 500 total points
ID: 21828961
on closer inspection, why are you joining to the Service table on fields from product joined to A1?, ie
LEFT JOIN Service ON Time.[Change Number]  = Al.[Associated Change Order Number]

it should be something like
LEFT JOIN Service ON Time.[Change Number]  = service.whateverFieldThatJoinsToYourTimeTable
0
 

Author Comment

by:pgmtkl
ID: 21867440
i will try the above ways.
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.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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