Well, Are you tying the Row to the Supplier name with anything other than a Name, (SupplierID number or something) ?
Cause if you are going to (Are You?) still reference the row of data to the supplier, you will also have to flesh this out as well. FWIW, in my own internal system, I do not use the FirstName, LastName, MI paradigm. I use MainName, which can be a Company or a Person. The other way doesn't fit very well if your system mixes people and companies together. Further the way MainName is stored, lends itself (IMHO) the best for creating an Address on the fly from the dataset when doing Invoices and Checks, etc. Have you done a Select Distinct against the Name column in your existing to see how many permutations of the Same Referenced Supplier is presented? That will be a good step to take (I have spent a lot of time at the command line doing "Update Set" to change the different ways the data was entered for the same reference to a common one and I'm afraid there really is no way to escape this part of the process. I would project your dataset into a new table or even a different DB to do this work, so you have an escape plan in case you make a boo boo in the updates... Create a table for the SupplierNames Including a Column to use as the SupplierID (maybe just using Autonumber). Add a Column to the Datatable with the same datatype and name as the SupplierID in the Supplier Table Once you have the Names Standardized, you can Insert the Names into your Supplier using "Insert into Select Distinct" construct. Assign your supplier Numbering system to the dataTable using a Cursor to traverse the Supplier table, doing an update on the Datatable where the SupplierName = SupplierName.. When you get to the stage of having the Names Normalized in the DataSet, come back if you need help, once there are specific ColumnNames and such to refer to. ----- Original Message ----- From: "Lin MacDonald" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[email protected]> Sent: Sunday, January 13, 2008 9:36 AM Subject: [RBASE-L] - pulling a text column apart > Sorry, another Newbie question -- > As I redesign my Database and application, I want to correct some > design problems. One of them is that in my Supplier table, I > have one column for Contacts. I'd like to move that column to > another table with multiple fields, such as First Name, Last > Name, etc. The problem is that the data in that field is not > consistant. It could contain the following: > Null > FName > FName LName > FName1, FName2 > FName1, FName2, FName3 > So, what would be the best way to break it apart? Should I use a > temporary table? I thought of using SSUB, but that wouldn't work > for the ones that had a First and Last name in the field with > just a space in between. > thanks, > Lin MacDonald >
