Findout Unmatched rows without unique value

[punna111]

Hi All,

I have Table1 as below:

Inv_No. Amt
555 100
555 50
555 200
555 50
555 300
555 50
555 50


and Below is the Table 2

Inv_No. Amt
555 50
555 200
555 50

I would like to find out Unmatched rows, as like below:

Inv_No. Amt
555 100
555 300
555 50
555 50

Can any help me to get the above unmatched rows
Thanks in advance

 

[arnelgp]

return records from table1 without matching record in table2:

select * from table1 Left join table2 on table1.[Inv_No.]=table2.[Inv_No.] And table1.[Amt] = table2.[Amt] WHERE ((table2.[Inv_No.]) Is Null);

return records from table2 without matching record in table1:

select * from table2 Left join table1 on table2.[Inv_No.]=table1.[Inv_No.] And table2.[Amt] = table1.[Amt] WHERE ((table1.[Inv_No.]) Is Null);

 

[punna111]

Hi arnelgp,

Thanks for reply,

The result i got with 1 st query is below

Table1.Inv_No Table1.Amt Table2.Inv_No Table2.Amt
555 100
555 300


and 2nd query result is blank as below (no records are there)

Table2.Inv_No Table2.Amt Table1.Inv_No Table1.Amt

any more help, please..

 

[plog]

You can't do what you want with the data you gave--its not unique enough. If you had 100 rows of 555-50 data in table1 and just 1 row of 555-50 data in table2, all 100 rows in table1 would find a match.

What you can do is sum up your records in each table and then do subtraction to find the difference in unique records. Thats not going to give you the results like you requested though, it would return this:


Inv_no, Amt, Unmatched
555, 50, 99

 

[arnelgp]

yes, i have it wrong. we need a VBA and a temp table for that.

 

[punna111]

Thanks for the reply again.

any other way or technique for this??

 

[arnelgp]

just in case your interested, heres the VBA, create a table tblNoMatch with same structure as table1. all unmatched records will be save to this table.
not tested.

Public Sub subCreateRowDiff()

    Dim strSQL As String
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim db As DAO.Database
    Dim lngCount As Long
    
    Const TEMP_TABLE As String = "tblNoMatch"
    
    strSQL = "SELECT table1.[Inv_No], table1.[Amt], Count(table1.Inv_No]) As Expr1 GROUP BY table1.[Inv_No], table1.[Amt];"
    Set db = CurrentDb
    
    'remove all records
    db.Execute "DELETE " & TEMP_TABLE & ".* FROM " & TEMP_TABLE & ";"
    'open table1
    Set rs1 = db.OpenRecordset(strSQL)
    strSQL = Replace(strSQL, "table1", "table2")
    'open table2
    Set rs2 = db.OpenRecordset(strSQL)
    'check for difference
    With rs1
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            lngCount = .Fields(2).Value 'the count field
            'find matching record in table2
            rs2.FindFirst "[Inv_No] = " & .Fields(0) & " AND [Amt] = " & .Fields(1).Value
            If rs2.NoMatch Then
                'save this record as many times (lngCount)
                While lngCount <> 0
                    db.Execute "Insert Into " & TEMP_TABLE & "(Inv_No, Amt) " & _
                                "Values(" & .Fields(0).Value & ", " & .Fields(1) & ");"
                    lngCount = lngCount - 1
                Wend
            Else
                ' there is a match
                ' check the difference
                If .Fields(2).Value > rs2.Fields(2).Value Then
                    lngCount = .Fields(2).Value - rs2.Fields(2).Value
                Else
                    lngCount = rs2.Fields(2).Value - .Fields(2).Value
                End If
                While lngCount <> 0
                    db.Execute "Insert Into " & TEMP_TABLE & "(Inv_No, Amt) " & _
                                "Values(" & .Fields(0).Value & ", " & .Fields(1) & ");"
                    lngCount = lngCount - 1
                Wend
            End If
            .MoveNext
        Wend
    End With
    
    ' now use table2 as reference
    With rs2
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            lngCount = .Fields(2).Value 'the count field
            'find matching record in table1
            rs1.FindFirst "[Inv_No] = " & .Fields(0) & " AND [Amt] = " & .Fields(1).Value
            If rs1.NoMatch Then
                'save this record as many times (lngCount)
                While lngCount <> 0
                    db.Execute "Insert Into " & TEMP_TABLE & "(Inv_No, Amt) " & _
                                "Values(" & .Fields(0).Value & ", " & .Fields(1) & ");"
                    lngCount = lngCount - 1
                Wend
            Else
                ' we already did this before
                ' so no need
            End If
            .MoveNext
        Wend
    End With
    rs1.Close: Set rs1 = Nothing
    rs2.Close: Set rs2 = Nothing
    Set db = Nothing
End Sub