select
[SubShipD].[PONum] as [SubShipD_PONum],
[SubShipD].[PartNum] as [SubShipD_PartNum],
[SubShipD].[POLine] as [SubShipD_POLine],
[SubShipD].[PORelNum] as [SubShipD_PORelNum],
[JobOper].[ActBurCost] as [JobOper_ActBurCost],
[JobOper].[ActLabCost] as [JobOper_ActLabCost],
[JobOper].[DocUnitPrice] as [JobOper_DocUnitPrice],
[JobOper].[EstUnitCost] as [JobOper_EstUnitCost],
[JobOper].[OpCode] as [JobOper_OpCode],
[JobOper].[PartNum] as [JobOper_PartNum],
[PODetail].[DocUnitCost] as [PODetail_DocUnitCost],
[Vendor].[VendorID] as [Vendor_VendorID],
[Vendor].[Name] as [Vendor_Name],
[JobOper].[OprSeq] as [JobOper_OprSeq],
[SubShipD].[OprSeq] as [SubShipD_OprSeq]
from Erp.SubShipD as SubShipD
inner join Erp.JobOper as JobOper on
SubShipD.Company = JobOper.Company
And
SubShipD.JobNum = JobOper.JobNum
And
SubShipD.AssemblySeq = JobOper.AssemblySeq
And
SubShipD.PartNum = JobOper.PartNum
And
SubShipD.OprSeq = JobOper.OprSeq
left outer join Erp.PORel as PORel on
SubShipD.Company = PORel.Company
And
SubShipD.PONum = PORel.PONum
And
SubShipD.POLine = PORel.POLine
And
SubShipD.PORelNum = PORel.PORelNum
left outer join Erp.PODetail as PODetail on
PODetail.Company = PORel.Company
And
PODetail.PONUM = PORel.PONum
And
PODetail.POLine = PORel.POLine
left outer join Erp.Vendor as Vendor on
PODetail.Company = Vendor.Company
And
PODetail.VendorNum = Vendor.VendorNum
I would remove the distinct on the BAQ as well until the end. We want to see why there are duplicates.
Once you have added the table you will get “duplicates” due to the nature of the tables. You will need to add another join from the SubShipD to QuoteOpr on PartNum and Company.
If there are still duplicates show the QuoteOpr OprSeq in the query are the multiple OprSeq per quote on parts that are being contracted out?
QuoteOpr also has a SubContract flag, is that yes?
The newly added tables are connected themselves but the quote information cannot be shown…
I checked the links in between the tables, they are all good…
yes, there is a subcontract flag in QuoteOpr table.
Also, I would need the quote date from QuoteHead table.Maybe it can be added in between jobhead and quotedtl??
We can tackle that in a bit. The JobHead has the QuoteNum and QuoteLine fields. If it is blank then we are going to have a hard time making the jump to the rest of the query.
I have a Skype account. let me get something set up.
I have had an assembled BAQ made, i mean the quote part and PO part, but the problems are duplicating, wrong matches… please see the attachment
i know it would be so much more efficient by talking instead of typing, it is not so convenient for me to use either conference meeting or video call in the big office…
We don’t use the quote table, so it is hard for me to troubleshoot the query past the joboper table.
That is why I am asking to do a call, as would be a lot quicker than us going back and forth with me being blind to the data, and you trying something that isn’t working. I am guessing 15 minutes or so.
What you have just sent is not what we were talking through, more of combining the two initial baqs, but with a few changes. That will cause duplicates on the join of the inner queries, which you already are seeing. You need to know the JobNum, JobOpr, AssemblySeq associate to the PODetail. Otherwise how do you know which PONum, Part is the correct one? That is what the role of the SubShipD, it helps to join those tables.
I know I would like to do call; could you do a at 5:00 PM? have to take your off-work time.
My cell phone# is 216-318-7519 as our office phone will be turned off after 5.
I have working on the BAQ since yesterday. And I found that the RFQ tables are not a good option as expect RFQItem table, the others actually do not store real information in it or it cannot be put with Quote Tables. i know this sounds weird.
Yesterday, I found that the RCVDTIL table has the POnum and unit cost. The only it has not worked out is the duplicating issue. could you please take a look at this?
I find the only reason that the PO#s are duplicating maybe because there is no table which contain both quote# and PO# . If so, the PO#s could be eliminated.
