Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / November 2005

Tip: Looking for answers? Try searching our database.

Select Query "Where" Problem

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Pringle9984 - 11 Nov 2005 13:45 GMT
I'm using the following query

SELECT job.job_skyjob, sky_promocodes.promo_id
digibox.*, customer.*, engineer.engineer_code, job.
FROM ((sky_promocodes INNER JOIN (job INNER JOIN custome
ON job.customer_id = customer.customer_id) O
sky_promocodes.promo_id = job.job_promocode) LEFT JOIN digibox O
job.job_number = digibox.job_id) INNER JOIN engineer O
job.engineer_id = engineer.engineer_i
WHERE (((job.job_skyjob)=Yes)
ORDER BY job.job_number

It *should* find the details tied to every job where job.job_skyjob
Yes; and it almost works; however it will only show the jobs wher
job.job_skyjob = Yes and where the promocode is not null

Any suggestions on how to fix this problem
[MVP] S.Clark - 11 Nov 2005 16:59 GMT
When you add the WHERE to the outer join, it treats it like an INNER join.

You can break it out to two queries.  One to do the WHERE the other to do
the LEFT.

Signature

Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

> I'm using the following query:
>
[quoted text clipped - 13 lines]
>
> Any suggestions on how to fix this problem?
John Spencer - 11 Nov 2005 17:07 GMT
You will probably have to change the join involved to a Left Join (or
possibly a right join) since you are joining on the promoCode.  This may
give you an ambiguous join error if you try to use the right join.

I always have a problem envisioning these multi-table joins which is the
reason I like using the query grid when possible to build the query.  Here
is a stab at rewriting the FROM clause.  No guarantees.

SELECT  sky_promocodes.promo_id,
digibox.*, customer.*, engineer.engineer_code, job.*
FROM ((((job LEFT JOIN Customer ON job.customer_id = customer.customer_id )
LEFT JOIN sky_promocodes  job.job_promocode = sky_promocodes.promo_id )
LEFT JOIN digibox ON job.job_number = digibox.job_id)
LEFT JOIN engineer ON job.engineer_id = engineer.engineer_id)
WHERE job.job_skyjob=Yes

> I'm using the following query:
>
[quoted text clipped - 13 lines]
>
> Any suggestions on how to fix this problem?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.