Compare tables?

archer_coal

archer_coal

Well-known member
Joined
Apr 4, 2003
Messages
96
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
 
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?
 
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. Im sure my sql isnt 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.
 
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
Code: 
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.
 
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
 
There may be other columns that are null
Note: You have used "SELECT * ".
Do
Code: 
Select a,b,c from table1
If any of the columns in table1 doesnt have any values then use IIF(ISNULL)

For ex: Let us assume "a " field is null
Code: 
Select IIF(ISNULL(a),,a),b,c from table1

Hope this helps.
 
You have..

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

If Im not mistaken, you need to actually instantiate the DataSet. This is probably why youre getting the null error.

Dim myDataset As New DataSet
 
RE

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

Code: 
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?
 
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 Im not sure, but Im hoping it does. If it doesnt youre going to have one heck of a mess on your hands. :)
 
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!
 
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:
Code: 
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.


;)
 
Back
Top