Comma Delimited List in Memo Field

[LadyDi]

I have inherited a database where technicians track equipment that has been repaired. In this database, there is a memo field where the technician lists all the part numbers he / she uses to repair the piece of equipment. This field was never reported against and was strictly used for reference. However, someone is coming to me now and asking that a report be generated with that information. Then she will separate those parts out on her end. However, because it is a free form field, the technician is not forced to put a comma between each part number. I was wondering if there is a way for the database to go through approximately 62,000 records and ensure that there is a comma between each part number? I was also wondering if there is way to program the database to automatically insert a comma after each part number.

I know having a list of part numbers in one field is not the best practice for a database, but as I said, I just inherited this database. To my knowledge, this field was never reported against, so I never made any changes to it. There were over 55,000 rows in the database when I got it. I am open to any suggestions on how to improve this.

 

[pbaldy]

I was wondering if there is a way for the database to go through approximately 62,000 records and ensure that there is a comma between each part number?

How is it to know? If a space separates them, you could use the Replace() function to replaces spaces with commas. That said, if somebody put

A 123

instead of

A123

this would break down.

 

[LadyDi]

Unfortunately, there are a few records where the part numbers is listed as 123456789 A. Most of the time they list the parts this way 123-456789-000-A, but there are a few occasions where they used spaces instead of dashes.

 

[pbaldy]

For Access to be able to do it, you have to give it rules to follow. You might look at the field and "know" what the part numbers are, but in your head, perhaps without even realizing it, you're following a set of rules to distinguish between them. I think to be able to handle inconsistent data like that, you'll need to come up with those rules. It might end up being a function that steps through the field character by character.

 

[redalert]

I spent many years tidying large amounts of data and developed a number of methodologies and techniques along the way.

Before you do anything else, and you may have already done this, ensure that new records being created by the technicians follow the new rules.

I was nearly always was able to write some code and apply some relatively simple logic and rules that corrected a large percentage of the data. You already have the format for the part number and a list of part numbers so some of the logic is simple. A function to identify how many times the format exists for each record will be handy and also to identify whether there are any other characters in the data that fall outside of the format.

For the remaining records I looked for patterns in the data and write code to
identify records where the same pattern applied. I would either write code to automate the corrections or create a form where I could correct the data either manually or with some clever interactive string manipulation functionalty.

The law of diminishing returns will come in somewhere in this process and ever increasing amounts of time can be spent correcting a proportionately small number of records.

You will need to decide when to stop and what the consequence are of having records that you cannot fully or even partially correct and that do not adhere to the new rules.

I used to create a series of status codes that indicated where the record was in the correction process. Create a new field to store this code.

Work methodically and systematically through the data, and keep regular backups so that you an roll back the data if required. Document what you have done to the data between backups.

You will need some string manipulation functions so I have attached a database of some that I have used. If you develop any functions of your own then keep them very simple and try to make them application independant
so that they can be used for other jobs.

At the last resort, there may be paper records somewhere from which part numbers can be taken but that is time consuming.

Do you have a list of part numbers for each piece of equipment that was repaired and does the record have a reference to that piece of equipment? If so then that list could be used to check inconsistences in part numbers at any stage.

It would be good if you could submit a sample of the data. Just a glance will tell me a lot.

 

[Galaxiom]

Firstly I would be changing the data structure so the parts used are held in a related table as individual records. This will make enterng the data more reliable and reporting on it easier. Only valid part numbers should be accepted.

Use a query and a recordset to append records to the related table. This query will use a custom function based on Split() to parse all the comma separated entries. Then compare the results with valid part numbers, accepting those which fit and inspecting those that don't.

Don't delete anything until all the data has been converted.