Results 1 to 2 of 2
  1. #1
    lawman is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2020
    Posts
    4

    Reducing rows in result

    I have below query, with 3 tables


    1. Client table - (fields = ID (primary) , Surname)
    2. Case table - (fields = ID (primary), Reference)
    3. ClientCase-Join - joining table (fields = ID (primary), ClientID (foreign), CaseID (foreign))


    Code:
    SELECT Client.Surname, Case.Reference
    FROM [Case]
    INNER JOIN (Client
    INNER JOIN [ClientCase-Join]
    ON Client.[ID] = [ClientCase-Join].[ClientID])
    ON Case.[ID] = [ClientCase-Join].[CaseID];
    ClientCase-Join lists all the clients in each case, using foreign keys to the primary keys of tables Client, Case.


    If I have 3 Clients AA,BB,CC in Case 11 I get 3 rows showing each Client name next to Case.


    ie.
    Client - Case
    AA, 11
    BB, 11
    CC, 11
    ** Where I have multiple Clients in a Case, I would just like it to show a single row of the first Client name with '+' sign next to it, indicating multi client case.


    ie.
    Clients - Case
    AA+, 11
    ** If possible, it would also be good to have a number returned and stored in a field in Case table. eg. Case.NumberClients = 3


    Thanks in advance as Sql confuses the heck out of this newbie.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    As posted elsewhere:

    An oft-referenced option:

    http://allenbrowne.com/func-concat.html

    It could be modified to give you the + and count I would think.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Query result in rows
    By Peter3 in forum Queries
    Replies: 9
    Last Post: 12-16-2017, 02:18 AM
  2. Replies: 4
    Last Post: 11-30-2014, 08:03 AM
  3. Replies: 3
    Last Post: 04-30-2014, 11:19 AM
  4. Replies: 4
    Last Post: 04-22-2014, 08:23 AM
  5. Replies: 1
    Last Post: 12-03-2011, 01:26 AM

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