How do I update all records by entering value in a field?

[TomNick]

Hi,

I would like to update all the records in a database by entering a
percentage value in a form.

The condo owners in the building where I live have monthly fees to pay
From
time to time, these fees have to be increased. I would like to be abl
to
enter a percentage value in a form, and have all the records be update
by
that value (ex.: monthly fees $100 + 10% increase = new fee $110).

Thanks.

Tom

 

[Marshall Barton]

I would like to update all the records in a database by entering a
percentage value in a form.

The condo owners in the building where I live have monthly fees to pay.
From
time to time, these fees have to be increased. I would like to be able
to
enter a percentage value in a form, and have all the records be updated
by
that value (ex.: monthly fees $100 + 10% increase = new fee $110).

Just run an UPDATE query:

UPDATE tablename SET [fees] = [fees] * (1 + .10)

But that will cahnge every record in the table so you better
have a fresh backup of the table (or entire database) before
you run the query.

Since you don't seem to have a field in the table with the
date of the last fee change, there is no way for you to
specify records that the query should skip because they have
already been updated. I.e. the query can only be run once,
a potentially dangerous situation.

 

[TomNick]

I would like to update all the records in a database by entering a
percentage value in a form.

The condo owners in the building where I live have monthly fees t pay.
From
time to time, these fees have to be increased. I would like to b able
to
enter a percentage value in a form, and have all the records b updated
by
that value (ex.: monthly fees $100 + 10% increase = new fee $110).

Just run an UPDATE query:

UPDATE tablename SET [fees] = [fees] * (1 + .10)

But that will cahnge every record in the table so you better
have a fresh backup of the table (or entire database) before
you run the query.

Since you don't seem to have a field in the table with the
date of the last fee change, there is no way for you to
specify records that the query should skip because they have
already been updated. I.e. the query can only be run once,
a potentially dangerous situation.

Thanks for the response, Marsh.

There is a date field in the database that records the last update.
see what you mean about the run-once aspect. Since this is a once
year, maybe twice yearly occurence, I think a backup would be in orde
anyways. The fee increase is something that's done across the board t
everyone in the database, so there is no need to filter by date or an
other condition.

I'll give your suggestion a try. Thanks again!

To

 

[nhornsby]

If you are still having a problem, let me know. There is a simpl
solution to this, with retention of the existing data.
N

 

[Marshall Barton]

Just run an UPDATE query:

UPDATE tablename SET [fees] = [fees] * (1 + .10)

But that will cahnge every record in the table so you better
have a fresh backup of the table (or entire database) before
you run the query.

Since you don't seem to have a field in the table with the
date of the last fee change, there is no way for you to
specify records that the query should skip because they have
already been updated. I.e. the query can only be run once,
a potentially dangerous situation.

There is a date field in the database that records the last update. I
see what you mean about the run-once aspect. Since this is a once a
year, maybe twice yearly occurence, I think a backup would be in order
anyways. The fee increase is something that's done across the board to
everyone in the database, so there is no need to filter by date or any
other condition.

If you modify the query, you can set the date it was updated
at the same time. At the same time you should consider add
in where clause so if, for some unforseen reason, you need
to run the query again, it will only update the records that
need to be updated. For Example:

UPDATE tablename
SET [fees] = [fees] * (1 + .10),
LastUpdate = Date()
WHERE LastUpdate < DateAdd("m", -5, Date())

 

[TomNick]

If you are still having a problem, let me know. There is a simpl
solution to this, with retention of the existing data.
N.

Actually, I've been looking over the design of the database, and if a
all possible, it would make sense to put the "increase" field in th
data entry form. This form is an at-a-glance screenful of informatio
on all the records. Putting the field on this form would allow fo
what-if scenarios, giving the user the ability to see what an 8.5
increase would mean instead of, say, a 9% increase.

To