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?