changing values of one field based on another

[bluegrassstateworker]

How can I best change the values of one field in a table based on
values of another field of the same table. We have an existing table
of thousands of entries and I would like to use the following logic to
populate a new boolean field.
If field1 = "Done" Then BooleanFieldCompleted = True

I have some Excel VBA experience but limited Access. I dont want to
do this manually! Any assistance appreciated.

 

[Douglas J. Steele]

In general, you'd use an Update query. However, in this case I don't see why
you'd need such a field.

Why not just create a query with a computed field that returns True or False
based on the value in field1? You can then use the query wherever you'd
otherwise have used the table.

Storing a field that's entirely derivable from one or more other fields in
the same row is seldom a good idea.

 

[Wayne-I-M]

UPDATE TableName SET TableName.BooleanFieldCompleted = True
WHERE (([TableName]![Field1]="Done"));

Change the names to what they really are in your application
I assume the BooleanFieldCompleted is a True/False (not text) field. If not
(it is text) then change to

UPDATE TableName SET TableName.BooleanFieldCompleted = "True"
WHERE (([TableName]![Field1]="Done"));

Good luck

 

[Conan Kelly]

bluegrassstateworker,

Do you NEED to store this in the table? It seems you are adding an
unnecessary field to a table...you already have one that tells you the same
thing. By adding a new field to your table, you are increasing the size of
it. You can do what you are asking with a query where it will create a new
calculated column based on the value of [field1].

What are all the possible values for [field1]? If it is only "Done" and ONE
other thing (ie. NULL, "Not Done", etc.), then the most normalized thing to
do is remove [field1] from the table and replace it with a field of booleen
data type. If there are more than 2 possible values for [field1], then
leaving it like it is and having a calculated column in a query is probably
the better option.

If you need to store a separate field in the table, then maybe something
like this would work:

UPDATE [YourTableName]
SET [BooleanFieldCompleted] = -1
WHERE [field1] = "Done"

HTH,

Conan

 

[Conan Kelly]

bluegrassstateworker,

Just to add on to Douglas's comments (and mine), if you store this booleen
field/column separately in the table, then some of the records get changed
to "Done" in the future, you will have to manually change this field to TRUE
for each of these records, or rerun this update query. It would be very
easy for these 2 fields to be out of sync.

If you just have a calculated column in a query, then this column would be
re-calculated every time the query is run, which would mean these two
columns will always be in sync.

HTH,

Conan


\

 

[bluegrassstateworker]

UPDATE TableName SET TableName.BooleanFieldCompleted = True
WHERE (([TableName]![Field1]="Done"));

Change the names to what they really are in your application
I assume the BooleanFieldCompleted is a True/False (not text) field.  Ifnot
(it is text) then change to

UPDATE TableName SET TableName.BooleanFieldCompleted = "True"
WHERE (([TableName]![Field1]="Done"));

Good luck

--
Wayne
Manchester, England.

How can I best change the values of one field in a table based on
values of another field of the same table.  We have an existing table
of thousands of entries and I would like to use the following logic to
populate a new boolean field.
If field1 = "Done" Then BooleanFieldCompleted = True

I have some Excel VBA experience but limited Access.  I dont want to
do this manually! Any assistance appreciated.- Hide quoted text -

- Show quoted text -

To some, I didnt make clear: I want to change a group of values
permanently in the table. This will be a one-time maintenance task.
Going forward, this task will not be performed.

 

[bluegrassstateworker]

UPDATE TableName SET TableName.BooleanFieldCompleted = True
WHERE (([TableName]![Field1]="Done"));

Change the names to what they really are in your application
I assume the BooleanFieldCompleted is a True/False (not text) field.  If not
(it is text) then change to

UPDATE TableName SET TableName.BooleanFieldCompleted = "True"
WHERE (([TableName]![Field1]="Done"));

Good luck

- Show quoted text -

To some, I didnt make clear: I want to change a group of values
permanently in the table.  This will be a one-time maintenance task.
Going forward, this task will not be performed.- Hide quoted text -

- Show quoted text -

A bit of history: this is a database containing tables not well
normalized and they are now realizing it with reporting and archiving
issues. I need to keep current data and then normalize these tables
in phases. FIELD1 is actually called: status. Lo and behold, they
cannot capture entries that could have been in a status other than
"Done" (ie "Declined", "Rejected"). This means that questions such
as, "how many transactions were rejected?" can be answered now and
their last active status recorded. In the past, the status would need
to be changed to "Done" to separate it out from the active records
currently viewed but their last status would be lost. Make sense?

In this exercise, my intent is to take the "Done" entry in one FIELD1
and convert it to a TRUE value in the BooleanFieldCompleted field.
That will mark all past transactions that were closed. Then, I will
remove the "Done" choice from the list box users see when changing the
value in FIELD1. Going forward, users will have to manually mark the
record using the BooleanFieldCompleted entry when it is completed. I
will need to do something like this exercise on other fields with the
same reporting issues. I hate cleaning up: whether it be a house or a
database!