We have employees selecting what serial number they completed for an operation.
What / where is the link between the Job Number, Asm Seq, Opr Seq. Employee and serial number.
I have been asked by QA to provide them with the employee that completed an operation on a serial tracked part, and I cannot find the link.
The two major tables containing the data you want are the SNTran table and the LaborDtl table. If you are recording serial numbers at each operation, the SNTran table will have the JobNum, AssemblySeq, OprSeq, and serial number entries, and the LaborDtl table will have JobNum, AssemnblySeq, OprSeq, and EmployeeID. Creating a query that joins those two tables on JobNum, AssemblySeq, and OprSeq should give you the information you are looking for.
If the above paragraph just looks like jibberish to you, we can help further as well.
That is exactly what I thought.
We are reporting / recording serial numbers at quantity completion of each operation.
I have looked at the SNTRAn table for a job that is in process now, and all of the Opr seq number are 0.
I have joined the two tables and not getting the data.
My bad… instead of OprSeq in the SNTran table, look at LastLbrOprSeq.
Probably best to just look at the SNTran table and look for all records for a particular job… see which fields have data, and then try to figure out what the data is and how you can use it. That’s how I explore questions like this one. I went into the Demo database and just dumped the entire SNTran table to Excel from a BAQ… looked at the field descriptions in the BAQ program and found there is a “TranType” field with a value of “OPR-CMP” (that looks suspiciously like “operation completion”), and then looking back at the job and LastLbrOprSeq value, looked up the job and blah blah blah you get the idea.
Yes, I get it, so I am getting the data from SNtrans that looks right, and I am getting 533 records, which does match up with the quantity completions for the operations on the job.
When I join to laborDtl, the number of records grow, I then joined SNtransdate to Labordtl clock in date.
Number of records still grows but not at much.
I need to find one more “maybe just one more” common connecting field.
But you have gotten me very close.
If I do figure it out I will post it.
Thanks
Got it
select
[SNTran].[JobNum] as [SNTran_JobNum],
[SNTran].[AssemblySeq] as [SNTran_AssemblySeq],
[SNTran].[OprSeq] as [SNTran_OprSeq],
[SNTran].[LastLbrOprSeq] as [SNTran_LastLbrOprSeq],
[SNTran].[SerialNumber] as [SNTran_SerialNumber],
[SNTran].[TranType] as [SNTran_TranType],
[SNTran].[TranDate] as [SNTran_TranDate],
[SNTran].[ScrapLaborDtlSeq] as [SNTran_ScrapLaborDtlSeq],
[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
[EmpBasic].[Name] as [EmpBasic_Name]
from Erp.SNTran as SNTran
left outer join Erp.LaborDtl as LaborDtl on
SNTran.Company = LaborDtl.Company
and SNTran.JobNum = LaborDtl.JobNum
and SNTran.AssemblySeq = LaborDtl.AssemblySeq
and SNTran.LastLbrOprSeq = LaborDtl.OprSeq
and SNTran.ScrapLaborDtlSeq = LaborDtl.LaborDtlSeq
inner join Erp.EmpBasic as EmpBasic on
LaborDtl.Company = EmpBasic.Company
and LaborDtl.EmployeeNum = EmpBasic.EmpID
where (SNTran.JobNum = ‘83027’ and SNTran.TranType = ‘OPR-CMP’)
Great job!