?
Solved

Left join queries

Posted on 2008-06-19
6
Medium Priority
?
214 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
[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
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
DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

 
LVL 19

Accepted Solution

by:
frankytee earned 1500 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 1500 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

770 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