Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I am trying to extend my skills in Access Queries.

    I have some data as follows:

    AIDX String
    1 a
    1 m
    1 z
    1 w
    1 i
    2 a
    2 z
    3 q

    The output is:

    1 a, m, z, w, i
    2 a, z
    3 q

    Now I can do this easily in VB. Can I do with with an aggregate and no VB? Or is the closest I can get is using "Group By" with a VB function to build the string?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You do need VBA, but you could use a user-defined VBA function in a query. I have attached such a function in a text file to Post 301393.

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='761579' date='25-Feb-2009 16:25']You do need VBA, but you could use a user-defined VBA function in a query. I have attached such a function in a text file to Post 301393.[/quote]


    Do you need to loop through the rows or can you work with the Groups. For example if I do a total on Group By, can I do a function on a Group By? Or do I have to supply the code to loop through the records?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The function in the attachment to the post I referred to handles the looping for you.

    If you have a Totals query that groups by AIDX, you can create a calculated column like this:

    StringList: Concat("NameOfTable", "StringField", "AIDX=" & [AIDX])

    where NameOfTable is the name of the table containing the data and StringField is the name of the field whose values you want to concatenate.

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='763422' date='04-Mar-2009 15:14']The function in the attachment to the post I referred to handles the looping for you.

    If you have a Totals query that groups by AIDX, you can create a calculated column like this:

    StringList: Concat("NameOfTable", "StringField", "AIDX=" & [AIDX])

    where NameOfTable is the name of the table containing the data and StringField is the name of the field whose values you want to concatenate.[/quote]

    I was playing around with the group by and it seemed like it just wanted to work with it one row at a time, so no advantage over just looping through a recordset that is set up with a couple of fields in an order by.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='wejones' post='763452' date='04-Mar-2009 22:13']I was playing around with the group by and it seemed like it just wanted to work with it one row at a time, so no advantage over just looping through a recordset that is set up with a couple of fields in an order by.[/quote]
    Sorry, I don't understand your remark. If you wish, you can post a stripped down copy of your database. See post 401925 for instructions.

Posting Permissions

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