Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2007
    Posts
    1

    Unanswered: Split one record in Access table to many records based on a field?

    Can someone help me with the code to split one record in an access table to many records given an account number field?

    Example data:
    Fields within Access table

    Name Address Acct Number
    Mary Smith 111 Smith Street 1234, 4557, 2346

    Results I want:
    Mary Smith 111 Smith Street 1234
    Mary Smith 111 Smith Street 4557
    Mary Smith 111 Smith Street 2346

    Thank you!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    My question would be why isn't 1234, 4557, 2346 in a relational table joined in a one-to-many relationship? This would make your life a lot easier.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    mmmmmmmmmmm
    This is a Bit of a hard one only cause Where is the Starting point

    A quick look cccccccccc
    Split the Line by the ,

    data = "Mary Smith 111 Smith Street 1234, 4557, 2346"
    parts = split(data,",",-1,1)

    Parts(0) = "Mary Smith 111 Smith Street 1234"
    Parts(1) = "4557"
    Parts(2) = "2346"

    so that part was easy how mmmmmmmmmmmmmmmm
    could split the 1st array again by the spacees and them take the last array

    Ndata = Parts(0)
    Nparts = split(Ndata," ",-1,1)

    lastone = Nparts(Ubound(Nparts))

    data = replace(data,lastone,"") take the number out of the time


    data should say = "Mary Smith 111 Smith Street"

    put the accin the first arry

    part(0) = lastone

    now
    data = "Mary Smith 111 Smith Street"
    parts(0) = "1234"
    Parts(1) = "4557"
    Parts(2) = "2346"

    know you can add the data to a database

    zz = ubound(Parts)

    for aa = 0 to zz
    add record
    feild(1) = data & " " & Parts(aa)
    update
    next


    above is just off the top of my head testing is needed

    The good time about it is the is arry so it should work in no matter how many acc number there are...
    Last edited by myle; 10-23-07 at 15:38.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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