Are you getting multiple rows per part? Or just more parts than you figured you would receive in your result set?
Are you putting a criteria on PartPlant.Plant? Otherwise you’d get a row for each plant where the part meets the criteria (but perhaps that’s the goal).
Could be your joins. Can you post up a pick of the query? Need more to go on.
Yes, I am getting multiple rows per part. I am not wanting to see multiple rows per part, just the current run rates for each part (active rev) in each plant.
Seeing as you’re using Part Plant, that can be a many-to-one, right? The Part could exist in multiple plants, so you’re going to get multiple rows. Do you have multiple sites?
Same with Part Rev. A Part could have multiple revisions, so you would again have multiple rows.
But I expect the issue may be the fact that you’re using ECO tables. There are records for every ECO group that has ever touched this part. In your ECOOpr table, add the GroupID as a display table. Do you see multiple results? If so, you may want to use a subquery on ECOGroup with a calculated field for max(CloseDate) or something along those lines. I would expect that to be the group that made the most recent change to the method. But some companies leave groups open for long periods… so this could be an issue. But then you could join that subquery to ECOOpr so only results for the ECOGroup that had the latest CloseDate would be displayed.
My thoughts at this point anyway.
Edit: See @jkane MUCH better solution to that portion
Thanks @jkane, I almost tagged you on that post because I assumed there was a better table option but couldn’t think of it. My head got stuck in ECO world.
I say this every time I see this. ECO tables are working tables. Not history tables, and not master tables. Treat them like temp tables and don’t ever use them unless you are specifically looking at things that are currently checked out in engineering workbench. I wish Epicor would just clear them out when parts get checked in because it’s super easy to get sucked into thinking that these are reliable tables to use.