Update query, that updates 40 fields?

[thedude1971]

Hi there,
I'm running Access'2010. I have a table with two fields thats stores find/replace data in it. They go to a form select what needs changing and then the form runs the query.

So I need to update 40 fields and I have a update query. My problem is it only completes the first field it sees and seems to ignore the other 39. Some of those fields are blank and I'm wondering if thats the reason.

OR do I have to use SQL instead? If I have to use SQL code, can someone help me with that? As I have no experience of SQL.

With just two fields as an example, the SQL code looks like this:-
UPDATE Info, [Policy rename] SET Info.[Document ref1] = [Policy rename]![Policy to], Info.[Document ref2] = [Policy rename]![Policy to]
WHERE (((Info.[Document ref1])=[Policy rename]![Policy name]) AND ((Info.[Document ref2])=[Policy rename]![Policy name]));

I have also attached a picture of the query in normal view mode too.
Thanks very much.

 

[billmeye]

In your query you have to account for every field in your table if you want them to be updated.

 

[Beetle]

I have a table with two fields thats stores find/replace data in it.

So I need to update 40 fields and I have a update query.

So does your table have 2 fields or 40 fields? Are you perhaps confusing rows with fields in your second quote above?

You'll need to clarify your original post and maybe provide a few more details.

 

[Galaxiom]

Your need to use two separate queries. One to change [Document Ref1] and the other to change [Document Ref2].

Join the two tables on policyname and the document ref you want to change. Enter the [Policy To] as the <Update To> cell on the Document Ref field.

BTW. Your data structure is not good. The reference documents should be held as one record per reference document in a related table with the foreign key as the ID field from info table.

 

[Pat Hartman]

If you are storing the same data in 40 columns, you have a serious design issue. The references should be stored as 40 ROWS in a separate table. Then if they all have to have the same data, a simple update query will update all the rows. Although, why you would ever do that is a question I would ask.

 

[thedude1971]

Thanks for everyones suggestions.