Creating a new column using a combination of data from different columns in access (New to the forum

[MazT]

Hi

I have a table with multiple columns - however i need to cleanse the data. Two of the columns (A and B) have the similar data collected from different sources. We generally go with the data in Column B as it is most accurate. However, due to an error that's occured, some of the data in B comes up as 'unknown/x'. My task is to update B where the cells return 'unknown/x' with the data in column A.

In my mind I am thinking the best way to achieve this is to create a third column C, where I retain data in B except where B is 'unknown/x' in which case C will retain data in A.

Is that the best way round it?

What is the best syntax to use? I am desperate for help.

[JoeM]

There are two ways to go about this:

1. Update the data in column B with an Update Query.
You would first create a query that only returns column B, and put "unknown/x" in the Criteria of this field. This will then only return records that have this value in it.
Then, change it to an Update Query, and on the "Update to:" line for this field, enter [ColumnA] (or whatever the name of your column A field is).
Then run the query.
Column B will not be updated. You can complete remove column A from the table, if your desire.

2. Create a query that returns the value you want in a Calculated Field. This is desireable if you wish to keep both the original column A and column B values (for historical reasons or whatever). That calculated field would look something like:
MyValue: IIF([ColumnB]="unknown/x",[ColumnA],[ColumnB])

[MazT]

There are two ways to go about this:

1. Update the data in column B with an Update Query.
You would first create a query that only returns column B, and put "unknown/x" in the Criteria of this field. This will then only return records that have this value in it.
Then, change it to an Update Query, and on the "Update to:" line for this field, enter [ColumnA] (or whatever the name of your column A field is).
Then run the query.
Column B will not be updated. You can complete remove column A from the table, if your desire.

2. Create a query that returns the value you want in a Calculated Field. This is desireable if you wish to keep both the original column A and column B values (for historical reasons or whatever). That calculated field would look something like:
MyValue: IIF([ColumnB]="unknown/x",[ColumnA],[ColumnB])

Thank you - I used the second option as I wanted to maintain both original columns. It worked like a breeze!

[JoeM]

You are welcome.
Glad it all worked out for you!