Save calculated data to my table... 1

[gruvn]

OK - before you jump in and tell me all about normalizing my data and not duplicating unnecessary information, let me tell you want I want to do:

I have a millions of records exported from another application, and one of the fields I have is a butchered version of what I need to use as a primary key. The key should be a Long Integer, but it's text. It should not contain letters, but there are lots of entries like abc_55469. In some cases, someone has prepended a bunch of 9s onto an otherwise correct entry (e.g. 99993491).

What I can do is run queries to find all of the records that have the same kind of problems. I also know how to use calculated fields to display the actual value I want. What I don't know how to do is permanently capture my calculated data into the field so I can go ahead and delete the bad field. Until now, I've created an empty Long Integer column, running my queries with a calculated field, then copying and pasting the calculated values into my empty column. The problem is that I'm limited with copying 65,000 records at a time, and I have upwards of 8million.

Can anyone help? I'd appreciate it a ton!

 

[gwinn7]

Copy and Paste? Yikes!

Why not just run an Update query like this...

update mytable set mynewcol = myfunc([myoldcol])

Where myfunc(strtext as text) as long is a function to process and return your clean unique numerical id.

Does that help?

Gary
gwinn7

 

[gruvn]

You can take pride in the fact that you're someone's hero today! Thank you so much! I just updated 1,300,000 rows!