CODEWITHSUNDEEP
ms-accessms-access-2007
Manas Saha
Manas Saha

Reputation: 1497

Need to split delimited string into seperate rows in ms-access Sub form

I have a form in MS-Access which shows tabular data. as follows

record id     record date    record content
---------     -----------    --------------
   1           1/2/2011      name: ben, age:38, sex: M; name: emma, age:32, sex: F
   2           5/5/2012      name: john, age:28, sex: M; name: eva, age:24, sex: F

There is no limit in how many people's records can be there in each record content cell. but each record will have only those 3 fields. Name, Age and Sex.

I need to split the record content in a subform such a way that it looks like:

record id     record date    record content
---------     -----------    --------------
   1           1/2/2011      name   age  sex
                             ----   ---  ---
                             ben    38    M
                             emma   32    F

   2           5/5/2012      name   age  sex
                             ----   ---  ---
                             john   28    M
                             Eva    24    F

What is the easiest way to achieve this? The table from where the record is coming has the data in similar format as shown in the first diagram. How can I split this compound string and display it in multiple rows?

Upvotes: 0

Views: 1306

Answers (1)

Yawar
Yawar

Reputation: 11607

You need to normalise your data--split the source table up into two.

(How? You need to write some VBA code to:

  • read each row of your records table
  • save the value of the [record id] column into a variable you will use later in this loop
  • for each value of the [record content] column, get the value into a string
  • split the string using the delimiter ";". That is, semi-colon. Use the split() function. See Split strings in excel (vba) for an example. You will get back a list of strings. Each string in the list will be like this: "name: xyz, age: xyz, sex: xyz"
  • extract your data from this string using a regular expression. See http://mark.biek.org/blog/2009/01/regular-expressions-in-vba/ for an example in Access.

  • this regular expression will probably get you the name, age and sex out of the string as the first, second and third matched items:

    "^.*name:\s*([^,]+),\s*age:\s*([^,]+),\s*sex:\s*([.*])$"

  • after doing the regex match you grab the matched items, put them into name, age and sex variables, and use these values and the record id you saved earlier to insert into the new People table.)

Tables:

People

ID

RecordID - references Records.ID

PersonName

Age

Sex

Records

ID

RecordDate

After that you can use a sub-form within your current main form to display the people associated with each record. Access makes that pretty easy if you have a parent-child relationship between two tables, as you do above.

Upvotes: 2

Related Questions