"Unconcatenating" Data In A Field (1 Viewer)

EzGoingKev

Registered User.
Local time
Today, 15:06
Joined
Nov 8, 2019
Messages
178
"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?
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 12:06
Joined
Oct 29, 2018
Messages
21,358
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

MVP / VIP
Local time
Today, 19:06
Joined
Jan 14, 2017
Messages
18,186
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

I’m here to help
Staff member
Local time
Today, 12:06
Joined
Oct 29, 2018
Messages
21,358
Hi Colin. Thanks for jumping in!
 

EzGoingKev

Registered User.
Local time
Today, 15:06
Joined
Nov 8, 2019
Messages
178
I am sorry for not stating this originally, the data is in Access.

I updated my original post.
 

isladogs

MVP / VIP
Local time
Today, 19:06
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 15:06
Joined
Nov 8, 2019
Messages
178
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

I’m here to help
Staff member
Local time
Today, 12:06
Joined
Oct 29, 2018
Messages
21,358
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

MVP / VIP
Local time
Today, 19:06
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 15:06
Joined
Nov 8, 2019
Messages
178
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

I’m here to help
Staff member
Local time
Today, 12:06
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 15:06
Joined
Nov 8, 2019
Messages
178
The site will not let me post a link due to my post count.
 

EzGoingKev

Registered User.
Local time
Today, 15:06
Joined
Nov 8, 2019
Messages
178
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:06
Joined
Sep 21, 2011
Messages
14,046
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

Registered User.
Local time
Today, 15:06
Joined
Nov 8, 2019
Messages
178
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.
 

Attachments

  • unconcat_sample.zip
    19.6 KB · Views: 110

theDBguy

I’m here to help
Staff member
Local time
Today, 12:06
Joined
Oct 29, 2018
Messages
21,358
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...
 

Attachments

  • unconcat_sample.zip
    28.5 KB · Views: 109

Users who are viewing this thread

Top Bottom