Split() Function or something like it

[JRS_]

Hi,

I have a data table that has 2 fields that I need to separate out. I think I can use the split() function but I don't know how to populate fields with the result. I am trying to use the split funtion in a module, but I also don't really know how to do that.

Any help is greatly appreciated!!

FirstField has an unknown number of phrases. SecondField has an unknown number of numbers that correspond to the number of phrases. The data looks like:

ID | FirstField | SecondField
1 | PhraseA, PhraseB, PhraseC | 1, 2, 3
2 | PhraseB, PhraseA | 2, 2

I want it to look like:



ID | Phrase1 | Number1 | Phrase2| Number2 | Phrase3 | Number3 |
1 | PhraseA | 1 | PhraseB | 2 | PhraseC | 3
2 | PhraseB | 2 | PhraseA | 2 | |

[RuralGuy]

If you normalized your table, the task would be done.

[dreday]

Is this something you need to do continuously? If not, you might be better off looking at the table in excel and using the Text to Column feature to separate things out

[JRS_]

I don't think Text to Columns will work - it's not a one-time data dump & I need to set up a process for someone else that involves as little data manipulation as possible.

In terms of normalizing the db, is there a way that I can separate out the fields on data import? The data is coming to me in an Excel spreadsheet & will be imported into Access.

I appreciate the help. I'm stumped.

[RuralGuy]

In terms of normalizing the db, is there a way that I can separate out the fields on data import? The data is coming to me in an Excel spreadsheet & will be imported into Access.

I appreciate the help. I'm stumped.

The multiple "fields" in one field should actually be multiple records if the table were normalized.

[JRS_]

Unfortunately, I do not have control over the way in which the data arrives. The data comes in an Excel spreadsheet that I import into Access. It arrives in the first format in the example above and I have to parse it out somehow. I can parse it into a second data table that joins to the main table, but I haven't figured out how to do that. I was thinking I might be able to use the Split() function, but I don't know how to use an array to populate a data table. Can that be done?

[boblarson]

Unfortunately, I do not have control over the way in which the data arrives.

That's not the point RG was trying to make. The way the data comes in, is not the main issue. The main issue is what you have said you want to do with it. Setting it up like:

ID | Phrase1 | Number1 | Phrase2| Number2 | Phrase3 | Number3 |
1 | PhraseA | 1 | PhraseB | 2 | PhraseC | 3
2 | PhraseB | 2 | PhraseA | 2 | |

is NOT NORMALIZED

It SHOULD BE something like
(ItemID is autonumber and PK)

ItemID    |   ID   |    Phrase    |   Num
    1            1         Phrase A        1
    2            1         Phrase B        2
    3            1         Phrase C        3
    4            2         Phrase B        2
    5            2         Phrase A        2

and so on.

And that is fairly easy to code.

[JRS_]

Ah, I see what you're saying regarding the table structure...Can you pls point me to some code snippets or a tutorial or something like that which could help me? After I do the initial import, I don't know how to take the data from those couple of fields and turn that into a different data table. Thank you.

[boblarson]

So the table it is going to eventually be going in is not going to be a new table each time (or at least it shouldn't be). Can you describe a little better as to what the whole process is supposed to accomplish? That might yield some better suggestions.

[JRS_]

Thanks for working through this with me.

Users have multiple options, worded in phrases, that they select. Then they associate the number of whatevers in the next column. The data currently look like:
ID | Phrases | Numbers
1 | 1: Sustainable farms., 2: High-yield corn variants. | 2, 3
2 | 1: Sustainable farms., 2: High-yield corn variants. | 1, 1

I need to be able to split up the phrases and numbers, then aggregate the data so I can report out that we have 3 sustainable farms and 4 high-yield corn variants. I will also need to generate other stats based on successfully splitting out the data in these fields.

I will receive the data in an excel spreadsheet, then was planning to import it into Access & generate reports here. The phrases aren't unlimited, but there aren't a consistent number of characters to expect in the phrases so I can't parse it out on import using the wizard. The numbers, also, could be single or double digit in varying order, so the import wizard parsing tool can't be used here either.

Once the data is imported into an Access table from the excel sheet, can I set Access up to automatically run a query or something like it that creates or populates a second table with the data in the Phrases and Numbers fields normalized? I initially thought I'd be able to use the split function to parse out the fields, but I (clearly) don't know how to make it work.

Thanks!

[boblarson]

How about posting a sample Excel sheet exactly like you would get it and with about 20 records in it. Then I can see what I can come up with.

[JRS_]

Here's the sample. Please bear with my examples, I have the structure of the file, but no real data. Thank you!!

[JRS_]

Hi there - I was hoping Bob or someone may have had the chance to take a look at my fine mess & offer a suggestion about how to normalize the data from the attached excel sheet. Thanks in advance & happy holidays!

[boblarson]

Sorry, I'm trying to figure out what output you are going to need from this as the spreadsheet is a bit different than your first simplified example.

[JRS_]

Thanks Bob! Hopefully this is an interesting problem for you to work out.