Same calculation on multiple fields

[tko]

I would like to perform the same (simple) calculation on multiple fields from a single table. Example: [field1]*1.14, [field2]*1.14... [fieldn]*1.14

Is there an easy way to do this without having to type out each and every expression (n could be as high as 50) in a query?

[ketbdnetbp]

You can run an Update Query -

Make a back-up first, just in case of error.

Create query in design mode, right click in space where the tables are displayed, select, query type - Update Query

in the field column Update to row: [Field1]*1.14, repeat for additional fields, etc.

That way you just have to run the query once.

Hope this helps!

Jim

[tko]

Thanks for the reply, but "repeat for additional fields" is exactly what I'm trying to avoid. I oversimplified the calculation so that people would focus on what I'm trying to accomplish. The actual calculation is more involved and I don't want to repeat it 50 times in the same query.

[orange]

Do you really have 50 fields in a table? Could you describe the table and its purpose?

Are you experienced in vba?

Here's the basics of an approach if you really are serious:

Using the query wizard, create a query using the Table you want.
Click each field you want to get the fields.
Remove/delete the fields that are not involved.
In SQL view you will see

SELECT field1,field2,fieldx from MyTable

Copy that line to a text editor
Do a replace all using source ,f replace with * 1.14, f
This will give

SELECT field1* 1.14,field2* 1.14,fieldx from MyTable

add the additional * 1.14 after the last field
to get

SELECT field1* 1.14,field2* 1.14,fieldx * 1.14 from MyTable

Copy this sql , and paste it into Query wizard SQL view.
Access will create

SELECT field1*1.14 AS Expr1, field2*1.14 AS Expr2, fieldx*1.14 AS Expr3
FROM MyTable;

But I haven't seen many normalized tables with 50 fields -- and I guess there going to be number or currency datatpyes.

[tko]

Thanks for the reply Orange. What you've suggested looks like it will accomplish what I need to do. I'll try it out and let you know. BTW: my table has one date field and a long list of currency pair fields.