Update Query Possible? (1 Viewer)

[ccflyer]

Hi Everyone,

I have a situation, and I am wondering if there is a way to use an update query to make my life a little bit easier.

I have a table with around 9,000 records, and 7 columns (for reference this will be table A). Column number 1 in table A is a lookup field that looks to table B. As it currently is, table B has 3 columns and table A looks off column 2. I want to make table A look to column 3 of table B. I have already changed table A to have a lookup to column 3 of table B, but I am wondering if there is a way to convert all of the existing records in table A, column 1 to the corresponding values in table B, column 3.

Any help or input at all would be greatly appreciated.

Thanks,
Chris

 

[FoFa]

UPDATE [Table A]
SET [Table A].[NewColumn] = [Table B].[NewColumn]
From [Table A]
INNER JOIN [Table B] on [Table A].[OldCOlumn] = [TableB].[OldColumn]

Basically

 

[ccflyer]

Thank you for replying so quickly!

I tried your idea, but can't seem to get it working. I am very new to the SQL language, and have mostly worked with querys in design view.

To help you understand a little better what I am doing:

Table A = Invoices
Column 1 = Well Name and Number

Table B = Well Info
Column 2 = Well and AFE no
Column 3 = Well Name

Currently: -Table A, Column 1 is a lookup to Table B, Column 2.
Needs to Be: -Table A, Column 1 is a lookup to Table B, Column 3
- Data needs to be converted in Table A.

Your help is very appreciated!
Chris

 

[FoFa]

In design view, add Table A first, then Table B.
Grab the column in A and drag to B on the columns to join them
Select Query, update to convert it to an update query
Drag the column you want to update down to the designer
In the UPDATE TO put the [tablename].[columnname] you want to update table A with.

 

[ccflyer]

Thanks for explaining it a little better FoFa. I tried what you said, and the query said it would effect XXXX records, but when I look at the records in table A, they still appear as the old values of column 2, table B.

-Chris

 

[KeithG]

Did you complete the update or hit cancel?

 

[ccflyer]

Hey KeithG,

I completed the update. It doesn't make sense, it seems like it should work, but it doesn't.

-Chris

 

[FoFa]

Verify the UPDATE TOO points to the NEW COLUMN in table B (this is what Table A will be updated with).

 

[ccflyer]

Yep, that's verified; UPDATE TO does point to column 3 (the one with the desired data).

-Chris

 

[FoFa]

You didn't run the queery in design view only, right? (they do not update from design view).
The column to be updated is from Table A right?
You didn't get any errors, right?
Why are you saying it is not updating, how are you looking the data after the update?

 

[ccflyer]

1) I did run the query in design view only, but I checked to see if it worked by opening table A manually.

2)The column to be updated is from table A. (Currently it holds values from table B, column 2; it needs to hold values from table B, column 3)

3)No errors, only a warning message that notified me that I would be making changes to XXXX number of records -- and I agreed to the warning.

4)After the update I am manually opening the table and checking to see if the values in table A, column 1 have been changed to the values in table B, column 3.

Thanks for your patience,
Chris

 

[FoFa]

Can you go into SQL view in your query and paste that code here?

 

[ccflyer]

Hey thanks for trying to help guys, but I ended up manually going through and updating each record manually. It was a lot of work, but I had to get this thing done by friday.

Thanks,
Chris