I am attempting to create a BAQ for one of our employees. I have successfully created it and it displays the manufacturer and supplier part numbers for any given company part number. It also doesn’t include any parts that don’t have at least one or the other. My problem is that we only want one supplier part to show up per company part number. Does anyone know how to limit my results to show only ONE supplier and supplier part number per company part? Here is my query phrase.
select
[Part1].[Part_PartNum] as [Part_PartNum],
[Manufacturer1].[Manufacturer_Name] as [Manufacturer_Name],
[Manufacturer1].[PartXRefMfg_MfgPartNum] as [PartXRefMfg_MfgPartNum],
[Vendor1].[Vendor_Name] as [Vendor_Name],
[Vendor1].[PartXRefVend_VendPartNum] as [PartXRefVend_VendPartNum]
from (select
[Part].[Company] as [Part_Company],
[Part].[PartNum] as [Part_PartNum]
from Erp.Part as Part) as Part1
left outer join (select
[Vendor].[Company] as [Vendor_Company],
[Vendor].[Name] as [Vendor_Name],
[VendPart].[VenPartNum] as [VendPart_VenPartNum],
[PartXRefVend].[VendPartNum] as [PartXRefVend_VendPartNum],
[PartXRefVend].[PartNum] as [PartXRefVend_PartNum],
[PartXRefVend].[Company] as [PartXRefVend_Company],
[PartXRefVend].[Character01] as [PartXRefVend_Character01]
from Erp.Vendor as Vendor
inner join Erp.VendPart as VendPart on
Vendor.Company = VendPart.Company
and Vendor.VendorNum = VendPart.VendorNum
inner join Erp.PartXRefVend as PartXRefVend on
VendPart.Company = PartXRefVend.Company
and VendPart.PartNum = PartXRefVend.PartNum
where not PartXRefVend.Character01 = ‘second’) as Vendor1 on
Part1.Part_Company = Vendor1.PartXRefVend_Company
and Part1.Part_PartNum = Vendor1.PartXRefVend_PartNum
left outer join (select
[PartXRefMfg].[PartNum] as [PartXRefMfg_PartNum],
[PartXRefMfg].[MfgNum] as [PartXRefMfg_MfgNum],
[PartXRefMfg].[MfgPartNum] as [PartXRefMfg_MfgPartNum],
[Manufacturer].[Name] as [Manufacturer_Name],
[AprvMfg].[Company] as [AprvMfg_Company],
[AprvMfg].[PartNum] as [AprvMfg_PartNum]
from Erp.AprvMfg as AprvMfg
inner join Erp.PartXRefMfg as PartXRefMfg on
PartXRefMfg.Company = AprvMfg.Company
and PartXRefMfg.PartNum = AprvMfg.PartNum
and PartXRefMfg.MfgNum = AprvMfg.MfgNum
inner join Erp.Manufacturer as Manufacturer on
Manufacturer.Company = AprvMfg.Company
and Manufacturer.MfgNum = AprvMfg.MfgNum) as Manufacturer1 on
Part1.Part_Company = Manufacturer1.AprvMfg_Company
and Part1.Part_PartNum = Manufacturer1.AprvMfg_PartNum
where Vendor1.Vendor_Name is not null or Manufacturer1.Manufacturer_Name is not null
Just grabbing the top or first record would be fine. I’m not sure in the first link that there are instructions on how to just grab the top? I just see Jose saying you can tell it to just pull the Top(1).
It seems I couldn’t successfully complete the second link’s instructions for limiting the data pulled in. When I was linking the vendor database in the sub-query criteria, there was some kind of error.
Look at the second link. Set up your sub quer(ies) with the results set like below. Then instead of joining it like a table, make the joins in the SubQuery Criteria. Then make a calculated field with the results of the sub query. You will have to make a sub query per item you want to show (one for vendor, one for vendor part number) because it can only return one column per sub query, but it will get you just the first record.
This makes sense, but whenever I set it up like this, the subquery criteria is not linking properly. It gives me the error saying that it can’t be bound.
You don’t need the vendor in the top level and in the subquery. The joins that you are trying to make in the screen shot above is replacing the joins that you have set up in the top level. Your are having problems because you are just joining a duplicate table to itself. (you shouldn’t have vendor = vendor anywhere.) Your joins should be vendor to part table, and your top level should only have the part table, and the subqueries should have the vendor and manufacturer table.
Also, you only have one field in the display fields of the TopVendor query right?
Yes, I only have the one display field. I will have to duplicate this query and mess around with it. Thanks for your help with this! Hopefully I can get it working by tomorrow.
