ID two columns from 1 source (1 Viewer)

[dapfeifer]

Hey all,

I'm working on a database that has data in four columns in one table. What I need to do is take that data, compare, then replace it with data from a single column on another table if the code matches in either column.

Just to help clarify, I've got two columns of code numbers in one table, and I've got another table containing those code numbers and the company each code number represents. I need to match the code numbers, then replace the code number from the first-mentioned table with the company name from the second table. I've tried a few different techniques and can't seem to have any luck. I apologize for the lack of code, but any suggestions to accomplishing this task would be appreciated.

 

[WayneRyan]

df,

It sounds like you're saying:

Table1
======
Table1ID - AutoNumber
CompanyID - FK to company table <----+
OtherFields - Other Stuff            |
                                     | <-- Normally you join on this
Table2                               |     common field
======                               |
CompanyID - Primary Key <------------+
CompanyName - Text

But ...

Table1
======
Table1ID - AutoNumber
CompanyID - FK to company table <----+
OtherFields - Other Stuff            |
                                     | <-- You seem to be saying you
Table2                               |     want to move the Company Name
======                               |     into the other table's ID field
CompanyID - Primary Key              |
CompanyName - Text  -----------------+

Just a thought here:

Update Table1
Set    A.CompanyID = B.CompanyName            <-- This does what you said, but now the tables
From   Table1 As A Inner Join Table2 As B On      won't join on the ID field, you'd have to
       A.CompanyID = B.CompanyID                  use the CompanyName field.  If that changes
                                                  in Table2 you've got a BIG problem.

Need more info

Wayne

 

[dapfeifer]

I apologize again for the somewhat vague-ness of my description, but I did manage to get it to work through the Relationships. Don't quite ask me how it works, but it does so I'm going to go with it. Thanks again!

 

[WayneRyan]

D,

Were you just trying to join to the 2nd table to get the company name?

Wayne