"Unconcatenating" Data In A Field

[EzGoingKev]

"Unconcatenating" Data In A Field - Access

Good morning.

I have an Access table with data in two fields -

__F1__|______F2______
Name1 | 47;1295;
Name2 |
Name3 | 1412;1893;1912
Name4 | 14;1492;1812
Name5 | 1247;1298

The names in F1 will always be unique
There might be some overlap in F2.

If found some VBA that gives me a split function that works well but I have to create an expression for each string in F2. The amount can vary from empty to many, many more.

Ideally I would like two fields with all the numbers in this format:

__F1__|__F2__
Name1 | 47
Name1 | 1295
Name2 |
Name3 | 1412
Name3 | 1893
Name3 | 1912

and on for the rest of them.

Is there a way to accomplish my goal that works irregardless of how many strings there are in F2?

 

[theDBguy]

Hi. Yes. I think Colin (isladogs) wrote an article showing how to use Excel's Power Query to do this. I'll see if I can find a link.

 

[isladogs]

Ah...the perils of using multivalued fields.

I was just getting the link when DBG answered. Normalise Access data using Excel Power Query. That method should work but you may need to split F2 first??

Or you may be able to use the split function together with a union query.

Or I can do the conversion for you using the deep hidden system table where the MVF data is stored...but that would be chargeable. For more details, see Multi value fields …and why you really shouldn't use them.

 

[theDBguy]

Hi Colin. Thanks for jumping in!

 

[EzGoingKev]

I am sorry for not stating this originally, the data is in Access.

I updated my original post.

 

[isladogs]

I realise that. So you can either export it to Excel and try Power Query then import it back again …. or try one of the other approaches I suggested.

 

[EzGoingKev]

I realise that. So you can either export it to Excel and try Power Query then import it back again …. or try one of the other approaches I suggested.

The data is what it is. It is back end data for a program we use. I need to figure out a way to work with it.

I do not want to export and re-import data to and from Excel if I do not have to.

I thought of using unions but again, I would have to do a union for as many strings there are and that varies.

What I really need is something that does the opposite of Allen Browne's ConcatRelated VBA.

 

[theDBguy]

The data is what it is. It is back end data for a program we use. I need to figure out a way to work with it.

I do not want to export and re-import data to and from Excel if I do not have to.

I thought of using unions but again, I would have to do a union for as many strings there are and that varies.

What I really need is something that does the opposite of Allen Browne's ConcatRelated VBA.

Hi. Didn't you say you found some VBA already? What does it look like? Just curious...

 

[isladogs]

OK. I gave you three suggestions.
If you don't want to use any of them, that's fine but if that's the case I'll drop out of the thread now.

Good luck

 

[EzGoingKev]

Hi. Didn't you say you found some VBA already? What does it look like? Just curious...

OK so I have two different data sets.

Set one the largest string count is three. Using the union function I used this VBA I found:

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, ";", -1)
ParseText = var(X)
End Function

I did three unions and that worked fine.

The second data set has up to thirty strings in it.

 

[theDBguy]

OK so I have two different data sets.

Set one the largest string count is three. Using the union function I used this VBA I found:

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, ";", -1)
ParseText = var(X)
End Function

I did three unions and that worked fine.

The second data set has up to thirty strings in it.

Hi. Can you post a small db with some sample data sets? Thanks.

 

[EzGoingKev]

Hi. Can you post a small db with some sample data sets? Thanks.

What is the best way to share it?

Does it need to be in ZIP folder like when emailing?

 

[theDBguy]

What is the best way to share it?

Does it need to be in ZIP folder like when emailing?

Yes, try that. I just need some sample data.

 

[EzGoingKev]

The site will not let me post a link due to my post count.

 

[Gasman]

The site will not let me post a link due to my post count.

It should , if zipped.

 

[EzGoingKev]

The zip folder is in a OneDrive folder. I tried to post the share link to it but like I said, you need (10) posts to post an image or a link.

 

[theDBguy]

The site will not let me post a link due to my post count.

Did you try attaching it as a zipped file?

 

[Gasman]

The zip folder is in a OneDrive folder. I tried to post the share link to it but like I said, you need (10) posts to post an image or a link.

You upload it as an attachment via the Manage attachments button in Advanced post mode.

 

[EzGoingKev]

OK I attached it.

#1 is how the data is in the linked table. I can do nothing about that.
#2 is an example of how I would like it unconcatenated.
#3 is the final format I will put it in so I can query it against another table.

 

[theDBguy]

OK I attached it.

#1 is how the data is in the linked table. I can do nothing about that.
#2 is an example of how I would like it unconcatenated.
#3 is the final format I will put it in so I can query it against another table.

Hi. Thanks. Check out the attached.


There are several ways to approach this. I'll describe two methods, and explain the one I used.

1. The first method would be a full VBA solution where you open the first table and loop through the records and create a separate record for each pair of MODEL_CODE and ENH_MODEL
2. This is the method I decided to use:

• Using Allen Browne's ConcatRelated() function or my SimpleCSV() function, I created a long string combining all ENH_MODEL from Table1
• I then use the Split() function to put each value in an array and then looped through the array to create a table of ENH_MODELs
• Now, using a query, I am able to duplicate your Table2
• From here, you should be able to create a query that looks like Table3

Hope it helps...