Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

record splitting

Discussion in 'Information Technology' started by deco, Jul 28, 2006.

  1. deco

    deco
    Expand Collapse
    Guest

    Hi,

    I receive via Excel a report about documents. It list several fields, like
    doc_name, doc_id, …
    A document can be linked to several clients. For instance, a document doc_A
    can be linked to 3 customers, client_1, client_2, client_3. In my report, the
    clients linked to a document will be grouped in one field, separated by
    commas. For record doc_A, it will be client_1, client_2, client_3.

    With one record of my document report, I must create one record per client.
    For instance I have :

    doc_id : doc_A
    doc_client : client_1, client_2, client_3

    I must get 3 records in a separate table:

    doc_id : doc_A
    doc_client : client_1

    doc_id : doc_A
    doc_client : client_2

    doc_id : doc_A
    doc_client : client_3

    Could you please help me on that problem.

    Rds
    Marco
     
  2. Loading...


  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 14 Jun 2006 10:19:02 -0700, deco
    <deco@discussions.microsoft.com> wrote:

    >doc_id : doc_A
    >doc_client : client_1, client_2, client_3
    >
    >I must get 3 records in a separate table:
    >
    >doc_id : doc_A
    >doc_client : client_1
    >
    >doc_id : doc_A
    >doc_client : client_2
    >
    >doc_id : doc_A
    >doc_client : client_3
    >
    >Could you please help me on that problem.


    A "Normalizing Union Query" is the ticket here. Let's assume you have
    either linked or imported the spreadsheet as tblSource, and that you
    have a properly normalized table with the two fields (or perhaps more,
    the logic is the same) named tblDest.

    Create a Query in the SQL window (the query grid can't do it):

    SELECT tblSource.Doc_ID, tblSource.Client_1 AS [Doc_Client]
    FROM tblSource
    WHERE tblSource.Client_1 IS NOT NULL
    UNION ALL
    SELECT tblSource.Doc_ID, tblSource.Client_2 AS [Doc_Client]
    FROM tblSource
    WHERE tblSource.Client_2 IS NOT NULL
    UNION ALL
    SELECT tblSource.Doc_ID, tblSource.Client_3 AS [Doc_Client]
    FROM tblSource
    WHERE tblSource.Client_3 IS NOT NULL;

    Save this query as uniAllClients. Then base an Append query on it to
    populate tblDest:

    INSERT INTO tblDest
    (Doc_ID, Doc_Client)
    SELECT Doc_ID, Doc_Client
    FROM uniAllClients;


    John W. Vinson[MVP]
     
  4. deco

    deco
    Expand Collapse
    Guest

    HI,

    Many thanks for your help. The problem is that in my document report, all
    clients are specified in only one field, doc_client, separated by a comma :
    customer_a, customer_b, customer_c, .... They are not into separated fields
    client_1, client_2, ...

    That's the key point here.

    Rds
    Marco

    "John Vinson" wrote:

    > On Wed, 14 Jun 2006 10:19:02 -0700, deco
    > <deco@discussions.microsoft.com> wrote:
    >
    > >doc_id : doc_A
    > >doc_client : client_1, client_2, client_3
    > >
    > >I must get 3 records in a separate table:
    > >
    > >doc_id : doc_A
    > >doc_client : client_1
    > >
    > >doc_id : doc_A
    > >doc_client : client_2
    > >
    > >doc_id : doc_A
    > >doc_client : client_3
    > >
    > >Could you please help me on that problem.

    >
    > A "Normalizing Union Query" is the ticket here. Let's assume you have
    > either linked or imported the spreadsheet as tblSource, and that you
    > have a properly normalized table with the two fields (or perhaps more,
    > the logic is the same) named tblDest.
    >
    > Create a Query in the SQL window (the query grid can't do it):
    >
    > SELECT tblSource.Doc_ID, tblSource.Client_1 AS [Doc_Client]
    > FROM tblSource
    > WHERE tblSource.Client_1 IS NOT NULL
    > UNION ALL
    > SELECT tblSource.Doc_ID, tblSource.Client_2 AS [Doc_Client]
    > FROM tblSource
    > WHERE tblSource.Client_2 IS NOT NULL
    > UNION ALL
    > SELECT tblSource.Doc_ID, tblSource.Client_3 AS [Doc_Client]
    > FROM tblSource
    > WHERE tblSource.Client_3 IS NOT NULL;
    >
    > Save this query as uniAllClients. Then base an Append query on it to
    > populate tblDest:
    >
    > INSERT INTO tblDest
    > (Doc_ID, Doc_Client)
    > SELECT Doc_ID, Doc_Client
    > FROM uniAllClients;
    >
    >
    > John W. Vinson[MVP]
    >
    >
     
  5. BD

    BD
    Expand Collapse
    Guest

    Do an import before into a table and named it "tblSource" and after
    that do what John Vinson said.

    []'s
    BD

    On Thu, 15 Jun 2006 00:58:02 -0700, deco
    <deco@discussions.microsoft.com> wrote:

    >HI,
    >
    >Many thanks for your help. The problem is that in my document report, all
    >clients are specified in only one field, doc_client, separated by a comma :
    >customer_a, customer_b, customer_c, .... They are not into separated fields
    >client_1, client_2, ...
    >
    >That's the key point here.
    >
    >Rds
    >Marco
     
  6. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Is it always 3 records in doc_client?

    If so, you should be able to use

    SELECT Doc_ID, Trim(Split(Client_1, ",")(0)) AS [Doc_Client]
    FROM tblSource
    UNION ALL
    SELECT Doc_ID, Trim(Split(Client_1, ",")(1)) AS [Doc_Client]
    FROM tblSource
    UNION ALL
    SELECT Doc_ID, Trim(Split(Client_1, ",")(2)) AS [Doc_Client]
    FROM tblSource

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "deco" <deco@discussions.microsoft.com> wrote in message
    news:1E7BBF4B-C387-4C89-BB48-843D942D4EB8@microsoft.com...
    > HI,
    >
    > Many thanks for your help. The problem is that in my document report, all
    > clients are specified in only one field, doc_client, separated by a comma
    > :
    > customer_a, customer_b, customer_c, .... They are not into separated
    > fields
    > client_1, client_2, ...
    >
    > That's the key point here.
    >
    > Rds
    > Marco
    >
    > "John Vinson" wrote:
    >
    >> On Wed, 14 Jun 2006 10:19:02 -0700, deco
    >> <deco@discussions.microsoft.com> wrote:
    >>
    >> >doc_id : doc_A
    >> >doc_client : client_1, client_2, client_3
    >> >
    >> >I must get 3 records in a separate table:
    >> >
    >> >doc_id : doc_A
    >> >doc_client : client_1
    >> >
    >> >doc_id : doc_A
    >> >doc_client : client_2
    >> >
    >> >doc_id : doc_A
    >> >doc_client : client_3
    >> >
    >> >Could you please help me on that problem.

    >>
    >> A "Normalizing Union Query" is the ticket here. Let's assume you have
    >> either linked or imported the spreadsheet as tblSource, and that you
    >> have a properly normalized table with the two fields (or perhaps more,
    >> the logic is the same) named tblDest.
    >>
    >> Create a Query in the SQL window (the query grid can't do it):
    >>
    >> SELECT tblSource.Doc_ID, tblSource.Client_1 AS [Doc_Client]
    >> FROM tblSource
    >> WHERE tblSource.Client_1 IS NOT NULL
    >> UNION ALL
    >> SELECT tblSource.Doc_ID, tblSource.Client_2 AS [Doc_Client]
    >> FROM tblSource
    >> WHERE tblSource.Client_2 IS NOT NULL
    >> UNION ALL
    >> SELECT tblSource.Doc_ID, tblSource.Client_3 AS [Doc_Client]
    >> FROM tblSource
    >> WHERE tblSource.Client_3 IS NOT NULL;
    >>
    >> Save this query as uniAllClients. Then base an Append query on it to
    >> populate tblDest:
    >>
    >> INSERT INTO tblDest
    >> (Doc_ID, Doc_Client)
    >> SELECT Doc_ID, Doc_Client
    >> FROM uniAllClients;
    >>
    >>
    >> John W. Vinson[MVP]
    >>
    >>
     
  7. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    I just realized that John Nurick's "SafeSplit" function would be useful
    here if you don't always have exactly 3 records.

    The function is defined as:

    Public Function SafeSplit(V As Variant, _
    Delim As String, Item As Long) As Variant

    On Error Resume Next 'to return Null if Item is out of range
    SafeSplit = Split(V, Delim)(Item)
    End Function

    You'd then change your query to:

    SELECT Doc_ID, Trim(SafeSplit(Client_1, ",", 0)) AS [Doc_Client]
    FROM tblSource
    UNION ALL
    SELECT Doc_ID, Trim(SafeSplit(Client_1, ",", 1)) AS [Doc_Client]
    FROM tblSource
    UNION ALL
    SELECT Doc_ID, Trim(SafeSplit(Client_1, ",", 2)) AS [Doc_Client]
    FROM tblSource



    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:%23CRJu$FkGHA.836@TK2MSFTNGP02.phx.gbl...
    > Is it always 3 records in doc_client?
    >
    > If so, you should be able to use
    >
    > SELECT Doc_ID, Trim(Split(Client_1, ",")(0)) AS [Doc_Client]
    > FROM tblSource
    > UNION ALL
    > SELECT Doc_ID, Trim(Split(Client_1, ",")(1)) AS [Doc_Client]
    > FROM tblSource
    > UNION ALL
    > SELECT Doc_ID, Trim(Split(Client_1, ",")(2)) AS [Doc_Client]
    > FROM tblSource
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "deco" <deco@discussions.microsoft.com> wrote in message
    > news:1E7BBF4B-C387-4C89-BB48-843D942D4EB8@microsoft.com...
    >> HI,
    >>
    >> Many thanks for your help. The problem is that in my document report, all
    >> clients are specified in only one field, doc_client, separated by a comma
    >> :
    >> customer_a, customer_b, customer_c, .... They are not into separated
    >> fields
    >> client_1, client_2, ...
    >>
    >> That's the key point here.
    >>
    >> Rds
    >> Marco
    >>
    >> "John Vinson" wrote:
    >>
    >>> On Wed, 14 Jun 2006 10:19:02 -0700, deco
    >>> <deco@discussions.microsoft.com> wrote:
    >>>
    >>> >doc_id : doc_A
    >>> >doc_client : client_1, client_2, client_3
    >>> >
    >>> >I must get 3 records in a separate table:
    >>> >
    >>> >doc_id : doc_A
    >>> >doc_client : client_1
    >>> >
    >>> >doc_id : doc_A
    >>> >doc_client : client_2
    >>> >
    >>> >doc_id : doc_A
    >>> >doc_client : client_3
    >>> >
    >>> >Could you please help me on that problem.
    >>>
    >>> A "Normalizing Union Query" is the ticket here. Let's assume you have
    >>> either linked or imported the spreadsheet as tblSource, and that you
    >>> have a properly normalized table with the two fields (or perhaps more,
    >>> the logic is the same) named tblDest.
    >>>
    >>> Create a Query in the SQL window (the query grid can't do it):
    >>>
    >>> SELECT tblSource.Doc_ID, tblSource.Client_1 AS [Doc_Client]
    >>> FROM tblSource
    >>> WHERE tblSource.Client_1 IS NOT NULL
    >>> UNION ALL
    >>> SELECT tblSource.Doc_ID, tblSource.Client_2 AS [Doc_Client]
    >>> FROM tblSource
    >>> WHERE tblSource.Client_2 IS NOT NULL
    >>> UNION ALL
    >>> SELECT tblSource.Doc_ID, tblSource.Client_3 AS [Doc_Client]
    >>> FROM tblSource
    >>> WHERE tblSource.Client_3 IS NOT NULL;
    >>>
    >>> Save this query as uniAllClients. Then base an Append query on it to
    >>> populate tblDest:
    >>>
    >>> INSERT INTO tblDest
    >>> (Doc_ID, Doc_Client)
    >>> SELECT Doc_ID, Doc_Client
    >>> FROM uniAllClients;
    >>>
    >>>
    >>> John W. Vinson[MVP]
    >>>
    >>>

    >
    >
     
  8. deco

    deco
    Expand Collapse
    Guest

    HI,

    It did work perfectly. Many thanks y'all for your help.

    Rds
    Marco

    "deco" wrote:

    > Hi,
    >
    > I receive via Excel a report about documents. It list several fields, like
    > doc_name, doc_id, …
    > A document can be linked to several clients. For instance, a document doc_A
    > can be linked to 3 customers, client_1, client_2, client_3. In my report, the
    > clients linked to a document will be grouped in one field, separated by
    > commas. For record doc_A, it will be client_1, client_2, client_3.
    >
    > With one record of my document report, I must create one record per client.
    > For instance I have :
    >
    > doc_id : doc_A
    > doc_client : client_1, client_2, client_3
    >
    > I must get 3 records in a separate table:
    >
    > doc_id : doc_A
    > doc_client : client_1
    >
    > doc_id : doc_A
    > doc_client : client_2
    >
    > doc_id : doc_A
    > doc_client : client_3
    >
    > Could you please help me on that problem.
    >
    > Rds
    > Marco
    >
     

Share This Page