Need help with SQL 'report'

[godsspeed]

Ok, so im not sure if this is the best place to put this... so if its not, mods can move as they see fit.

I have this (and many many others) report that I use several times a day depending and need some help.

DISCLAIMERS: 1) I'm no SQL expert (more of a newb) 2) this was created by someone else before me 3) i have limited access to the system/db that this is pulling from

Ok so real quick: We have several clients who have several more 'jobs' that we produce. Each job had a Catalog # of which stock that job requires. here is the existing report that i've been using (but noticed a problem)

Select
Job_Path.Dept_Name,
Job_List.JobName,
Job_List.Form,
Job_List.Description,
Job_List.Active as Status,
Job_List.WT_DEPT_NAME,
Job_Path.Step_ID,
BOM.Cat_No,
BOM.Item_Type,
BOM.Description,
Activities.Activity_Name,
Generic_Qualities.Generic_Quality_ID,
Generic_Qualities.Quality_Note,
Qualities.Quality_ID,
Qualities.Quality_Note,
Change_Control.Control_Note,
Change_Control.Begin_Date,
Change_Control.End_Date,
Job_Path.Pref_Equip_No,
Setups.Setup_Type,
Setups.Setup_Parameter
From Job_List join Job_Path on Job_List.Job_List_Code = Job_Path.Job_List_Code
left JOIN Setups ON Job_Path.Job_Path_Code = Setups.Job_Path_Code
left join Activities on Job_Path.Activity_Name = Activities.Activity_Name and Job_Path.Dept_Name = Activities.Dept_Name
left join BOM on Job_Path.Job_Path_Code = BOM.Job_Path_Code
left join Generic_Qualities on Activities.Activity_Code = Generic_Qualities.Activity_Code
left join Qualities on Job_Path.Job_Path_Code = Qualities.Job_Path_Code
left join Change_Control on Job_Path.Job_Path_Code = Change_Control.Job_Path_Code
Where Job_List.Active not like 'Obsolete%' and
Job_List.Client_No = '_______' and
BOM.Cat_No = '______________'
Order by Active, JobName, Step_ID, Begin_Date, End_Date, Activity_ID, Quality_ID, Generic_Quality_ID

what this will do is it will show all jobs for for specified client that call for specified Cat#. Here's the problem... this will only show the 'activities' for each jobs that fit the criteria. so if a job has 2 activities that contain the specified Cat# only those will get pulled in. what i need it to do is pull in ALL activities for each job that uses that Cat#! not just the parts of each job. does that make sense?!

Much thanks in advnace!! also, i'll try to answer any questsion you may have, but due to my limited ability, i make no promises how helpful i may be.

 

[SQL_Hell]

Hi,

Yeah it's difficult to understand what you mean:

Quote:
so if a job has 2 activities that contain the specified Cat# only those will get pulled in. what i need it to do is pull in ALL activities for each job that uses that Cat#! not just the parts of each job

So you want all Activities for all jobs regardless of what the Activities Cat# is??

There doesn't seem to anything referencing the Activities cat no, could it possibly be the Job_Path.Dept_Name and Activities.Dept_Name?

Select 
Job_Path.Dept_Name, 
Job_List.JobName, 
Job_List.Form, 
Job_List.Description,
Job_List.Active as Status,
Job_List.WT_DEPT_NAME,
Job_Path.Step_ID, 
BOM.Cat_No,
BOM.Item_Type,
BOM.Description, 
Activities.Activity_Name,
Generic_Qualities.Generic_Quality_ID, 
Generic_Qualities.Quality_Note, 
Qualities.Quality_ID,
Qualities.Quality_Note,
Change_Control.Control_Note, 
Change_Control.Begin_Date, 
Change_Control.End_Date,
Job_Path.Pref_Equip_No,
Setups.Setup_Type, 
Setups.Setup_Parameter
From Job_List join Job_Path on Job_List.Job_List_Code = Job_Path.Job_List_Code
left JOIN Setups ON Job_Path.Job_Path_Code = Setups.Job_Path_Code 
left join Activities on Job_Path.Activity_Name = Activities.Activity_Name --and Job_Path.Dept_Name = Activities.Dept_Name
left join BOM on Job_Path.Job_Path_Code = BOM.Job_Path_Code
left join Generic_Qualities on Activities.Activity_Code = Generic_Qualities.Activity_Code 
left join Qualities on Job_Path.Job_Path_Code = Qualities.Job_Path_Code 
left join Change_Control on Job_Path.Job_Path_Code = Change_Control.Job_Path_Code
Where Job_List.Active not like 'Obsolete%' and 
[B]Job_List.Client_No = '_______' and[/B]
[B]BOM.Cat_No = '______________'[/B]
Order by Active, JobName, Step_ID, Begin_Date, End_Date, Activity_ID, Quality_ID, Generic_Quality_ID

 

[godsspeed]

A "job" can have lets say 8 Activities. One of those Activities will contain the Cat #'s needed. The current Query just pulls out those steps that have the Cat#. so if Step 2 is a Gather Material step with the Cat # and Step 3 is the next step, step 3 doesnt get included because it doesnt have that Cat# in the BOM (bill of material) section. the problem is, Step 3 has other important machine setup information in regards to the Cat #.... so my issue is if i want to find all jobs that call for a certain Cat#, it will show me all of those jobs, however it will only show me the steps containing the Cat#, and not the rest of the job information. i would like to see all of the steps for all of the jobs containing that Cat#.

I'm assuming it may not be possible due to the way everything is setup and how it works...
again, im not too familar with sql... know enough to kind of 'get by' but wouldnt go as far as to say im doing everything the most effcient/best way.

i am really familiar with Excel. And if the proccess if familiar i can understand what im looking for may not be easy/possible. If you filter a datatable, it will retrieve the individual records that meet the criteria... which is how this appears to generate as well.

 

[AccessMSSQL]

Try a subquery in your WHERE clause - replace your BOM.Cat_No = '___') to this

AND Job_List.Job_List_Code IN (SELECT Job_List_Code FROM Job_Path inner join BOM on Job_Path.Job_Path_Code = BOM.Job_Path_Code WHERE BOM.Cat_No = '___')

 

[godsspeed]

HOLY COW, I THINK THAT WORKS!!!!!!!!!!! I'll have to run a few tests to make sure, but first glance looks perfect!!!

1000000 THANKS TO YOU!!!