Parts are duplicated when running a BAQ listing parts and their standard cost info, etc. I originally did not have JobNum as a field and added it to see that the JobOpDtl data (ResourceID, ProdStandard, and ProdCrewSize) might be based off the JobNum since the JobNum is different, but when I deleted all four fields (JobNum, ResourceID, ProdStandard, and ProdCrewSize) the parts were still duplicating…
Any ideas how to eliminate all the duplicates?
You have job number on there you are getting one row per job
Deleting the field does nothing the join to job is bringing one row per job
I need more information/help. Are you saying to delete the join below? If so, then how will I get the following fields of data since they come from the table you say to unjoin?
ResourceID, ProdStandard, and ProdCrewSize
If you need job data you are going to have an entry per job oper detail
If you want to get part cost, delete everything related to transaction. Mean delete table JobHead/ JobOpDtl.
So, my question is how to get those 3 fields along with part cost detail? Are the 3 fields (ResourceID, ProdStandard, and ProdCrewSize) that are within the JobOpDtl table also somewhere else? Meaning, what other table can I link to this part info where it won’t repeat the parts since those 3 fields (ResourceID, ProdStandard, and ProdCrewSize) do not change at all, not with a different job number nor anything else.
Try PartOpDtl. The is where the revision details are stored.
Well, there’s always “Distinct.” It’s a crutch, but I’ve certainly used it.
If you remove the “extra” fields from the Display Fields and set the “SubQuery Options” to Distinct, you may get what you want:
Unfortunately, replacing JobOpDtl with PartOpDtl gave even more rows/duplicates. And, PartOpDtl does not have the field ProdStandard either. I tried the table PartOpr as well and it also gave more rows/duplicates, yet it had ProdStandard but then was missing ResourceID.
Maybe I’m joining incorrectly and/or have the Table Relations not set up correctly?
And now I removed all joined tables and kept only the “part” type tables and it timed out, probably because of so many rows. So even the part tables are having it duplicate rows. What am I doing wrong? I just want a list of our parts and the particular fields of the part’s data!?
Dropping in a table with no joins is called a cross join. And that will multiply your records, since if there is no criteria then everything matches.
So if table 1 has 20 rows and table 2 has 10 rows, then you will get 200 results.
If you add another table with 30 rows, you take the 200 x 30 = 5000 rows.
Your tables have a lot more rows than that, and you have more tables than that.
With very rare exceptions, you NEED to join your tables.
Whatever you have going on here… it just ain’t right.
The problem is (I think) you’re WANTING one result (row) per part… and want those results to include Resource ID, ProdStandard, and ProdCrewSize.
The problem is those (3) fields are included for each operation on the METHOD to make each part. Each operation has its own scheduled ResourceID, each has its own ProdStandard, each has its own ProdCrewSize.
This is what @josecgomez was getting to early on. Just looking at PARTS, is a one-row per part query. But as soon as you add tables like JobHead, or JobOprDtl, or PartOpr… its now a one-to-many query. Each PART could have multiple job records (resulting in more rows), each PART could have more than one operation in its method (resulting in more rows), etc.
So, for example, if you have (10) operations to make part XYZ… you’re going to end up with (10) rows of results… one for each operation.
I’M MAKING AN ASSUMPTION… that you might be looking for the TOTAL (sum) of the ProdStandard??? How many total hours to make each part??? In that case, you could sum the ProdStandard values.
You could also “average” the ProdCrewSize to get… relatively close to a true answer. it would average the crew size for each operation and give you that overall average, per part.
I’m not sure how you would get an aggregate of the ResourceID… I guess if you really need it, maybe you could concatenate a string of those results???
But I was able to recreate and get some aggregate values of ProdStandard & ProdCrewSize… again, going off of my ASSUMPTIONS above.
I used the PartOpr table to get ProdStandard & ProdCrewSize values:
I created (2) calculated fields to combine the ProdStandard & ProdCrewSize values:
Query Results: (NOTE… my company doesn’t use Std. Costs, nor add Unit Price values on our parts… so that’s why I’m not getting any results there). But I did get (1) Row per part number:
Yes, you are exactly right…I want ONE part per row with all that particular part’s details (fields).
As I mentioned, I took away all the “job” info, but I was still getting tons of rows (which is what @Banderson was stating), so you can obviously see I don’t know how to properly set up this BAQ (or “join” as he mentioned) to get ONE part per row.
It would be great to have the production details per part I mentioned (ResourceID, ProdStandard, and ProdCrewSize) since these details do not change by job…I just want what production standard we stated for the part, what crew size for the part, etc.
I am going to try what you mentioned. Fingers crossed.
Gotcha.
Just remember, fields like ResourceID, ProdStandard, ProdCrewSize, etc. are not per PART… they’re per OPERATION.
I don’t know how your methods are set-up as far as operations, resources, etc. Possible you may only have (1) operation “Extrusion”… with (1) ResourceID “Color”, (1) ProdStandard, and (1) ProdCrewSize. So… using PartOpr table may work.
But if you have more than one operation on the method, you’ll get multiple rows.
If there are multiple operations… Extruder, Pelletize, Dry, Blend, Packaging, etc… you could limit your query via Table Criteria on PartOpr where Field “OpCode” = “Extrusion”.
This would enable you to only get the ProdStandard, CrewSize, & ResourceID of that “Extrusion” operation… per part.
Basic BAQ building flow.
If you don’t understand why they are duplicating, don’t just give up and use the “Distinct” option. That’s setting you up for more issues later. You should know exactly why you need that before you use it.
This is what I did and it’s still duplicating. I guess I may not understand join and/or the Table Criteria…does it look right to you?
You can’t leave those unconnected tables in there. (Job head and job op detail). Just disconnecting the joins doesn’t remove them from the query, it creates a cross join. You have to delete them from the canvas.
Read this article. It should help you understand better what you are doing.
Thank you! 
