I a SQL guy and have this query running fine in SQL, but I need to run this query for an MSAccess database. I'm using the SQL View in Query Design in MSAccess and getting a UNION syntax error??
Can someone help me make this query MSAccess compatible please.
select Bill_Of_Jobs.Component_Job
As Job, job.Assembly_Level As Lvl, job.Customer, isnull(job.Customer_PO,"")
As Customer_PO,
job.Part_Number, job.Description, isnull(job.Rev,"") As Rev, material_req.Material, material_req.Quantity_Per,
material_req.Part_Length, material_req.Part_Width, material_req.Est_Qty, material_req.UofM
from (Job left outer join bill_of_jobs on bill_of_jobs.Parent_Job=jo
b.job)
left outer join Material_Req on bill_of_jobs.component_job
=material_
req.job where bill_of_jobs.root_job='106
86'
UNION ALL
select Job.Job As Job, job.Assembly_Level As Lvl, job.Customer, isnull(job.Customer_PO,"")
As Customer_PO,
job.Part_Number, job.Description, isnull(job.Rev,"") As Reve, material_req.Material, material_req.Quantity_Per,
material_req.Part_Length, material_req.Part_Width, material_req.Est_Qty, material_req.UofM
from Job
left outer join Material_Req on job.job=material_req.job
where job.job='10686'
UNION ALL
select Job.Job As Job, max(job.Assembly_Level) As Lvl, max(job.Customer) As Customer, isnull(max(job.Customer_PO
),"") As Customer_PO,
max(job.Part_Number) As Part_Number, max(job.Description) As Description, isnull(max(job.Rev),""') As Reve, max(job.Part_Number) As Material,
max(bill_of_jobs.Relations
hip_Qty) As Quantity_Per, 0 As Part_Length, 0 As Part_Width,
(max(job.Make_Quantity)*ma
x(bill_of_
jobs.relat
ionship_qt
y)) As Est_Qty, "" As UofM
from Job inner join Bill_Of_Jobs on job.job = Bill_Of_Jobs.Component_Job
inner join Material_Req on job.job=material_req.job
where bill_of_jobs.root_job='106
86'
and job.Assembly_Level > 0
group by job.job
order by Job;
Start Free Trial
