Three colunms data jumble

[kcaenj]

I have an access database where the last three columns are jumbled and I want to condense the data into two coherent columns. The example below shows what the data set looks like.

<table border=1>
<td>Colum A</td><td>Colum B</td><td>Colum C</td><tr>
<td>"NA"</td><td>emp1@test.com</td><td> </td><tr>
<td>OU=VPNE"</td><td>"NA"</td><td>emp2@test.com</td><tr>
<td>OU=VPNE"</td><td>"NA"</td><td>emp3@test.com</td><tr>
<td>"NA"</td><td>emp4@test.com</td><td> </td>
</table>

I need to make some kind of macro or update code that will fix all the cases (there are over 150,000) so that the third column doesn't exist and colums A and B only have the "NA" and then the "email".

Any help would be greatly appreciated.

Thanks,

KC

 

[Norie]

How did you get the data like this?

Have you split up somehow using an update query or Text>Columns... in Excel.

Do you have an example of the original raw data and also an example of the end result required?

 

[kcaenj]

these colums were imported this way from a CSV file. I would like the final product to just have two columns with NA and then the email

the other garbage column should be deleted

Any help would be appreciated.

Thanks

KC

 

[Norie]

KC

Why not choose not to import the first column when you are in the Import Wizard?

 

[SydneyGeek]

If I understand what you're after, you could try this (you'll need to change table and field names to suit) :
1. Create and run this query --
UPDATE Table1 SET Table1.[Column A] = [Column B]
WHERE (((Table1.[Column A])<>"NA"));
2. Now create and run this query --
UPDATE Table1 SET Table1.[Column B] = [Column C]
WHERE (((Table1.[Column B])="NA"));
3. Now go to the table and delete Column 3 (best done in Design view).

This will leave you with ONLY "NA" in Column A, all "NA" in Column B replaced with the corresponding email addresses in Column C, and then Column C manually removed.

Note: make a copy of the table before proceeding, so you have a fall-back position. The process could be automated but I prefer to check intermediate steps, particularly if you are making permanent changes to your data.

Denis