Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page