Left join queries

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*"));
pgmtklAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
frankyteeConnect With a Mentor Commented:
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
 
suresh-infotechCommented:
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
 
pgmtklAuthor Commented:
I get join expression not supported. Does that have to do with access?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jeffrey CoachmanMIS LiasonCommented:
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
 
frankyteeConnect With a Mentor Commented:
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
 
pgmtklAuthor Commented:
i will try the above ways.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.