compare 2 fields from 2 tables

[yoonie_85]

hello,

i've searched through all the posts, but I can't seem to find what i'm looking for. I really need help...here's the situation

i have 2 tables
table1's first 2 columns are id number column3 is a phonenumber
table2's fisrt 2 columns are id numbers, column3-5 are phonenumber, Yes olumn, no clumn, respectively.
im trying to write a query where I compare the pair of ID number from table 1 to the id number from table 2. If there is a match, I want to copy the phone number from table 1 and put it in colm3 of table 2, as well as fill in the yes & no columns respectively.

Im' hvaing a lot of trouble with this....if anyone could help me, i'd really appreciate it. thanks

 

[WayneRyan]

Yoonie,

I saw you other post in the module section. Recordsets are
definitely very, very slow compared to queries.

I did this same thing a while ago. Access front-end comparing
all of the tables in a SQL server database to all of the tables
in another SQL server database. About 150 tables, 250 thousand
records. Run-time of about 2 minutes.

My approach was to build queries in Access, send them to the
Server and execute them. My code to build these was too general
purpose (and used sysobjects/syscolumns from SQL server).

Basically for each table, I created three queries, one to detect
Additions, Deletions and Updated records.

I know that I used the:

Select *
From   TableA
Where  CStr(KeyA) & CStr(KeyB) Not In (
   Select CStr(KeyA) & CStr(KeyB)
   From   TableB)

That listed the records that TableB deleted.

The reverse of it show the records that TableB added.

The modifications were just a join of the two tables where the
"other" fields were not equal.

On SQL Server that worked very well. As an Access-Only effort,
I hear that the JET engine does not process nested selects well.
It SHOULD still be faster than the records.

Also, you might want to use "Select * into ..." or "Insert Into"
and put your results into a Temp table for further processing.

Just some thoughts, I'll revisit my s/w and watch this post.

Wayne

 

[yoonie_85]

Hi Wayne,

thanks so much for your response...

I'm having trouble with this...I'm able to get it working perfectly with recordsets. however as you said this is really inefficient. I want to try and use SQL but I'm not exactly sure of where to start

Instead of inputting a yes or no into the yes/no fields...

I now have to simple copy all the record ids that have matches to one table, and copy record ids that dont have matches to another table.

if you could point me in the right direction, i'd really appreciate it. thank you.