Re: help writing update query

[fredg]

On Sat, 16 Feb 2008 16:59:00 -0800, Rick wrote:

> First I'm new to Access, so be patient.
> I want to write an update query that goes through a table and adds 1 percent
> to an existing value. fyi i'm updating interest, which changes on the first
> of every month.
> I need to update several different columns, so if I can't do it all in one,
> I can write individual ones.
> I looked in the knowledge base, but I didn't see anything that covered it.

You can do it all at once.

Here is the Update SQL.
Copy and paste the below SQL into the SQL view of a new query.
Change the table and field names as needed.

Update YourTable Set YourTable.[Field1] = [Field1] * 1.01,[Field2] *
1.01, [Field3] * 1.01;

Note: you already have this information in your computer?
Access Help + Contents + Microsoft Access Help + Microsoft Jet SQL
Reference + Data Manipulation Language + Update Statement
There is an example of updating 2 fields at one time right there.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Marshall Barton]

Rick wrote:

>First I'm new to Access, so be patient.
>I want to write an update query that goes through a table and adds 1 percent
>to an existing value. fyi i'm updating interest, which changes on the first
>of every month.
>I need to update several different columns, so if I can't do it all in one,
>I can write individual ones.
>I looked in the knowledge base, but I didn't see anything that covered it.

Before you do anything that modifies a bunch of records,
make sure you have a good backup copy of the table, just in
case you make a mess of things (including running the query
more than one time.

The easiest way to do that is to first create an ordinary
Select query that retrieves just the fields and records
that you want to update. Once that is displaying the
correct records and fields, use the Query menu to change it
to an Update query and set the Update To cells to the
expression you want the new values to be. For exampe,
Field: Arm4yr Arm5Yr
Update To: 1.1 * Arm4yr 1.1 * Arm5yr

Generally, this is not a good thing to do and it strongly
suggests that you ahve a major flaw in you table designs.

--
Marsh
MVP [MS Access]

[Rick]

thanks for your quick response. I'm not sure where the sql place is to paste
it?
Yes there are several interest "columns" as part of the table, which has
other info as well

[fredg]

Create a New query.
When the dialog box opens, select Design View.
When the Show Table dialog appears, close the dialog (no need to
select a table, though you can if you want to).
When the grid appears, click on View + SQL (or click the tool button
that says SQL).
The SQL window will open with the word Select highlighted.
Delete the word.
Then write the query, exactly as I wrote it, replacing my generic
table and field names with the actual table and field names you are
using.

Your done.
Test the query by clicking on the Query View tool button (the one that
previously said SQL. The records will be displayed, but not updated.
If all is well you can run the query by clicking on the bang (!) tool
button. You'll be prompted to OK the update.

If you wish, you can now click on View + Design and you will see the
query grid, which will now look like the grid that Marshall suggested
to you.

PS: Don't forget to back up your data, as this appears to be the first
time you have done this.