Hi everyone I need some help again. I am not good with VB and modules so hopefully I am making a rookie mistake.
I am using some VB code I found on the web to assign a "Priority Code" to a list of Jobs. (The code is creating a numeric series.) Here's the code:
Here's it called out in a query where the results are what I want:
Here is the query from above when run - gives me exactly what I want (Note job#64782-C is assigned priority #1 - this is correct.):
But when I join that query with another, the priority no longer works the way I want. Is there a way to have the "good" query run first before running the one it's joined to?
And here is the joined query (that produces the wrong result) SQL code:
SELECT [Active Jobs Query].Job, [Dispatch Priority4 Query].PriorityKey, [Dispatch PO5 Query].MatlStat, [Dispatch PO5 Query].Due, dbo_Job.Part_Number, dbo_Job.Description AS PNDescrip, [Combo Jobs2 Query].[CNCWC Count], [Combo Jobs2 Query].[TtlCNC Ops], [SawOP1 Query].SawStatus, dbo_Job_Operation.Sequence, dbo_Job_Operation.Work_Center, Left([work_center],3) AS WCTrim, dbo_Job_Operation.Operation_Service, dbo_Job_Operation.Description AS OPDescrip, dbo_Job_Operation.Status AS OpStatus, dbo_Job_Operation.Sched_Start, dbo_Job_Operation.Sched_End, dbo_Job.Make_Quantity, dbo_Job_Operation.Run_Qty, dbo_Job_Operation.Est_Setup_Hrs, dbo_Job_Operation.Est_Run_Hrs
FROM (((((([Active Jobs Query] INNER JOIN dbo_Job_Operation ON [Active Jobs Query].Job = dbo_Job_Operation.Job) INNER JOIN [CNC Work Centers] ON dbo_Job_Operation.Work_Center = [CNC Work Centers].[CNC WC]) INNER JOIN dbo_Job ON [Active Jobs Query].Job = dbo_Job.Job) INNER JOIN [Combo Jobs2 Query] ON [Active Jobs Query].Job = [Combo Jobs2 Query].Job) LEFT JOIN [SawOP1 Query] ON [Active Jobs Query].Job = [SawOP1 Query].Job) LEFT JOIN [Dispatch PO5 Query] ON [Active Jobs Query].Job = [Dispatch PO5 Query].Job) LEFT JOIN [Dispatch Priority4 Query] ON [Active Jobs Query].Job = [Dispatch Priority4 Query].Job
WHERE (((Left([work_center],3))="mil" Or (Left([work_center],3))="lat" Or (Left([work_center],3))="saw") AND ((dbo_Job_Operation.Status)<>"C") AND ((dbo_Job_Operation.Sched_Start)<=[cutoff date]))
ORDER BY [Active Jobs Query].Job, dbo_Job_Operation.Sequence;