Reporting with different queries

Chief

Registered User.
Local time
Yesterday, 17:08
Joined
Feb 22, 2012
Messages
156
Hello,
Ill do my best to explain what i am trying to achieve.
I have a query which looks at an entered date being the date of Dispatch for several types of jobs.
The types of jobs then have dates that a certain process has to be done by to achieve the dispatch date.
I want to be able to count how many jobs based on the dispatch date entered, at each process.
example;
Enter Dispatch date; 11/06/14 (this is working no problem) and I get how many jobs are being dispatched on that date. which is one. (easy)
How ever; each job type has a different type of process at different dates.
so I want to count each process.
example;
How many jobs are also being detailed on the same date as the job above? I know there is 3 other jobs being detailed on that date (the date not being the dispatch date but the TechDetailDue Date).
my query is only showing the one job being dispatched. (makes sense cause that is what I have asked for)
Here is my initial query;

SELECT job_info.jobid, job_info.made_co, job_info.dispatch_due, job_type.jobtype, job_info.dispatch, job_info.tech_detail_due, job_info.tech_detail_complete, job_info.Assembled, customer.company_name, job_info.customer_reference, job_info.order_recd, job_info.DateEntered, job_info.Pending, tblPending.Description, job_info.biesse_due, job_info.MCDispatchDue, job_info.TwoPakOutBy, job_info.hinge_drill_due, job_info.comments, job_info.SplitJob, job_info.Crate, job_info.OrderOther, job_info.CVOrder
FROM ((job_type INNER JOIN job_info ON job_type.jobtypeid = job_info.job_type) INNER JOIN customer ON job_info.customer = customer.companyid) LEFT JOIN tblPending ON job_info.PendingDesc = tblPending.PendingID
WHERE (((job_info.dispatch_due)=[Forms]![reports_list]![txtDateToOps]) AND ((job_info.dispatch) Is Null) AND ((job_info.tech_detail_due) Is Not Null) AND ((job_info.tech_detail_complete) Is Null))
ORDER BY job_info.tech_detail_due;

How do I now using above query add another query or alter this query to look up these other fields to report on?

Sounds confusing, sorry....
 
This is for entering a new job into my schedule. I run this query to see what other jobs are being tech detailed on the corresponding date. So its like I get the dispatch due date for job, then Look at the Tech detail due date for that job im trying to enter, then query that date to report on what else is already booked in...
 
I think I solved it with this.

SELECT qryOpsRptTechDetail.tech_detail_due, job_info.jobid, job_info.made_co, job_info.job_type, job_info.tech_detail_due, job_info.tech_detail_complete, job_info.dispatch, job_info.Pending
FROM (job_info INNER JOIN job_type ON job_info.job_type = job_type.jobtypeid) INNER JOIN qryOpsRptTechDetail ON job_info.tech_detail_due = qryOpsRptTechDetail.tech_detail_due
WHERE (((job_info.tech_detail_due) Is Not Null) AND ((job_info.tech_detail_complete) Is Null) AND ((job_info.dispatch) Is Null));
 

Users who are viewing this thread

Back
Top Bottom