filtering same values in one field in 2 tables

[kavithabhaskar]

Hi I have table2 and table1. I want to check if table2 values exist in table1. If they do, I want them to be displayed on datagridview in a form. The table1 and table2 have same fields except that the primary key in each table is the serial number.

Even if duplicates exist in table2, its fine.. i just want ..what's on table2, if it exists in table1, display that
the query that i used was

cmd = New OleDbCommand("SELECT * from table2 WHERE MFG.table1 = MFG.table2", con)

this does not seem to work..why is that?

[kavithabhaskar]

SELECT * from table2 where exists(select MFG from table1 where MFG.table1 = MFG.table2)

would this be correct ?

[kavithabhaskar]

This code works if what exists in table2 exists in table1 also and that's displayed in my datagridview.. but how do i do this for my other three fields ? is there way by which i can use the AND operator and do something?

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Final Database.mdb")

        con.Open()

        Dim cmd As New OleDbCommand

        cmd = New OleDbCommand("SELECT table2.GRAPHICSVERSION from table2 where exists(select * from table1 where table2.GRAPHICSVERSION=table1.GRAPHICSVERSION)", con)


        Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)

        Try
            Dim ds As DataSet = New DataSet()

            da.Fill(ds, "table2")

            DataGridView2.DataSource = ds.Tables("table2").DefaultView

        Finally

            con.Close()

            cmd = Nothing

            da.Dispose()

            con.Dispose()

        End Try

        con.Close()

        cmd = Nothing

con.Dispose()

End Sub

End Class

[kavithabhaskar]

i have both matching data and not matching data.. i want to get the matching data in one color adn the not matching data in another..can i do that here ?

can u please help me? i have been trying to find a solution for weeks now..!!

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Final Database.mdb")

con.Open()

Dim cmd As New OleDbCommand

cmd = New OleDbCommand("SELECT DISTINCT MAKE from table2 where exists(select * from table1 where table2.MAKE = table1.MAKE)union select distinct MAKE from table2 where not exists(select * from table1 where table2.MAKE = table1.MAKE)", con)

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)

Try

Dim ds As DataSet = New DataSet()

da.Fill(ds, "table2")

DataGridView2.DataSource = ds.Tables("table2").DefaultView

Finally

con.Close()

cmd = Nothing

da.Dispose()

con.Dispose()

End Try

con.Close()

cmd = Nothing

con.Dispose()

End Sub

[kavithabhaskar]

FOLKS.. i am trying to compare the field's values in 2 tables: table1 and table2. if a field's value in table2 exists in table1, i want it in green else red.

I am trying to achieve this but i get the error : "oledb exception was unhandled" and "Ierrorinfo.get description.failed with E_FAIL(0X80004005)" at the line da.fill(ds,"table2")

this error normally comes up when the fieldnames used SQL keywords but i dont think there is a keyword in my Query that I am using.. i ahev been trying to get this solved for weeks with no result..i would appreciate if you could help me with this..

thanks
Kavs

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Final Database.mdb")

        con.Open()

        Dim cmd As New OleDbCommand

        cmd = New OleDbCommand("SELECT table2.GRAPHICSVERSION, case when GRAPHICSVERSION IS NULL then 0 else 1 end as ExistsInTable1 from table2 left outer join table1 on table2.GRAPHICSVERSION=table1.GRAPHICSVERSION",con)

        Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)

        Try
            Dim ds As DataSet = New DataSet()

            da.Fill(ds, "table2")

            DataGridView2.DataSource = ds.Tables("table2").DefaultView

        Finally

            con.Close()

            cmd = Nothing

            da.Dispose()

            con.Dispose()

        End Try

        con.Close()

        cmd = Nothing

        con.Dispose()

    End Sub

[M_K_Higa]

I think you have to qualify the field, "GRAPHICSVERSION" in the case statement.

You have:

cmd = New OleDbCommand("SELECT table2.GRAPHICSVERSION, case when [B]GRAPHICSVERSION[/B] IS NULL then 0 else 1 end as ExistsInTable1 from table2 left outer join table1 on table2.GRAPHICSVERSION=table1.GRAPHICSVERSION",con)

I think you need something like this:

cmd = New OleDbCommand("SELECT table2.GRAPHICSVERSION, case when [B]table1.GRAPHICSVERSION[/B] IS NULL then 0 else 1 end as ExistsInTable1 from table2 left outer join table1 on table2.GRAPHICSVERSION=table1.GRAPHICSVERSION",con)

[kavithabhaskar]

hi Mike.. I am using Access 2003 and VB 2005.. but the code that i have put up works in sql and I dont think its' used in access..can u pls help me!?

[M_K_Higa]

Sorry - I haven't touched Access in ages so I don't think I can add any more value to this thread.

Perhaps the only advice I can give is to open up Access and test the query within the Access environment. Otherwise, use some of the Access wizards to see what kind of code it generates. Maybe this will give you a starting point.

Sorry I can't help any further.

[kavithabhaskar]

np Mike..thanks..