SQL or VB find and replace table records?

[Guest]

I have an table in Access that was imported from an Excel spreadsheet. It is
very large and contains about 75000 records. Many of these records are "null"
and the ones of interest are the text character "X". Fields containing an "X"
represent a BOOLEAN 1 (one) or TRUE condition. This "X" character needs to be
replaced throughout the table by a variable that changes frequently. Below is
an example:

ID Part# Product1 Product2 Product3 Product4
1 0001 X X
2 0002 X X X
3 0003 X X

The idea is to find the "X" associating a product with a part number and
replace the "X" with the actual part number. I would like the result to
resemble the following:

ID Part# Product1 Product2 Product3 Product4
1 0001 0001 0001
2 0002 0002 0002 0002
3 0003 0003 0003

The idea is to be able to create reports on how many products use a certain
part number. Transforming the data will enable the easy creation of queries
and reports.

 

[Guest]

If, as it appears in your example, the X in each Product field is replaced by
the value in Part#, an Update Query will do the job. If this is not true,
you need to provlde more detail ie what is the is variable you mentions,
where is it, and how is it changed

The update query I mentioned is pretty easy, you just need to make each
Product field a calculated field that tests for the X and replaces it with
Part#:

Prod1: IIf([Product1] = "X", [Part#], Null)
Do the same for each Product Column

 

[Guest]

Thank you for the help. This worked great! The variable I mentioned was the
part number itself. Nothing extra was needed to make it work. I just made a
calculated field in the query using the example you gave me and it did the
trick. I am new to Access and this is a rush job so your help save me a lot
of time and frustration. Thank you!

If, as it appears in your example, the X in each Product field is replaced by
the value in Part#, an Update Query will do the job. If this is not true,
you need to provlde more detail ie what is the is variable you mentions,
where is it, and how is it changed

The update query I mentioned is pretty easy, you just need to make each
Product field a calculated field that tests for the X and replaces it with
Part#:

Prod1: IIf([Product1] = "X", [Part#], Null)
Do the same for each Product Column

I have an table in Access that was imported from an Excel spreadsheet. It is
very large and contains about 75000 records. Many of these records are "null"
and the ones of interest are the text character "X". Fields containing an "X"
represent a BOOLEAN 1 (one) or TRUE condition. This "X" character needs to be
replaced throughout the table by a variable that changes frequently. Below is
an example:

ID Part# Product1 Product2 Product3 Product4
1 0001 X X
2 0002 X X X
3 0003 X X

The idea is to find the "X" associating a product with a part number and
replace the "X" with the actual part number. I would like the result to
resemble the following:

ID Part# Product1 Product2 Product3 Product4
1 0001 0001 0001
2 0002 0002 0002 0002
3 0003 0003 0003

The idea is to be able to create reports on how many products use a certain
part number. Transforming the data will enable the easy creation of queries
and reports.

 

[Guest]

Glad it worked out for you, Andy. If you would, please rate the post. When
others are searching for an answer, it will help them identify questions that
have been answered.

Thank you for the help. This worked great! The variable I mentioned was the
part number itself. Nothing extra was needed to make it work. I just made a
calculated field in the query using the example you gave me and it did the
trick. I am new to Access and this is a rush job so your help save me a lot
of time and frustration. Thank you!

If, as it appears in your example, the X in each Product field is replaced by
the value in Part#, an Update Query will do the job. If this is not true,
you need to provlde more detail ie what is the is variable you mentions,
where is it, and how is it changed

The update query I mentioned is pretty easy, you just need to make each
Product field a calculated field that tests for the X and replaces it with
Part#:

Prod1: IIf([Product1] = "X", [Part#], Null)
Do the same for each Product Column

I have an table in Access that was imported from an Excel spreadsheet. It is
very large and contains about 75000 records. Many of these records are "null"
and the ones of interest are the text character "X". Fields containing an "X"
represent a BOOLEAN 1 (one) or TRUE condition. This "X" character needs to be
replaced throughout the table by a variable that changes frequently. Below is
an example:

ID Part# Product1 Product2 Product3 Product4
1 0001 X X
2 0002 X X X
3 0003 X X

The idea is to find the "X" associating a product with a part number and
replace the "X" with the actual part number. I would like the result to
resemble the following:

ID Part# Product1 Product2 Product3 Product4
1 0001 0001 0001
2 0002 0002 0002 0002
3 0003 0003 0003

The idea is to be able to create reports on how many products use a certain
part number. Transforming the data will enable the easy creation of queries
and reports.