Question on creating an update query

[Ayiramala]

Hi,
I am trying to create an update query where the field DivisionID needs to be updated. What I want is this: 1 should be updated to 7, 2 to 8,3 to 9, 4 to 10, 5 to 11, and 6 to 12, I know how to do it with a single value, and how to do it by entering a parameter. But is there a way to include ALL of the above within the query, so that running it once will update all the values?

Any help is appreciated. Thanks.

[June7]

So you want to add 6 to each value? Why?

UPDATE tablename SET DivisionID = DivisionID + 6

Suggest you first test with a copy of the table.

[John_G]

Go to the query design view, and in the "Update To" row for the DivisionID, put DivisionID + 6. when you run the query, all DivisionID values will be incremented by 6.

Just be careful not to run the query more than once!

[Micron]

Solution depends: If there aren't too many fields you want to update, I think it can be done in one query, but you will need criteria for each field, otherwise the update will propagate through all records of a field. This means you will need a criteria row in the design grid for each field - you cannot put them all on the same row. You only show 6 fields, but maybe you haven't said there are 66. Also, are the numbers text or numbers?
Assuming they are numbers and you want to always add 6 (or is that a coincidence of your post?) then this will update each field that meets the criteria by adding six to the value in the row where the field is Val(n).

UPDATE [tblYourTable] SET [tblYourTable].Val1 = [Val1]+6, [tblYourTable].Val2 = [val2]+6, [tblYourTable].val3 = [val3]+6
WHERE ((([tblYourTable].Val1)=1)) OR ((([tblYourTable].Val2)=2)) OR ((([tblYourTable].val3)=3));

Note: two great answers posted before I was finished, otherwise, I probably would have bowed out. I totally missed the point that the values were in one field. I'll leave the answer in case it could be useful to somebody, sometime, in a galaxy far, far away...

[John_G]

One thing I should have mentioned -

Is DivisionID being used in any relationships?

If it is, and you don't have the relationships defined correctly, you could be facing all kinds of problems with related tables, problems which are not always evident, resulting in incorrect data.