Solved

Left join queries

Posted on 2008-06-19
6
206 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

895 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

17 Experts available now in Live!

Get 1:1 Help Now