Zydeceltico
Registered User.
- Local time
- Today, 08:51
- Joined
- Dec 5, 2017
- Messages
- 843
Hi all,
I'm trying to create a report for manufacturing inspections by job number.
Our job numbers look like this: A762502001.
The first five characters (e.g., A7625) are the actual job number. The next three (e.g., 020) is what we call the Task. The last two e.g., 01) are what we call the Resource.
The query returns them as one concatenated string (i.e., A762502001).
There could be, and usually permutations of Task and Resource and they are not, rarely, consecutive in nature.
For instance the entire job A7625 might have have these subs: A762502001, A762502002, A762503007, A762550001 or many other combinations.
I want to create a report where there is combo box in the header that the user can type in just the first five characters (e.g., A7625) and return all records for all of the permutations to include all Tasks and Resources.
I suspect this would a SUBSTRING call but I'm not sure how to implement it within my SELECT statement especially since I am concatenating in the SELECT statement.
Here is my current code:
Thank you as always,
Tim
I'm trying to create a report for manufacturing inspections by job number.
Our job numbers look like this: A762502001.
The first five characters (e.g., A7625) are the actual job number. The next three (e.g., 020) is what we call the Task. The last two e.g., 01) are what we call the Resource.
The query returns them as one concatenated string (i.e., A762502001).
There could be, and usually permutations of Task and Resource and they are not, rarely, consecutive in nature.
For instance the entire job A7625 might have have these subs: A762502001, A762502002, A762503007, A762550001 or many other combinations.
I want to create a report where there is combo box in the header that the user can type in just the first five characters (e.g., A7625) and return all records for all of the permutations to include all Tasks and Resources.
I suspect this would a SUBSTRING call but I'm not sure how to implement it within my SELECT statement especially since I am concatenating in the SELECT statement.
Here is my current code:
SQL:
SELECT tblinspectionevent.inspectionevent_pk,
tbljobs.job_id,
[jobnumber] & [task] & [resource] AS Job,
tblinspectmill.amsbundlenumber,
tblfinalproducts.finalproducttype,
tblparts.parttype,
tblinspectionevent.notes,
tblinspectmill.coveragemaintained,
tblinspectmill.coveragewidth,
tblinspectmill.interlockgap1,
tblinspectmill.interlockgap2,
tblinspectmill.camberfromzero,
tblinspectmill.sweepfromzero,
tblinspectmill.lengthactual,
tblinspectmill.shapeconforms,
tblcoils.coilnumber,
tblcoils.matcert,
tblinspectionevent.oilcanning,
tblinspectionevent.coatingissues,
tblinspectionevent.issuesother
FROM (tblfinalproducts
INNER JOIN tbljobs
ON tblfinalproducts.finalproduct_id = tbljobs.finalproduct_fk)
INNER JOIN (tblinspectionevent
INNER JOIN (tblparts
INNER JOIN (tblinspectmill
INNER JOIN tblcoils
ON
tblinspectmill.coilnumber_pk =
tblcoils.coilnumber_pk)
ON tblparts.part_id =
tblinspectmill.parttype_fk)
ON tblinspectionevent.inspectionevent_pk =
tblinspectmill.inspectionevent_fk)
ON tbljobs.job_id = tblinspectionevent.job_fk
ORDER BY tblinspectionevent.inspectionevent_pk DESC;
Thank you as always,
Tim