Comparing Rows/Columns in 2 tables

E

ECEstudent

Registered User.
Local time
Today, 13:35
Joined
Jun 12, 2013
Messages
153
Hi,

I have 2 tables and 1 query. Table dbo_RepOrderItem includes columns:

RepId
OrderNumber
Item
ShipDate
SerialCardID

Table tbl_LBP Sales Location Num includes columns:

Location ID
Rep Region Code


What I've been doing so far through my code is working through a query 'CalculateTotal' which looks up a structure number (Which I enter through an InputBox) from table dbo_RoicStructure, gets the SerialCardID from the same table and collects the associated RepId,OrderNumber,ShipDate from table dbo_RepOrderItem and checks that the OrderNumber(s) returned exist in column Location ID in table tbl_LBP Sales Location Num. It also checks that Rep Region Code is not equal to 'INT' nor 'inte'.

My code for all of this works just fine. What I am trying to do now is simplify my results. I want to only return rows that do not have the same Item, OrderNumber, and RepID.

Fore example:

OrderNumber: Item: RepID:
11 3 1
12 3 1
11 4 1
11 3 1
14 7 4
16 8 8

It would now count 5 existing RepID(s) rather than 6 because it would have deleted the duplicated row. This should only be deleted in the user's table not the actual table.

And speaking of the user's table. What I have been doing so far is only returning the results (right now it returns 6, which as I explained above is incorrect) but I also want to return a table or query that would should the user the work behind the returned number.

This is my code:
Code: 
Option Compare Database
Option Explicit
Sub SearchPartNumber_Entered()
Dim txtPartNumber As Variant
Dim rst As Recordset
Dim rstt As Recordset
Dim u As Variant
Dim i As Integer
Dim n As Integer
Dim Arr() As String
Dim Arry() As String
Dim x As Variant
Dim y As Variant
Dim varCode As Variant
Dim varCod As Variant
Dim Count As Integer
Dim Count2 As Integer
Dim VldOrdrNbrDestination As Integer
txtPartNumber = InputBox("Enter Part Number:")
If Not txtPartNumber = "" Then
        VldOrdrNbrDestination = 0
        If DCount("ChildProductNbr", "dbo_ProductStructure", "[ChildProductNbr] = '" & txtPartNumber & "'") > 0 Then
            MsgBox "Part Number Found!"
    
            Count = DCount("ChildProductNbr", "dbo_ProductStructure", "[ChildProductNbr] = '" & txtPartNumber & "'")
            MsgBox Count
            Set rst = CurrentDb.OpenRecordset( _
            "Select * from dbo_ProductStructure where ChildProductNbr= '" & txtPartNumber & "'")
    
            While rst.EOF = False
                ReDim Preserve Arr(i)
                Arr(i) = rst.Fields("ParentProductNbr")
                i = i + 1
                rst.MoveNext
            Wend
            x = Arr
    
            For Each varCode In x
                varCode = Replace(varCode, "-", "*")
                varCode = Trim(varCode) 'trim extra spaces from varCode
                MsgBox "'*" & varCode & "*'"
                u = varCode
                MsgBox "Look for structure"
                If DCount("Structure", "CalculateTotal", "[Structure] like '*" & u & "*'") > 0 Then 'If what the user inputs exists in database, enter if statement
                    Count2 = DCount("Structure", "CalculateTotal", "[Structure] like '*" & u & "*'")
                    MsgBox "Number of Structures Found = " & Count2 & ""
                    Set rstt = CurrentDb.OpenRecordset( _
                    "Select * from CalculateTotal where ([Structure] like '*" & u & "*')", dbOpenDynaset, dbSeeChanges)
                    While rstt.EOF = False
                        ReDim Preserve Arry(n)
                        Arry(n) = rstt.Fields("RepId")
                        n = n + 1
                        rstt.MoveNext
                    Wend
                    y = Arry
                    For Each varCod In y
                        If DCount("[Location ID]", "[tbl_LBP_Sales Location Num]", "[Location ID] = '" & varCod & "'") > 0 Then
    
                            If Not "[Rep Region Code]" = "INT" And Not "[Rep Region Code]" = "inte" Then
                                VldOrdrNbrDestination = VldOrdrNbrDestination + 1
                            End If
                        End If
                    Next varCod
    
                    MsgBox "Number of orders: '" & VldOrdrNbrDestination & "'"
    
                Else
    
                    MsgBox "Structure Not Found"
    
                End If
    
            Next varCode
    
        Else
    
            MsgBox "Part Number Not Found"
        End If
        
Else
        MsgBox "Enter Part Number"
End If
End Sub

Thank you in advance :)
 
Is anyone able to help me with this? I would really appreciate it. Thanks.
 
It is not clear what you are asking - if it is just to remove duplicates from appearing in a query then use SELECT DISTINCT or GROUP BY - use the former if you do not need to sum, count, etc.

With regards your second part, please can you rephrase since I do not understand
 
Thank you so much for the reply CJ!!

I will eventually need to count all of the RepID values in that column...and for the duplicate aspect. I should only delete a row if it has the same exact OrderNumber + Item + RepID as another row above it


I've checked with some of my code and there are about 502 duplicate RepID, thing is I can't just assume they have the same OrderNumber and Item. I need a way to check that AND return those updated rows/columns to the user. I don't know how to do that. any ideas?
 
I've been working on this since my previous posts, and this is what I have so far:

Code: 
Option Compare Database
Option Explicit
Sub SearchPartNumber_Entered()
Dim txtPartNumber As Variant
Dim rst As Recordset
Dim rstt As Recordset
Dim u As Variant
Dim i As Integer
Dim n As Integer
Dim Arr() As String
Dim Arry() As String
Dim x As Variant
Dim y As Variant
Dim varCode As Variant
Dim varCod As Variant
Dim Count As Integer
Dim Count2 As Integer
Dim VldOrdrNbrDestination As Integer
Dim value As Variant
Dim intUB As Integer
Dim intElem As Integer
Dim intLoop As Integer
Dim intCount As Integer
Dim varValue
Dim varLoop
Dim strResults As String
Dim s As Integer
Dim Arrn() As String
s = 0
Dim t As Variant

txtPartNumber = InputBox("Enter Part Number:")
If Not txtPartNumber = "" Then
        VldOrdrNbrDestination = 0
        If DCount("ChildProductNbr", "dbo_ProductStructure", "[ChildProductNbr] = '" & txtPartNumber & "'") > 0 Then
            MsgBox "Part Number Found!"
    
            Count = DCount("ChildProductNbr", "dbo_ProductStructure", "[ChildProductNbr] = '" & txtPartNumber & "'")
            MsgBox Count
            Set rst = CurrentDb.OpenRecordset( _
            "Select * from dbo_ProductStructure where ChildProductNbr= '" & txtPartNumber & "'")
    
            While rst.EOF = False
                ReDim Preserve Arr(i)
                Arr(i) = rst.Fields("ParentProductNbr")
                i = i + 1
                rst.MoveNext
            Wend
            x = Arr
    
            For Each varCode In x
                varCode = Replace(varCode, "-", "*")
                varCode = Trim(varCode) 'trim extra spaces from varCode
                MsgBox "'*" & varCode & "*'"
                u = varCode
                MsgBox "Look for structure"
                If DCount("Structure", "CalculateTotal", "[Structure] like '*" & u & "*'") > 0 Then 'If what the user inputs exists in database, enter if statement
                    Count2 = DCount("Structure", "CalculateTotal", "[Structure] like '*" & u & "*'")
                    MsgBox "Number of Structures Found = " & Count2 & ""
                    Set rstt = CurrentDb.OpenRecordset( _
                    "Select * from CalculateTotal where ([Structure] like '*" & u & "*')", dbOpenDynaset, dbSeeChanges)
                    While rstt.EOF = False
                        ReDim Preserve Arry(n)
                        Arry(n) = rstt.Fields("RepId")
                        n = n + 1
                        rstt.MoveNext
                    Wend
                    y = Arry
 

                
                
                intUB = UBound(Arry) 'get upper bound of the array
               
                For intElem = 0 To intUB
                
                    intCount = 0
                    varValue = Arry(intElem)
                    
                    
                    If Not IsNull(varValue) Then
                        
                        For intLoop = 1 To intUB
                            
                            varLoop = Arry(intLoop)
                            If Not IsNull(varLoop) Then
                                
                                If varLoop = varValue Then
            
                                intCount = intCount + 1
                                
                                End If
                             End If
                             
                        Next intLoop
                        If intCount > 1 Then
                            
                                ReDim Preserve Arrn(s)
                                Arrn(s) = varValue
                                s = s + 1
                            
                        End If
                    End If
                Next intElem
                t = Arrn
                
                MsgBox "Amount of Duplicate RepIDs = '" & s & "'"

                Dim rt As Recordset
                Dim p As Integer
                
                
                
                
                For Each value In t
                
                
                
                    Set rstt = CurrentDb.OpenRecordset( _
                    "Select * from CalculateTotal where (RepId = '" & t & "')", dbOpenDynaset, dbSeeChanges)
                  '  While rstt.EOF = False
                        ReDim Preserve Arre(r)
                        Arre(r) = rstt.Fields("Item")
                        r = r + 1
                        rstt.MoveNext
                   ' Wend
                    o = Arre
                
                
                 '   Set rt = CurrentDb.OpenRecordset( _
                 '   "Select * from CalculateTotal where (RepId = '" & t & "'")
                    p = 0
                    Dim Arrr() As String
                    
                    
                    intUB = UBound(Arre) 'get upper bound of the array
                   
    
                    For intElem = 0 To intUB
                    
                        intCount = 0
                        varValue = Arre(intElem)
                        
                        
                        If Not IsNull(varValue) Then
                            
                            For intLoop = 1 To intUB
                                
                                varLoop = Arre(intLoop)
    
                                If Not IsNull(varLoop) Then
                                    
                                    If varLoop = varValue Then
                
                                    intCount = intCount + 1
                                    
                                    End If
    
                                 End If
                                 
                            Next intLoop
    
                            If intCount > 1 Then
                                
    
                                    ReDim Preserve Arrr(p)
                                    Arrr(p) = varValue
                                    p = p + 1
                                
                            End If
    
                        End If
    
                    Next intElem
    
    
                    MsgBox "Amount of Duplicate Items = '" & p & "'"
                
                
                Next value
                

    '                For Each varCod In y
            '           If DCount("[Location ID]", "[tbl_LBP_Sales Location Num]", "[Location ID] = '" & varCod & "'") > 0 Then
            
            '               If Not "[Rep Region Code]" = "INT" And Not "[Rep Region Code]" = "inte" Then
        
            '                   VldOrdrNbrDestination = VldOrdrNbrDestination + 1
        
            '                End If
        
            '           End If
        
            '        Next varCod
            
            '               MsgBox "Ultimate Destination Number: '" & VldOrdrNbrDestination & "'"
            
        Else
            
            MsgBox "Structure Not Found"
            
        End If
    
        Next varCode
    
    Else
    
        MsgBox "Part Number Not Found"
    End If
        
Else
        MsgBox "Enter Part Number"
End If
End Sub
 
The code is really long at this point and the only reason for that is because of my attempt at finding duplicates (OrderNumber + Item + RepID rows)...
 
to return only one unique row, just use a group by query

select RO.OrderNumber, RO.Item, RO.RepID
from dbo_RepOrderItem RO
group by RO.OrderNumber, RO.Item, RO.RepID

to return only rows that are not duplicated

select RO.OrderNumber, RO.Item, RO.RepID, count(*) as theCount
from dbo_RepOrderItem RO
group by RO.OrderNumber, RO.Item, RO.RepID
having count(RO.OrderNumber)=1
David
 
aren't both codes doing the same thing? returning unique, non-duplicate rows?

Count is counting the number of rows that are unique? What's the last line of code doing? havingcount(RO.OrderNumber)=1


Thanks a lot David :)
 
Also, how can I view the updated table? The one that is updated after the duplicates are deleted?

Would that table be RepOrderItem? Is there a way to make it visible to the user after the code has gone through and the total calculation has been shown?
 
The SQL I posted will return different number of rows, the first will return a set of distinct values for ALL records, the second which by the way has lost some spaces, should be a space between group by, also
having count(RO.OrderNumber)=1
this will return only rows that have NO duplicates , so in the 6 rows you posted earlier, this sql will return only 4 rows, ignoring the 11 3 1's

This sql will only select rows, to delete duplicate rows is more complex

David


 
Some rows have duplicates. some don't. I want to return both but without the duplicates

for Example:
11,2,5
11,4,7
12,3,9
12,6,8
12,3,9


Should return: 4 not 5 nor 3.

Would the code below accomplish this?

select RO.OrderNumber, RO.Item, RO.RepID
from dbo_RepOrderItem RO
groupby RO.OrderNumber, RO.Item, RO.RepID
 
You must log in or register to reply here.

Similar threads

E
Putting values into temporary table
Replies
13
Views
2,969
Galaxiom
G
E
If statement Correction
2
Replies
20
Views
1,576
ECEstudent
E
E
Counting through vba
Replies
6
Views
787
ECEstudent
E
E
SQL/VBA Code
Replies
17
Views
1,828
ECEstudent
E
E
Temporary Table View
Replies
11
Views
939
ECEstudent
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom