I’m having problem with creating a BAQ and getting back the results I want.
What I’m trying to do is create a dashboard that will show which employees worked on a serial number for each specific part.
everything works fine when I set up the labordtl with the JobNum, PartNum, ClockInDate, ClockInTime and ClockOutTime, but as soon as I add int he SNTran table the results are incorrect, it shows one entry that was for one part under multiple parts.
Has anyone done this before or can help me get the results I want?
I’m not super familiar with the sntran table, but does that table have an op sequence number in it? You have the job opening table joined with the SN table , but there are going to me multiple ops and I think that’s why you are getting multiple rows.
If there is not an op sequence, I’m going to assume that the serial number is applied at the last operation. If you only want that operation, then you will need to make a subquery and do a calculated field with Max(opseq) and then group by job number and part number, the join to that sub query.
If you want everyone who worked on that part, then add the labor detail table in there but only show the employee ID and group by that. You can remove the op sequence unless you want to see that. That will give you a row for every serial number and person that worked on the serial number.
I hope that makes sense, I’m not at a computer right now .
Also what was the labor qty on that transaction? If he made more than one, he’s going to have more than one serial number.
@Banderson, looking at the table now. I was wondering what is missing from the join and thinking the same as you. there are assembly levels and opseq to link the tables.
Is that same part number used multiple times in the job? Add the assembly sequence to the join on the serial table and the job opr table. (I’m looking at your first query if that helps). Also, you should remove that distinct from the subquery settings. If you are getting duplicate rows, you’ll want to know that and fix your joins.
What version is your database? OprSeq is in the SNTran table at 10.2.300 and I think it has been in for a while. On the Job Setup when is the serial number tracked? Is the serial number tracked at each operation that is completed, or only at the end of the job? There is a Serial Number checkbox on the operation configuration in the BOO. What @Banderson is suggesting in written format sounds like it should work? The other option is to reverse your anchor table (first table). Since you are wanting who worked on each serial number, not what employees worked on what you could start with SNTran. Query the table and group like has been suggested, by Serial Number, TranType, PartNum, JobNum, AssemblySeq, OprSeq. Then link this to JobOper by JobNum, AssemblySeq, OprSeq. Then to LaborDtl by JobNum, AssemblySeq, OprSeq. Suggest to include company as a link field as well, but not as important if you do not have more than one company in the database. Finally, you can possibly drop out the JobOper table since LaborDtl should have everything you need to link to SNTran.
Your result set from SNTran should have every serial number with each individual operation per job and assembly sequence which was collected for the given part number. Then, you just add in the employee or employees for that transaction if multiple resources worked on the operation.
This is definitely doable. I would pick one job and one part number. Query each table for all the records pertaining to that job and part separately. Which ever query got the most records with the grouping will be your expected recordset. It is possible to have multiple labordtl records if the part is worked on multiple days as well. If this is the case you will need to group and max some fields.
Before you change anything big, just add the assembly seq to the join. It shouldn’t matter which order you have the tables (for something like this). They match or they don’t. It would just be a mental exercise that may help you understand what is going on.
