Results 1 to 9 of 9
  1. #1
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Stumped on a query (2002)

    A client has asked me to do something that has me stumped. I have a nicely normalized table that makes an association between a person and another object, and that works fine on forms and the like. Howevere now they want me to create a "multi-value" field in a query that shows the ID numbers of the objects in a character string delimited with commas. I have a technique that works as long as there are two and only two associations by using the Min and Max functions. However in this case there can be up to four associations. So I have the following table entries:
    PersonIDs
    1
    2
    3
    ObjectIDs
    7
    8
    9
    10
    Association Records
    <table border=1><td>PersonID</td><td>ObjectID</td><td>1</td><td>7</td><td>1</td><td>9</td><td>2</td><td>8</td><td>2</td><td>9</td><td>2</td><td>10</td><td>3</td><td>7</td><td>3</td><td>8</td><td>3</td><td>9</td><td>3</td><td>10</td></table>
    What the client wants to see is
    <table border=1><td>PersonID</td><td>Object IDs</td><td>1</td><td>7,9</td><td>2</td><td>8,9,10</td><td>3</td><td>7,8,9,10</td></table>
    Any suggestions would be much appreciated - I'm trying to avoid techniques such as using a make table query, or writing a whole bunch of VBA code and doing lots of recursive processing. thanks in advance.
    Wendell

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Stumped on a query (2002)

    Have at look at the attached sample.

    It uses the concat function to do what I think you want.
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stumped on a query (2002)

    Note: the Concat function used by John is from the Lounge - see <post:=301,393>post 301,393</post:>.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Stumped on a query (2002)

    Sorry

    I should have made sure that credit was shown in my post.
    Regards
    John



  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stumped on a query (2002)

    That's OK. I posted the link because the thread referred to provides some additional information.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Stumped on a query (2002)

    Thanks for digging into this - I'm a bit embarrased <img src=/S/blush.gif border=0 alt=blush width=15 height=15> that I didn't think to search the lounge. The concat function works fine as long as I'm doing this in Access, but the client really would like this as a SQL Server view, which means I don't have VBA as an option - only TSQL or resorting to C# and UDFs. There are techniques using CASE statements and GROUP BY (see <!mskb=175574>Microsoft Knowledge Base Article 175574<!/mskb>) where you have a small set of objects that are possible - something akin to a CrossTab query, but in this case there are several hundred objects and some 40000 people records. Another issue that comes out of that is performance - they would like a query where they can simply scroll up and down the list. Hmmmm - perhaps we need to revist the requirements. Thanks again for your help.
    Wendell

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Stumped on a query (2002)

    I have just re-read your original post and this have given me a new idea. I don't know if it is any use.

    You say you can get the Max and Min using queries, but there can be up to four associations.

    Find the Max and MIn, exclude them from the records, then find the Max and MIn from what is left , then join them all into another query.

    I attach another demo that does this. Sorry about the query names, but I could not think of good names.

    qryfinal returns the same results as qryconcat without using the concat function.
    Regards
    John



  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Stumped on a query (2002)

    I toyed with that approach for about 30 seconds and decided it was too complicated. But you've done a pretty decent job of putting it together - my only concern is what happens when you have 1 or 3. I'll play with the data and see what I get. Thanks again.

    Update: As I feared, if you have 3 entries, it returns the middle one twice. Not obvious to me at this point how to eliminate that problem.
    Wendell

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Stumped on a query (2002)

    Max and Min are the same when there is only 1 entry. With only 1 this occurs in the first round, with 3 in the second round.

    A solution I have not tried to implement is to extend the exclusion even more. Find the Min then exclude them. Find the Min again, then exclude these. Do it again, then find the Min of what is left. [i] added later...At the final step you don't need to find the MIn- only Min values are left so you can just use them.[/]

    This would avoid the need for the Union query
    Regards
    John



Posting Permissions

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