Change the UnitPrice by a percentage in all records within the table.

  • Thread starter Thread starter Barry McConomy
  • Start date Start date
B

Barry McConomy

Within a table I have a field named "UnitPrice" which holds a currency
price.

The table has about 100 records.

I would like to be able to change the UnitPrice by a percentage in all
records within the table.

Can somebody advise how I can do this.

Regards
Barry
 
Within a table I have a field named "UnitPrice" which holds a currency
price.

The table has about 100 records.

I would like to be able to change the UnitPrice by a percentage in all
records within the table.

Can somebody advise how I can do this.

Regards
Barry
Click to expand...

By what percentage?

Make an Update query:

Update YourTable Set YourTable.UnitPrice = [UnitPrice] * 1.15 Where
YourTable.UnitPrice Is Not Null;

Multipling by 1.15 will increase the value in each field by 15%.
 
Within a table I have a field named "UnitPrice" which holds a currency
price.

The table has about 100 records.

I would like to be able to change the UnitPrice by a percentage in all
records within the table.

Can somebody advise how I can do this.
Click to expand...

Back up your database first just for safety's sake.

If you want to reduce all the prices by 10% (HAH! I know it's an
increase... <g>) create a Query based on your table; change it to an
Update query using the Query menu item or the query type icon; and on
teh UpdateTo line under UnitPrice put

=Round(0.90 * [UnitPrice], 2)

Run the query by clicking the ! icon.

The Round() will prevent prices like $2.8427 (which will display as
$2.84 but give odd sums) from being stored in the table.
 
Paste this in the SQL window after changing whichever names are necessary:

UPDATE MyTable SET MyTable.UnitPrice = MyTable.UnitPrice +
(MyTable.UnitPrice * .05)

where .05 is 5% and MyTable is your table name.

Run the query
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Hi Fred

Thanks for the info.

That worked just fine.

Regards
Barry

fredg said:
Within a table I have a field named "UnitPrice" which holds a currency
price.

The table has about 100 records.

I would like to be able to change the UnitPrice by a percentage in all
records within the table.

Can somebody advise how I can do this.

Regards
Barry
Click to expand...

By what percentage?

Make an Update query:

Update YourTable Set YourTable.UnitPrice = [UnitPrice] * 1.15 Where
YourTable.UnitPrice Is Not Null;

Multipling by 1.15 will increase the value in each field by 15%.
Click to expand...
 
Thanks John

The information provided worked fine, especially with the "Round"

Regards
Barry

John Vinson said:
Within a table I have a field named "UnitPrice" which holds a currency
price.

The table has about 100 records.

I would like to be able to change the UnitPrice by a percentage in all
records within the table.

Can somebody advise how I can do this.
Click to expand...

Back up your database first just for safety's sake.

If you want to reduce all the prices by 10% (HAH! I know it's an
increase... <g>) create a Query based on your table; change it to an
Update query using the Query menu item or the query type icon; and on
teh UpdateTo line under UnitPrice put

=Round(0.90 * [UnitPrice], 2)

Run the query by clicking the ! icon.

The Round() will prevent prices like $2.8427 (which will display as
$2.84 but give odd sums) from being stored in the table.
Click to expand...
 
Back
Top