Create multiple rows if field has substrings

Topic

New Reply

I have table containing information about plots of land, each having an alphanumeric ID e.g. 2a.

One of the columns decribes the reason the plot is required for the project. This may be a single reason or a few (in the data I’m looking there are no more than 3 but there could be more). Multiple reason would be written as a sentence, not a delimited list, e.g.

“THIS, THAT and THE OTHER”

Based on the number of substrings (in the example there are 3) I need to make a new row in a new table. For each row the PLOT ID would be modified like so

2a(i) THIS
2a(ii) THAT
2a(iii) THE OTHER

All other fields (there are <20) need to copied

Is there any way I can do this with queries or am I looking at coding some recordset?

Reply | Quote

Replies

As your multiple reason is a sentence and not a delimited list how would you determine what the different substrings are in a query (I don’t see how) or in code ?
In your sample why should it not be :

2a(i) THIS
2a(ii) THAT
2a(iii) and
2a(iv) THE
2a(v) OTHER

Reply | Quote

As your multiple reason is a sentence and not a delimited list how would you determine what the different substrings are in a query (I don’t see how) or in code ?
In your sample why should it not be :

2a(i) THIS
2a(ii) THAT
2a(iii) and
2a(iv) THE
2a(v) OTHER

First I’ll have to look for commas. In the example above this would give “THIS” and “THAT and THE OTHER”

Then in the last (in this case second) substring, look for “* and *” to give “THAT” and “THE OTHER”

Reply | Quote

Then you have indeed some structure in this field so you can split it. But as the delimiters are , and ‘and’ (two different) you certainly have to do it in code.

Second thing : Why create a new table with 20 fields where all of them, excepted one, will be duplicated ? Wouldn’t it be better to create a table with three fields, the splitted field , the new alphanumeric ID and the primary field of your first table, and work with relations and queries ?

Reply | Quote

I get what you mean about the table. I really only need to make a new table with 3 fields – the original ID (to join back to the other info), the new ID (with i, ii etc.) and the single requirement. I will ultimately need to make a table with all the data though.

I have a bunch of tables whose data I have to get into excel to be put in the most ‘un-normalised’ structure you could imagine. This excel format was created by someone for a mail merge to ‘save time’. The amount of effort required to get data into the excel format is unreal or, perhaps even surreal!

I found this easier to do by working in excel and getting the information from the database via DAO. There have been plenty of problems, not in the least the issue of different characters for carriage returns in the two applications.

As this was something that will happen once and at the end of the project, I was easier to create a bunch of dump tables with appropriate excel friendly data.

Reply | Quote

Don’t forget that there is a Split function you can use to split your field with the , :
MyArray = Split([MyField],”,”)

Reply | Quote