Compare tables?

[archer_coal]

I have two tables

both have a FIELD 1

I want to compare the two fields and have my process report anything that doesnt match ( not case sensitive because its numerical)

 

any ideas?

i have my oledbadapter and connection string all setup

 

 

my select statement would be somthing like

 

SELECT Field1 FROM TABLE1, TABLE2 WHERE

 

then what?

Thanks for any help

[APaule]

Do the two tables have any fields in common. I mean do they have the same primary key, or something like that, or are they in the same order and have the equal count of records?

[wyrd]

So you want to retrieve the values in table 1 columnX that do not match ANY values in table 2 columnX? You can do this easily with a subquery. I'm sure my sql isn't exact but it should give you a general idea of what you need to do;

 

SELECT field1 FROM table1 WHERE field1 NOT IN (SELECT field1 FROM table2)

 

Hope that helps.

[archer_coal]

RE

 

Yes to both of your questions aPaule and wyrd. There is a common field of ID numbers. Not the actual id auto increment field, but thats ok for this query i think.

 

 

here is what i came up with that dosnt work

Dim Dataadapter As New OleDbDataAdapter()
       Dataadapter = New OleDbDataAdapter( _
"SELECT * FROM TABLE1 where FIELD1 NOT LIKE (Field1 FROM TABLE2) order by id", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\data\db1.mdb")

       Dim myDataset As DataSet
       Dataadapter.Fill(myDataset)
       Datagrid1.datasource = myDataset

  Dataadapter.Dispose()
 Dataset.Dispose()

 

I get an error that says "Value Cannot be null" at Dataadapter.Fill(myDataset)

 

Since there are no empty fields in either table for that column, im assuming the SQL syntax is wrong?

 

Thanks for your time and help on this.

[zy_abc]

Change: SELECT * FROM TABLE1 where FIELD1 NOT LIKE (Field1 FROM TABLE2) order by id

 

To: SELECT * FROM TABLE1 where FIELD1 NOT IN (SELECT Field1 FROM TABLE2) order by id

 

Hope it helps

[archer_coal]

re

 

Same error i'm afraid :confused:

[zy_abc]

There may be other columns that are null

Note: You have used "SELECT * ".

Do

Select a,b,c from table1

If any of the columns in table1 doesn't have any values then use IIF(ISNULL)

 

For ex: Let us assume "a " field is null

Select IIF(ISNULL(a),'',a),b,c from table1

 

Hope this helps.

[wyrd]

You have..

 

Dim myDataset As DataSet

Dataadapter.Fill(myDataset)

Datagrid1.datasource = myDataset

 

If I'm not mistaken, you need to actually instantiate the DataSet. This is probably why you're getting the null error.

 

Dim myDataset As New DataSet

[archer_coal]

RE

 

Ok i modified the code to make certian the dataset was truely being instantiated but now im getting a non-specific error.

 

Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\data\db1.mdb")
       objConn.Open()
       Dim Dataadapter As New OleDbDataAdapter()
       Dataadapter = New OleDbDataAdapter("SELECT * FROM star where Field1 NOT (Field1 FROM tax) order by id", objConn)
      
       Dim myDataSet As New DataSet()
       Dataadapter.Fill(myDataSet)
       DataGrid3.DataSource = myDataSet

       Dataadapter.Dispose()
       myDataSet.Dispose()

 

I changed NOT LIKE to NOT because its more Exact, but both "NOT LIKE" and "NOT" produce the same error

 

Does msAccess not support these expressions?

[wyrd]

The syntax I gave you uses NOT IN and a subquery (SELECT Field1 FROM tax) Scroll up and re-read my post.

 

Whether or not MS Access supports IN or subqueries I'm not sure, but I'm hoping it does. If it doesn't you're going to have one heck of a mess on your hands. :)

[archer_coal]

RE

 

I dont know why but... the first time i tried NOT IN, the app froze like a cheap marguritta. It works now though! :D

Thanks all and wyrd!

[wyrd]

Good to hear it works.

[tiagoney]

Re: RE

 

Please keep in mind that you should specify a field in your select statement. Using " * " is not a good strategy.

 

your code should look like this:

SELECT [[b]Field1[/b]] FROM star where Field1 NOT IN (SELECT [[b]Field1[/b]] FROM tax)"

 

also, there is no need for the order by clause, unless you need the data to be inserted in the new table in a given order.

 

 

;)