Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Francois

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Francois Caron View Post
    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"

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 ?
    Francois

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Don't forget that there is a Split function you can use to split your field with the , :
    MyArray = Split([MyField],",")
    Francois

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •