djp12345
asked on
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')
)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,(dat
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
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,(dat
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
ASKER
I want to accept and award points not close
user request.
user request.
user request.
user request.
Sorry for the extra submits, network hiccup.
ASKER
Private Sub ComboBox1_SelectedIndexCha
If ComboBox1.SelectedIndex > 0 Then
Me.IMVBindingSource.Filter
Me.IMVBindingSource.Sort = "item_id, vendor_name, On_Hand_Quantity"
Me.IMVTableAdapter.Fill(Me
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?