BAQ Assistance Requested - I don't know how Joins work

SO I’m trying to develop a dashboard that shows
Part:Description:Primary Vendor:Supplier Part Number:Last Cost:Avg Cost:UOMs:Calculated field (Avg vs last, display higest)

I THINK i have the query sort of working, but I have somewhere between 14 and 55 duplications for every part in my database, so even running a 50 part test takes 1-2 minutes.

I’m thinking it’s a join, but It could be something else, any guidance would be appreciated

Not sure if that image upload will work, but also

[PartPlant].[PartNum] as [PartPlant_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
when PartCost.AvgMaterialCost > PartCost.LastMaterialCost then PartCost.AvgMaterialCost
ELSE PartCost.LastMaterialCost
end) as [Calculated_HighMatCost],
[Vendor].[VendorID] as [Vendor_VendorID],
[Vendor].[Name] as [Vendor_Name],
[VendPart].[VenPartNum] as [VendPart_VenPartNum],
[PartCost].[LastMaterialCost] as [PartCost_LastMaterialCost],
[PartCost].[AvgMaterialCost] as [PartCost_AvgMaterialCost],
[Part].[PUM] as [Part_PUM],
[Part].[SalesUM] as [Part_SalesUM],
[Part].[IUM] as [Part_IUM]
from Erp.PartPlant as PartPlant
inner join Erp.Vendor as Vendor on
PartPlant.Company = Vendor.Company
and PartPlant.VendorNum = Vendor.VendorNum
inner join Erp.VendPart as VendPart on
VendPart.Company = Vendor.Company
and VendPart.VendorNum = Vendor.VendorNum
inner join Erp.PartCost as PartCost on
PartCost.Company = VendPart.Company
and PartCost.PartNum = VendPart.PartNum
and PartCost.LastMaterialCost = VendPart.BaseUnitPrice
inner join Erp.Part as Part on
VendPart.Company = Part.Company
and VendPart.PartNum = Part.PartNum
where (PartPlant.Plant = 'MfgSys')
order by PartPlant.PartNum

Here is a start to how to figure it out.

Start a new BAQ, and just link two tables. See if you have duplicates with the fields you have.

If not then start adding them one at a time, and keep checking.

At whatever point you find duplicates, you will know where they are coming from, and you can see if you need to adjust your joins, or criteria to narrow it down, etc.


What Kevin says. And I think you’ll see duplicates on your first link. I might move the link from PartPlant to Vendor to Part and try again.


here’s a good link that I’ve used to help explain to people what joins are and the different types. BAQ is just SQl with a fancy GUI. You can change the type in the bottom right corner just above you criteria tables.

Edit: This was the one I used before. Both are fine, but this one has pictures, which I think help.


Udemy has good stuff, cheap investment


Thank you for the instructional materials in re Joins.

If I use just part plant, with part # displayed, no duplicates
I add Vendor, and display supplier ID, no duplicates
I display vendor name, no duplicates
I add Table VendPart, no display fields, duplicates
I display VenPartNum, still duplicates

In this scenario I tried all 16 potential relationships, none of them changed the level of duplication

I remove part # display field from Part Plant, no duplicates

Add back part cost, no duplicates

I’m guessing those PartNum are the same, they seem to be, and by using the VendPart_PartNum it resolved the duplication shurg

Thanks for all your help, guess it wasn’t the joins after all (unless there’s a way to do what I did with the display fields with the joins, then I’ll blame the joins again)

OK, there are couple of tables that have special characteristics too.

PartCost can have multiple records per part. Find the CostID for your Plant (ours is ‘1’). The link will then look like:

VendPart has Effectivity Logic. So, there can be (and probably are) more than one per part. This will definitely give you duplicates. Add the EffectiveDate to your display and you’ll see what I mean.

1 Like

So, to get the most current VendPart takes a subquery and a trick. This is baptism by fire my friend.

Create a new innersubquery and drag VendPart to the screen. Create a criteria to eliminate any future EffectiveDates.

In your Display Fields, add Company, PartNum, EffectiveDate, and VendorNum.

Create a new calculated field called RowNum:

What this does is assigns the ROW_NUMBER to each record within the part in descending order (most recent to oldest). I did this is in a new query for demonstration purposes, and this only has the VendPart subquery in it. Notice what we now get:


We still have all the duplicates but now the most recent record is marked as 1 and each older record counts up. Armed with this subquery, we can filter everything else out except the 1s.

In the main query, select out only the 1s.

Now when we run this, we get:


…only one record per part - the most recent VendPart record per part. Use this subquery in place of the VendPart table in your query, and things will look a whole lot better.

The moral of the story is to pick easier BAQs to start with! :wink:


Beautiful. Thank you so much. Banging my head on this one.