• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 145
  • Last Modified:

SQL Statment Help - Receiving Duplicate Data in a Report

I am very very green at this so the easier the better. I am trying to create a Parts Receipt History Report  using the datagridview in visual studio 2005 (vb.net). The user will select critera in combo boxes (receipt date, po number, etc) that will fill in the datagriview. The problem I am having is when I run my query I am receiving duplicate data. The issue seems to be comming for the field RCP.RCP_VendorPackslipID . When I remove the VendorPackSlipID the duplicates go away. The problem is that I need to show that in the report. I will paste my code below. Can someone help me modify this to achieve the results I need which is no duplicate data showing vendorpackingslip. I included the where clause just in case it needs to be seen. Again please remember I am a novice at best trying to learn. I have also attached a screen shot to show the duplicate data.
SELECT 
VEN.VEN_POName AS PO_Name, POI.POI_ItemID AS Item_ID, POI.POI_ItemName AS Item_Name, POD.POD_PurchOrderID AS Purchase_Order_ID, POD.POD_RequiredDate AS Required_Date, POD.POD_PromiseDate AS Promise_Date, POD.POD_RequiredQty AS Required_Quantity, POR.POR_ReceiverDate AS Receiver_Date, POR.POR_ReceiptQty AS Received_Quantity, RCP.RCP_VendorPackslipID AS Vendor_Packslip_ID, POD.POD_CompleteFlag AS Complete_Flag, POR.POR_OnTimeStatus AS On_Time_Status, POD.POD_POUnitPrice AS PO_Unit_Price, POR.POR_DeliveryStatusDays AS Delivery_Status_Days
 FROM 
((((VEN INNER JOIN POM ON (VEN.VEN_VendorID = POM.POM_VendorID))  INNER JOIN POI ON (POM.POM_PurchOrderID = POI.POI_PurchOrderID))  INNER JOIN POD ON (POI.POI_PurchOrderID = POD.POD_PurchOrderID AND POI.POI_POLineNbr = POD.POD_POLineNbr))  LEFT OUTER JOIN POR ON (POD.POD_PurchOrderID = POR.POR_PurchOrderID AND POD.POD_POLineNbr = POR.POR_POLineNbr AND POD.POD_RequiredDate = POR.POR_RequiredDate))  INNER JOIN RCP ON (POR.POR_PurchOrderID = RCP.RCP_PurchOrderID AND POR.POR_POLineNbr = RCP.RCP_POLineNbr AND POR.POR_RequiredDate = RCP.RCP_RequiredDate AND POR.POR_ReceiverDate = RCP.RCP_ReceiverDate)
 WHERE  
(
  (VEN.VEN_VendorID = '') AND 
  (POI.POI_ItemID = '') AND 
  (VEN.VEN_POName = '') AND 
  (POD.POD_CompleteFlag = 0) AND 
  (RCP.RCP_VendorPackslipID = '') AND 
  (POR.POR_ReceiverDate = '2001-01-01') AND 
  (POD.POD_RequiredDate = '2001-01-01')
)

Open in new window

report.jpg
0
djp12345
Asked:
djp12345
  • 7
  • 2
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
add DISTINCT to your clause:


SELECT DISTINCT
VEN.VEN_POName AS PO_Name, POI.POI_ItemID AS Item_ID, POI.POI_ItemName AS Item_Name, POD.POD_PurchOrderID AS Purchase_Order_ID, POD.POD_RequiredDate AS Required_Date, POD.POD_PromiseDate AS Promise_Date, POD.POD_RequiredQty AS Required_Quantity, POR.POR_ReceiverDate AS Receiver_Date, POR.POR_ReceiptQty AS Received_Quantity, RCP.RCP_VendorPackslipID AS Vendor_Packslip_ID, POD.POD_CompleteFlag AS Complete_Flag, POR.POR_OnTimeStatus AS On_Time_Status, POD.POD_POUnitPrice AS PO_Unit_Price, POR.POR_DeliveryStatusDays AS Delivery_Status_Days
 FROM
((((VEN INNER JOIN POM ON (VEN.VEN_VendorID = POM.POM_VendorID))  INNER JOIN POI ON (POM.POM_PurchOrderID = POI.POI_PurchOrderID))  INNER JOIN POD ON (POI.POI_PurchOrderID = POD.POD_PurchOrderID AND POI.POI_POLineNbr = POD.POD_POLineNbr))  LEFT OUTER JOIN POR ON (POD.POD_PurchOrderID = POR.POR_PurchOrderID AND POD.POD_POLineNbr = POR.POR_POLineNbr AND POD.POD_RequiredDate = POR.POR_RequiredDate))  INNER JOIN RCP ON (POR.POR_PurchOrderID = RCP.RCP_PurchOrderID AND POR.POR_POLineNbr = RCP.RCP_POLineNbr AND POR.POR_RequiredDate = RCP.RCP_RequiredDate AND POR.POR_ReceiverDate = RCP.RCP_ReceiverDate)
 WHERE  
(
  (VEN.VEN_VendorID = '') AND
  (POI.POI_ItemID = '') AND
  (VEN.VEN_POName = '') AND
  (POD.POD_CompleteFlag = 0) AND
  (RCP.RCP_VendorPackslipID = '') AND
  (POR.POR_ReceiverDate = '2001-01-01') AND
  (POD.POD_RequiredDate = '2001-01-01')
)
 
0
 
djp12345Author Commented:
That's great. I'm wondering if I can ask another question. I currently have a combo box that filters the datagrid. Code is as follows:

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

        If ComboBox1.SelectedIndex > 0 Then


            Me.IMVBindingSource.Filter = "Item_Name = '" & ComboBox1.SelectedValue.ToString & "'"
            Me.IMVBindingSource.Sort = "item_id, vendor_name, On_Hand_Quantity"
            Me.IMVTableAdapter.Fill(Me.IERP81_TMADataSet.IMV)
        End If
    End Sub

It works great but now I want to create multiple filters along with combobox1. What I am looking to do is create two more comboxes (comobox2 and 3) and use them as to and from dates for the datagrid field Promise_Date but I don't know how to tie it in. So user can currently select a vendor but it pulls up everything associated for vendor. Now I want to still see the vendor but put to and from dates in based on the Required_Date Field. How can I do this?
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Normally it's one question per thread . . . BUT:

you need to add parameters to your sql.
declare @FD as datetime --from date
declare @TD as datetime  --to date

/*set end date time to 1 sec before midnight*/
set @TD=dateadd(second,-1,(dateadd(day,1,convert(datetime,convert(varchar,@TD,101)))))

pass them in from your asp.net  (not sure if you're using a stored proc or sql), however, in your query you would want to say where promisedate between @FD and @TD
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
djp12345Author Commented:
I want to accept and award points not close
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
user request.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
user request.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
user request.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
user request.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Sorry for the extra submits, network hiccup.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now