I am working on a Contacts dbfor a client. Imported tbl with 166K records. There are however, probably only about 25k valid records in the tbl. The problem is the data was exported from Maximizer, and spaces have been randomly added along with end of line cr's or lf's (not sure which).
Each complete contact is spread among every 7 records. So I need to figure out a way to get all data from every 7 records to be put into only one record. I currently have 77 fields across.
I am not very good at code, so if there is anyone who can help me, I would be incredibly grateful!
I think you are probably going to have to approach this with a mix of queries and vba
a great deal depends on what the transcription errors are. out of curiosity are the errors in the original data or in the export process?
you should be able to get rid of trailing spaces using "trim" / "ltrim" & "rtrim" functions
you should be able to get rid of non ascii characters, or multiple spaces, using "replace" function
it may be that you will need to develop a series of queries.
if you develop a query to run within the form then you could iterate through all the columns in the recordset collection, populating the recordset with a "select * from ...". I forget the syntax but you can find each column in a recordset without expressly knowing its name - i think its something like me.recordset.field(index)
the final step would be a "select distinct row" - probably as an insert into your final table. You will undoubtably have duplicates that your user will have to make decisions on.