From
memName1:

Shekhar Mehta
Aniruddh Daga
Aniruddh Daga, Nisheeth Totla
Virendra Singhvi
Raakesh Jain
Pradeep Tibrewal

memName2:

Naresh Kumar Jalan
Shekhar Mehta
Shekhar Mehta
Raj Somani
Sunil Poddar

memName3:

Vinay Agarwal
Deepak Choudhary, Raghupati Bhuwalka
Sanjay Bagaria
Raghupati Bhuwalka, Ramesh Khaitan
Nathmal Neotia, Ram Pal Widhawan, S Chiraniwala
Mahesh Shah, Padam Agarwal, Sanjay Bagaria
Sunil Poddar


To:

New Column

Shekhar Mehta
Aniruddh Daga
Nisheeth Totla
Virendra Singhvi
Pradeep Tibrewal
Raakesh Jain
Naresh Kumar Jalan
Raj Somani
Sunil Poddar
Vinay Agarwal
Deepak Choudhary
Raghupati Bhuwalka
Sanjay Bagaria
Nathmal Neotia

Well, I may be wrong, but I don't think a query will work. Since you have multiple names in memname3, and possibly memname1 and memname2, you are going to have to separate them out.
Do you use VBA?
If so, do you know how to use arrays?

Kevin

dartcoach

Well is there any function in MS Access which I can use to extract the commas from the multiple names?? I might use some other method to get the distinct names.

There is a way to do what you want in Access VBA. Some questions:
1. Are you using a form?
2. How are you going to run this process?
3. How often are you going to run this process?

Let me know, I can build you the code you need.

Kevin

dartcoach

Why would you put the first and last names in the same column? Why don't you seperate them into LName, FName columns while you are at it?

An array will do this using a split on the " " (that's a space), and a split on the ", " in other fields.

You will need to check for middle names as well, it looks like. Sorry if I don't understand the forms of the names. Use UBound to check for the size of the array created on each record and move the extra field to a MName column as needed. etc.

You will need a function for this.

If this is not a lot of data, you can also do manual ETL in Excel. That is just a copy and paste operation to the proper number of columns, then a Find and Replace on the commas and spaces as needed.

HTH

mmcdonal

arnabghosh,
I agree with mmcdonal, will these need to be eventually separated again into a 1st Name, Last Name and Middle Name?

Otherwise, I have the code, if you want it, to do what you've asked.

Let me know.

Kevin

dartcoach

I don't think it's that difficult:

1. Make 3 queries that will each pull one column from the table.

2. Open a new query in SQL view.

3. Make a union query as follows:

SELECT * from Query1
UNION ALL
SELECT * from Query2
UNION ALL
SELECT * from Query3;

Save the query as Query4

4. Make a new query called Query5 that selects distinct records from Query4. It's a normal query except that the Unique Values (or Unique Records, I never can remember) property is set to Yes.

That should do it.



Mark J. Weisberger

Mark,

That would be fine, if there was only 1 name in each column. If you notice the test data, each column possibly carries more than 1 name separated by a comma. These also have to be split out. I've written a vba module that concatenates all 3 columns into 1 and loads a temp table, then separates each record out the however many names are in that row and individually loads them to a table with the name field as unique, therefore not allowing multiple entries.

He hasn't asked for this code, so I think maybe he's gone another direction.

Kevin

dartcoach

arnabghosh,

Here's the code to do what you want. You will need to create a table with a key on name. In my test, I named it tblDistinctNames, with the column name DistinctName.

Put this code behind a button:


   Dim rs As Recordset
   Dim db As Database
   Set db = CurrentDb
   DoCmd.SetWarnings False
   mySQL = "SELECT [memName1] & ', ' " _
         & " & [memName2] & ', ' " _
         & " & [memName3] AS myName INTO myTemp " _
         & "FROM tblMemNames;"
   DoCmd.RunSQL mySQL
   Set rs = db.OpenRecordset("Select * from myTemp;")
   With rs
      If Not .EOF Then
         .MoveLast 'Determine How Many Records
         myRecCount = .RecordCount
         .MoveFirst ' Go Back to the start of the recordset
         For A = 1 To myRecCount ' Loop through the records
            x = Len(!myName) + 1 ' Determine how many characters on in the record
            mynewname = "" ' Make sure the Distinct Name Field is blank
            For y = 1 To x ' Loop through the record
               If Mid(!myName, y, 1) = "," Or y = x Then ' Check for comma separator or last character
                  mySQL = "Insert into tblDistinctNames (DistinctName) " _
                        & "Select " & """" & mynewname & """" & ";"
                  DoCmd.RunSQL mySQL ' Insert the Disticnt Name
                  mynewname = "" ' Since still in the same record, reset to blank
                  y = y + 1 ' This skips over the space after the comma
               Else
                  mynewname = mynewname & Mid(!myName, y, 1) ' Builds Distinct Name
               End If
            Next y
            .MoveNext
         Next A
      End If
   End With
   rs.Close
   Set rs = Nothing
   DoCmd.DeleteObject acTable, "myTemp"

Hope this does what you want.

Kevin



dartcoach