Results 1 to 11 of 11
  1. #1
    cfraser is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    3

    Retrieving multiple rows and putting into 1'string

    Hi Guys




    I have the following problem.


    I am very new to SQL and I am busy using microsoft access to learn the syntax.


    I am trying to write a query that selects multiple rows of a table and puts those into a single string with a comma delimiter.


    I want it to select all rows after a specific word is found in the row above and then stop selecting when another specific word below the last row is found.


    Example below: >>>


    The data i am using is very unorganised and has not headers, so I have to try and specific lines in order to allocate them to a specific user.


    Sample Data:


    ID FullContent
    459 Authorized Privileges:
    460 ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS
    461 CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP
    462 GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT
    463 NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL
    464 PRMMBX PSWAPM READALL SECURITY SETPRV SHARE
    465 SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX
    466 UPGRADE VOLPRO WORLD
    467 Default Privileges:


    Output Expected:


    "
    ACNT,ALLSPOOL,ALTPRI,AUDIT,BUGCHK,BYPASS,CMEXEC,CM KRNL,DIAGNOSE,DOWNGRADE,EXQUOTA,GROUP,GRPNAM,GRPPR V,IMPERSONATE,IMPORT,LOG_IO,MOUNT,NETMBX,OPER…etcc "


    The delimiter can be anything.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Will require VBA procedure. Review http://allenbrowne.com/func-concat.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    cfraser is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    3
    Quote Originally Posted by June7 View Post
    Will require VBA procedure. Review http://allenbrowne.com/func-concat.html
    Thanks for this.

    but I do not if it is what I need. I have 1780 users sitting in my database at the moment whose Privileges I need to extract, each user has a random amount of lines depending on what they have been given privileges to...

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The code doesn't care how many records are associated with each user. However, it's not going to work as-is with your data structure but it gives an idea of what is involved.

    Where is the user ID?

    You really need a properly structured database.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    cfraser is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    3
    That's the problem this is the only data I have....the user if is located in a line above the ones listed! This is the only way I can receive the data from a client's legacy system and I need to find a way of sorting it into a more manageable data.

    It's easy to extract usernames and password parameters because they are only made up of one line.

    So there is no way to extract multiples lies using the Authorized and Default Privileges as triggers to write those lines to a single string...

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You must know something about the data. Your sample isn't very helpful --we're still guessing as to what it means.
    What or where is the specific word?
    Somewhere in this text is a user or account followed by codes representing privileges.
    If you want to tell us more, or even make up some data that represents "something and related privileges", I'm sure we can help.

    I would suggest a recordset and vba with possible write to a textstream if the length is considerable.

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Also, how is the original data formatted? HTML, XML, XLS?

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You didn't show the user ID and password lines in the data sample. What you want can be done but it will involve complex code much like the example I referenced. However, code should be written to create properly normalized data structure, not a single string of text. The result would be something like:

    tblUsers
    UserID LastName FirstName Password
    1 Doe Jane ABC
    2 Smith John DEF

    tblPrivileges
    PrivID Desc Type
    1 ACNT Auth
    2 ALLSPOOL Auth
    3 ALTPRI Auth
    4 ? DEF

    tblUserPrivileges
    UserID PrivID
    1 1
    1 2
    1 3
    1 4
    1 5

    The first step toward normalizing this data would be to add UserID and Password and Type fields in the existing table and use code (or a lengthy and tedious manual edit) to populate those fields. Next steps would break up the table into the multiple tables described. Whether this should be 2 or 3 tables depends on the Type field. Is a privilege always Auth or Def regardless of the user?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    June,

    Do you know what the poster is really trying to accomplish? I agree that what you said here is normalized and great table structure. But I'm not picking up on that.

    From the sample and the wording in the post, and the desire for a CSV file, it seems to me that the poster is grasping and unsure of what is needed.
    We have no idea of the context in which the issue arose; where the OP fits into the picture; the severity of the issue and possible consequences - let alone format or SQL or Access. S/he is a self proclaimed novice learning, but has yet to tell us anything in detail about the problem/requirement.
    If the OP can't describe the issue in simple terms, there will be no meaningful focused advice. We're all guessing, and that's a poor and slow technique for problem solving.

    Note - I'm not challenging your response. I agree if that's what's required. If it is, then you get 100 points for best guess.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I responded to OP statement "selects multiple rows of a table and puts those into a single string with a comma delimiter".

    User has a table and they want to concatenate values from related records. Not an uncommon request, however, the presented data structure is not common.

    OP also references "client's legacy system" so I assumed the data structure is a holdover from another process that is no longer active.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Convert rows into string
    By KPABA in forum Queries
    Replies: 1
    Last Post: 06-06-2013, 06:03 PM
  2. Putting Rows in to columns
    By HowardlyDog in forum Queries
    Replies: 1
    Last Post: 06-29-2012, 01:39 PM
  3. Creating a String in VBA over multiple lines
    By Mazdaspeed6 in forum Programming
    Replies: 13
    Last Post: 01-03-2011, 12:32 PM
  4. Multiple Criteria in a string
    By cksm4 in forum Programming
    Replies: 3
    Last Post: 08-04-2010, 11:54 AM
  5. Replies: 5
    Last Post: 12-10-2009, 10:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums