To change negative to positive in MS Access

[jeszejared]

hi,
I want to change a column in my MS Access DB to reflect positive figure
because currrently it is showing all negative figures. can anyone
assist me?

thanks.

p/s: i have seen a solution posted stating that to change the field
from negative to positive:

MyFieldNegative: -[MyField]
or
MyfieldNegative: -1*[MyField]

like to know where i should put in this particular formula/script.

thank you.

J Mil
Toronto, CANADA

 

[Rick B]

Put it in a new column in your query.

 

[Guest]

Just do an update query with either of those formulas you like as the
UpdateTo for that field.

 

[jeszejared]

Hi Klatuu,

Thank u for your info but I am quite a beginner with Access so can u
elaborate a bit more.
I got what Rick was saying but I sure like to know how to do yours.

Thanks.

J.

Just do an update query with either of those formulas you like as the
UpdateTo for that field.

hi,
I want to change a column in my MS Access DB to reflect positive figure
because currrently it is showing all negative figures. can anyone
assist me?

thanks.

p/s: i have seen a solution posted stating that to change the field
from negative to positive:

MyFieldNegative: -[MyField]
or
MyfieldNegative: -1*[MyField]

like to know where i should put in this particular formula/script.

thank you.

J Mil
Toronto, CANADA

 

[Guest]

In the main database window.
Select Queries
Select New
Select Design View
Select the table with the field you want to change
From the menu bar, select Query
Select Update Query

Now, your query builder will show the table and it's fields in the top
portion. In the bottom portion, you will only have to use the first 3 rows,
Field:, Table:, and Update To:

In the top portion, double click on the field you want to update. It will
then appear in the first column in the bottom section Showing the Field name
and the Table name.

Type the following in the Update To: row below the field and table name.
Change MyFieldName to the name of your field:

[MyFieldName] * - 1

when you tab out of the box, it will change to Expr1: [MyFieldName] * - 1

That is okay.
Click on the red exclamation pint in the tool bar to run the query.

That should do it.

Oh, I forgot the part about make a copy of the table first...just in case

Hi Klatuu,

Thank u for your info but I am quite a beginner with Access so can u
elaborate a bit more.
I got what Rick was saying but I sure like to know how to do yours.

Thanks.

J.

Just do an update query with either of those formulas you like as the
UpdateTo for that field.

hi,
I want to change a column in my MS Access DB to reflect positive figure
because currrently it is showing all negative figures. can anyone
assist me?

thanks.

p/s: i have seen a solution posted stating that to change the field
from negative to positive:

MyFieldNegative: -[MyField]
or
MyfieldNegative: -1*[MyField]

like to know where i should put in this particular formula/script.

thank you.

J Mil
Toronto, CANADA

 

[jeszejared]

Klatuu,

You ARE the BEST~!!!! THANKS A ZILLION!!!

J.

In the main database window.
Select Queries
Select New
Select Design View
Select the table with the field you want to change
From the menu bar, select Query
Select Update Query

Now, your query builder will show the table and it's fields in the top
portion. In the bottom portion, you will only have to use the first 3 rows,
Field:, Table:, and Update To:

In the top portion, double click on the field you want to update. It will
then appear in the first column in the bottom section Showing the Field name
and the Table name.

Type the following in the Update To: row below the field and table name.
Change MyFieldName to the name of your field:

[MyFieldName] * - 1

when you tab out of the box, it will change to Expr1: [MyFieldName] * - 1

That is okay.
Click on the red exclamation pint in the tool bar to run the query.

That should do it.

Oh, I forgot the part about make a copy of the table first...just in case

Hi Klatuu,

Thank u for your info but I am quite a beginner with Access so can u
elaborate a bit more.
I got what Rick was saying but I sure like to know how to do yours.

Thanks.

J.

Just do an update query with either of those formulas you like as the
UpdateTo for that field.

:

hi,
I want to change a column in my MS Access DB to reflect positive figure
because currrently it is showing all negative figures. can anyone
assist me?

thanks.

p/s: i have seen a solution posted stating that to change the field
from negative to positive:

MyFieldNegative: -[MyField]
or
MyfieldNegative: -1*[MyField]

like to know where i should put in this particular formula/script.

thank you.

J Mil
Toronto, CANADA

 

[Conan Kelly]

Jeszejared,

Since you are so new to Access, I thought I'd tell you some things you may not know (I apologize if I'm telling you something you
already know).

Rick's method is just a calculated field in a SELECT query. When you run this query, this calculated field shows up in your
results, but it doesn't actually exist. The underlying data remains unchanged.

Klatuu's method is an UPDATE query and it WILL change your underlying data.

Both methods are correct, but it all depends on what you will be doing with your data as to which method you decide to use. If you
use Klatuu's method, any queries based on this table will run faster after you run his UPDATE query. You may not notice it if you
have just a few thousand or hundred thousand records, but the higher the number of records, the more you will notice.

If you will be importing new data into this table from text files or spreadsheets, you might consider using a calculated field in a
SELECT query. If you are importing, this can be done using the update method, but it can be a little tricky. Let me explain: If
you are importing data from an external source (text files, spreadsheets, etc...) and this field is negative in these source files,
when you run the UPDATE query on this table that has old data that has already been switched to positive and also has new data that
is still negative, all of the old data will be flipped to negative numbers and the new data will become positive. To work around
this you either need to change the data in the source files to positive before importing, have a 2-step import (import into an
intermediate table first--and then from the intermediate table, import into the main data table flipping the negatives to positives
in the process), or have an extra field in your table that will signify if the data is old, already changed data or if it is new,
unchanged data and then build your UPDATE query so it will change only the new, unchanged data.

If you are manually entering data, none of that matters. All you have to do is remember to enter it as a positive number instead of
a negative one.

I hope this helps.

Klatuu and Rick, I apologize if I am stepping on your toes.

Conan Kelly

Klatuu,

You ARE the BEST~!!!! THANKS A ZILLION!!!

J.

In the main database window.
Select Queries
Select New
Select Design View
Select the table with the field you want to change
From the menu bar, select Query
Select Update Query

Now, your query builder will show the table and it's fields in the top
portion. In the bottom portion, you will only have to use the first 3 rows,
Field:, Table:, and Update To:

In the top portion, double click on the field you want to update. It will
then appear in the first column in the bottom section Showing the Field name
and the Table name.

Type the following in the Update To: row below the field and table name.
Change MyFieldName to the name of your field:

[MyFieldName] * - 1

when you tab out of the box, it will change to Expr1: [MyFieldName] * - 1

That is okay.
Click on the red exclamation pint in the tool bar to run the query.

That should do it.

Oh, I forgot the part about make a copy of the table first...just in case

Hi Klatuu,

Thank u for your info but I am quite a beginner with Access so can u
elaborate a bit more.
I got what Rick was saying but I sure like to know how to do yours.

Thanks.

J.

Klatuu wrote:
Just do an update query with either of those formulas you like as the
UpdateTo for that field.

:

hi,
I want to change a column in my MS Access DB to reflect positive figure
because currrently it is showing all negative figures. can anyone
assist me?

thanks.

p/s: i have seen a solution posted stating that to change the field
from negative to positive:

MyFieldNegative: -[MyField]
or
MyfieldNegative: -1*[MyField]

like to know where i should put in this particular formula/script.

thank you.

J Mil
Toronto, CANADA

 

[jeszejared]

Hi Conan,

This is WOW! Thank you very much for your input. I got your first
solution but the second one is a bit vague for me (or have an extra
field in your table that will signify if the data is old, already
changed data or if it is new, unchanged data and then build your UPDATE
query so it will change only the new, unchanged data.) - again,
apologize for my amateur knowledge in Access. How do you signify in
your table that the data is old and allow the query to just change the
new data to negative, instead.

let me thank you in advance - i appreciate any help that you can pass
on, otherwise i understand if you are tied down with other things, i
can still use the first way to work around my problem. btw, i got like
up to 90,000 lines to consider so yea! its a LOT of data to manipulate.

J.

Jeszejared,

Since you are so new to Access, I thought I'd tell you some things you may not know (I apologize if I'm telling you something you
already know).

Rick's method is just a calculated field in a SELECT query. When you run this query, this calculated field shows up in your
results, but it doesn't actually exist. The underlying data remains unchanged.

Klatuu's method is an UPDATE query and it WILL change your underlying data.

Both methods are correct, but it all depends on what you will be doing with your data as to which method you decide to use. If you
use Klatuu's method, any queries based on this table will run faster after you run his UPDATE query. You may not notice it if you
have just a few thousand or hundred thousand records, but the higher the number of records, the more you will notice.

If you will be importing new data into this table from text files or spreadsheets, you might consider using a calculated field in a
SELECT query. If you are importing, this can be done using the update method, but it can be a little tricky. Let me explain: If
you are importing data from an external source (text files, spreadsheets, etc...) and this field is negative in these source files,
when you run the UPDATE query on this table that has old data that has already been switched to positive and also has new data that
is still negative, all of the old data will be flipped to negative numbers and the new data will become positive. To work around
this you either need to change the data in the source files to positive before importing, have a 2-step import (import into an
intermediate table first--and then from the intermediate table, import into the main data table flipping the negatives to positives
in the process), or have an extra field in your table that will signify if the data is old, already changed data or if it is new,
unchanged data and then build your UPDATE query so it will change only the new, unchanged data.

If you are manually entering data, none of that matters. All you have to do is remember to enter it as a positive number instead of
a negative one.

I hope this helps.

Klatuu and Rick, I apologize if I am stepping on your toes.

Conan Kelly

Klatuu,

You ARE the BEST~!!!! THANKS A ZILLION!!!

J.

In the main database window.
Select Queries
Select New
Select Design View
Select the table with the field you want to change
From the menu bar, select Query
Select Update Query

Now, your query builder will show the table and it's fields in the top
portion. In the bottom portion, you will only have to use the first 3 rows,
Field:, Table:, and Update To:

In the top portion, double click on the field you want to update. It will
then appear in the first column in the bottom section Showing the Field name
and the Table name.

Type the following in the Update To: row below the field and table name.
Change MyFieldName to the name of your field:

[MyFieldName] * - 1

when you tab out of the box, it will change to Expr1: [MyFieldName] * - 1

That is okay.
Click on the red exclamation pint in the tool bar to run the query.

That should do it.

Oh, I forgot the part about make a copy of the table first...just in case


:

Hi Klatuu,

Thank u for your info but I am quite a beginner with Access so can u
elaborate a bit more.
I got what Rick was saying but I sure like to know how to do yours.

Thanks.

J.

Klatuu wrote:
Just do an update query with either of those formulas you like as the
UpdateTo for that field.

:

hi,
I want to change a column in my MS Access DB to reflect positive figure
because currrently it is showing all negative figures. can anyone
assist me?

thanks.

p/s: i have seen a solution posted stating that to change the field
from negative to positive:

MyFieldNegative: -[MyField]
or
MyfieldNegative: -1*[MyField]

like to know where i should put in this particular formula/script.

thank you.

J Mil
Toronto, CANADA

 

[Conan Kelly]

Jeszejared,

If you want to use the UPDATE query method (change the data in the table) and you will be importing new data into the table, there
are a few ways to do this so you don't change data that has already been changed before, like I mentioned before:

1. Change the values in the source text files to positive numbers before importing
2. Use an intermediate table to import into first and then run an APPEND query that will add the new data to the main table and
change the negatives to positives in the process
3. Add an extra field to your main table to determine if the data has already been changed to a positive number and run an
UPDATE query that will only change the numbers that haven't been changed.

In order to do number 3, you would add a field to the main table (maybe call it "Old") and make it a "Yes/No" data type. After
doing that, you'll notice below that you can change the format to "Yes/No", "True/False", or "On/Off". You can choose which ever
one makes the most sense to you. Then if you built your UPDATE query following Klatuu's instructions, you would open it in design
view (if you saved it) and ad the new "Old" field to the query. In the "Update To" line you would put "Yes" or "True" etc. (w/o the
quotes), depending on the format you chose, and in the "Criteria" line you would probably put "Is Null" (<--- w/o the quotes).

Now the first time you run this query, the "Old" field for every record will probably be NULL, so it will change all -'s to +'s (and
+'s to -'s) and it will also change the "Old" field for all records to "Yes" (or "True" etc.). Now if you import new data, all of
the new numbers in the field that you want to change will be - (all of the old data will be +) and the "Old" field for the new
records will be NULL (all of the old records will be set to "Yes/True/On"). Now if you run this UPDATE query after the import, it
will change only the records where "Old" is NULL: It will set "Old" to "Yes/True/On" and it will change all -'s to +'s (and +'s
to -'s) in the field that you want to change.

Now remember, make a copy of your main table and test these UPDATE queries on the copy to make sure that they are working the way
that you want them to before running them on the main table.

I hope this helps,

Conan

Hi Conan,

This is WOW! Thank you very much for your input. I got your first
solution but the second one is a bit vague for me (or have an extra
field in your table that will signify if the data is old, already
changed data or if it is new, unchanged data and then build your UPDATE
query so it will change only the new, unchanged data.) - again,
apologize for my amateur knowledge in Access. How do you signify in
your table that the data is old and allow the query to just change the
new data to negative, instead.

let me thank you in advance - i appreciate any help that you can pass
on, otherwise i understand if you are tied down with other things, i
can still use the first way to work around my problem. btw, i got like
up to 90,000 lines to consider so yea! its a LOT of data to manipulate.

J.

Jeszejared,

Since you are so new to Access, I thought I'd tell you some things you may not know (I apologize if I'm telling you something you
already know).

Rick's method is just a calculated field in a SELECT query. When you run this query, this calculated field shows up in your
results, but it doesn't actually exist. The underlying data remains unchanged.

Klatuu's method is an UPDATE query and it WILL change your underlying data.

Both methods are correct, but it all depends on what you will be doing with your data as to which method you decide to use. If
you
use Klatuu's method, any queries based on this table will run faster after you run his UPDATE query. You may not notice it if
you
have just a few thousand or hundred thousand records, but the higher the number of records, the more you will notice.

If you will be importing new data into this table from text files or spreadsheets, you might consider using a calculated field in
a
SELECT query. If you are importing, this can be done using the update method, but it can be a little tricky. Let me explain:
If
you are importing data from an external source (text files, spreadsheets, etc...) and this field is negative in these source
files,
when you run the UPDATE query on this table that has old data that has already been switched to positive and also has new data
that
is still negative, all of the old data will be flipped to negative numbers and the new data will become positive. To work around
this you either need to change the data in the source files to positive before importing, have a 2-step import (import into an
intermediate table first--and then from the intermediate table, import into the main data table flipping the negatives to
positives
in the process), or have an extra field in your table that will signify if the data is old, already changed data or if it is new,
unchanged data and then build your UPDATE query so it will change only the new, unchanged data.

If you are manually entering data, none of that matters. All you have to do is remember to enter it as a positive number instead
of
a negative one.

I hope this helps.

Klatuu and Rick, I apologize if I am stepping on your toes.

Conan Kelly

Klatuu,

You ARE the BEST~!!!! THANKS A ZILLION!!!

J.

Klatuu wrote:
In the main database window.
Select Queries
Select New
Select Design View
Select the table with the field you want to change
From the menu bar, select Query
Select Update Query

Now, your query builder will show the table and it's fields in the top
portion. In the bottom portion, you will only have to use the first 3 rows,
Field:, Table:, and Update To:

In the top portion, double click on the field you want to update. It will
then appear in the first column in the bottom section Showing the Field name
and the Table name.

Type the following in the Update To: row below the field and table name.
Change MyFieldName to the name of your field:

[MyFieldName] * - 1

when you tab out of the box, it will change to Expr1: [MyFieldName] * - 1

That is okay.
Click on the red exclamation pint in the tool bar to run the query.

That should do it.

Oh, I forgot the part about make a copy of the table first...just in case


:

Hi Klatuu,

Thank u for your info but I am quite a beginner with Access so can u
elaborate a bit more.
I got what Rick was saying but I sure like to know how to do yours.

Thanks.

J.

Klatuu wrote:
Just do an update query with either of those formulas you like as the
UpdateTo for that field.

:

hi,
I want to change a column in my MS Access DB to reflect positive figure
because currrently it is showing all negative figures. can anyone
assist me?

thanks.

p/s: i have seen a solution posted stating that to change the field
from negative to positive:

MyFieldNegative: -[MyField]
or
MyfieldNegative: -1*[MyField]

like to know where i should put in this particular formula/script.

thank you.

J Mil
Toronto, CANADA