Replacing one value for another

[Richiebob]

Hello all, this is my first time using a site like this for help, so please excuse me if I do it wrong. I tried looking through the forum for my issue, but I am not sure how to even put a search on it, so here it goes.

I have a field in table 1 called field 3. This field is made up of multiple items listed in field 2. So for example,

Field 1 Field 2 Field 3
1 Bob (null)
2 Frank Bob, Joe
3 Joe Bob
4 Sara Bob, Joe, Frank.
etc.

What I need to do is replace the strings with the proper value in field 1. The number of items in field 3 can be up to an unknown number of items, from 1 to n.

What I am trying to get to is:

Field 1 Field 2 Field 3
1 Bob (null)


2 Frank 1,3
3 Joe 1
4 Sara 1,2,3
etc.

Any help would be greatly appriciated. I do not mind creating a macro or using a query. The main this is that I would like it to be automated for the most part and I do not want it to be specific towards just one set of data, so I had another database with different data, I could do the same thing.

Thanks again.

[Beetle]

Welcome to the forum.

Now let's see if we can figure out what you want to do here;

Field1     Field2                   Field 3
1          Bob     
2          Frank Bob, Joe
3          Joe Bob
4          Sara Bob, Joe, Frank.

So in the above example, in the first row, since Bob is the only value in Field2, and this is Bob's row, you want nothing in Field3.
In the second row, with Frank Bob and Joe in Field2, since this is Frank's row you want to ignore Frank but list the ID values for Bob and Joe in Field3.

Is that about right?

I can't help but think this is some kind of kludge due to an improperly normalized table structure, but I'll bite. Why do you want to do this?

[June7]

How many names are involved in this issue? Is there a reference table that associates names with the Field1 IDs? Presume Field2 is just simple text and not a multi-value field.

I expect any solution will involve VBA code.

[Richiebob]

Sorry, my post did not post correctly and I will do my best to answer questions as I am fairly new to Access. There are 4 total records in my example above. My actual table can have between 1 and whatever, all depends on the data that gets imported into the database, I can have 1000, 5 or 100000. Field 1 is basically the primary key value. For instance, Bob has a primary key value of 1 in my example. Field 3 has a combination of field 2 values, so in my example, Record a, "Bob" has nothing in the filed and I needs to remain with nothing. Record b, "Frank" has Bob and Joe as values. I want to change those values out for the primary key numbers associated with the names. So for record b, I am looking for Field 1 = 2; Field 2 = Frank; Field 3 = 1,3.

The reason why I need to do this is because I am then exporting the query out into Excel and then importing into Project and project, for some data, like Dependencies, need to be numbers and not names.

Original

Field 1 (pk)	Field 2	Field 3
1	Bob
2	Frank	Bob, Joe
3	Joe	Bob
4	Sara	Bob, Joe, Frank

Objective

Field 1 (pk)	Field 2	Field 3
1	Bob
2	Frank	1, 3
3	Joe	1
4	Sara	1, 2, 3

I hope that this might explain it a little better. Thanks again for your help.

[June7]

Need answer to: Is there a reference table that associates names with the Field1 IDs? Must the import data serve as the reference that will provide the ID to associate with each name? Could names be duplicated in Field2? Like:

Field 1 (pk)	Field 2	Field 3
1	Bob
2	Frank	Bob, Joe
3	Joe	Bob
4	Sara	Bob, Joe, Frank
5	Bob	Sara, Joan

This function will convert names to IDs using original sample data. Assumes every name in Field3 has a corresponding ID in Field1 and no duplicates in Field2:

Function ConvertNames(strNames As String) As Variant
Dim arrNames As Variant
Dim strIDs As String
Dim i As Integer
If Len(strNames) = 0 Then
    ConvertNames = Null
Else
    arrNames = Split(Replace(strNames, " ", ""), ",")
    For i = 0 To UBound(arrNames)
        strIDs = strIDs & DLookup("Field1", "Table1", "Field2='" & arrNames(i) & "'") & ", "
    Next
    ConvertNames = Left(strIDs, Len(strIDs) - 2)
End If
End Function

Place the function in a general module and then call from query, textbox, or VBA.

SELECT Field1, Field2, Field3, ConvertNames(Nz([Field3],"")) AS F3Conv FROM Table1;

Ordering the IDs sequentially within the string is a whole other issue I don't want to tackle. Review http://social.msdn.microsoft.com/For...b-d860773281f7

[Richiebob]

Great. Thanks for you help. I will give this a try and let you know if I have any issues.

Thanks again.

[orange]

I recommend you tell us in business terms and plain English WHAT exactly you are trying to do. There may be many options, but having 2 or 3 names in 1 field - isn't one of them.

Pretend you're in the lineup at McDonald's - in your best plain English - describe to the person behind you What you are trying to do. Assume the person doesn't know you, has no idea what a database is, and has never heard of Access.

[Richiebob]

I am not sure how much more "Plain English" you need. I am trying to replace the word Bob with the number 1 and so on. I am doing this to export the data out to be imported into Microsoft Project. How much more English do you need.

[orange]

You may want to investigate Normalization.
http://www.phlonx.com/resources/nf3/
https://en.wikipedia.org/wiki/Database_normalization

Good luck.

[Richiebob]

To answer your questions. Is there a reference table that associates names with the Field1 IDs? -- I have a query that combines two tables into one. So, my reference table is a query.
Must the import data serve as the reference that will provide the ID to associate with each name? -- Not sure I completely understand, but I need the number equal the id for each name in field to, so if Field 3 is Bob, Joe then I need 1,3. Those numbers will serve as ID markers Project. For example if Frank is dependent upon Bob and Joe, then 1 will be for Bob and 3 will be for Joe.
Could names be duplicated in Field2? -- Not at this time. The overall goal is to normalize the data, but it is not so right now.

Thanks again for your help. If I have any issues with the code that you provided I will let you know.

[June7]

Need some reference that can be used to lookup the id number for each name. This can be a table, query, or hard-coded in the procedure. Since I didn't know if you have such a reference aside from the import data, I used the import data as the reference.