I have a field that lists the county names that a company does business in. The field type is memo and the string ranges from no entries to 377 characters. The users have entered counties into this memo field in the following format: CountyName1, CountyName2, CountyName3, etc. Each business has a unique ID and there are 509 businesses.
My question is...How can I quickly convert this field to a record-based table, where there will be multiple records of a given business but only a single record for a business and county combination? The primary key would then shift from the unique ID to a shared key set on the unique ID and county.
This is a common problem I've dealt with when cleaning up behind Excel users who migrate to Access. In the past I have done this in a manual way, but given the number of business records and the number of counties that may be listed for a given business, I'd rather automat this in some fashion.
Any advice would be greatly appreciated.
Thanks,
David