Reducing rows in sql query (1 Viewer)

[lawman]

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))

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.

 

[pbaldy]

An oft-referenced option:

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

It could be modified to give you the + and count I would think.

 

[arnelgp]

What if you re arrange the declaration of your tables:

SELECT Client.Surname, Case.Reference FROM [ClientCase-Join] INNER JOIN (Client INNER JOIN [Case] ON Client.[ID] = [ClientCase-Join].[ClientID]) ON Case.[ID] = [ClientCase-Join].[CaseID];

 

[plog]

If possible, it would also be good to have a number returned and stored in a field in Case table. eg. Case.NumberClients = 3

First, that's not how databases work. You don't save values that are calculable. Instead, you calculate them in a query and refer to your query for those values.

Second, only use alphanumeric characters in table/field names. "ClientCase-Join" is a poor name because of that dash. It just makes coding and querying more difficutl. I suggest "ClientsCases"

Finally, to achieve what you want, you will need a subquery:

SELECT [ClientCase-Join].CaseID, Min([ClientCase-Join].ClientID) AS MinClient, Count([ClientCase-Join].ClientID) AS Clients
FROM [ClientCase-Join]
GROUP BY [ClientCase-Join].CaseID;

Paste that into a new query and name it 'sub1'. It determines what surname to show for each Reference and lets you know which ones have multiples. Then the below query will give you the results you want:

SELECT [Surname] & IIf([Clients]>1,"+") AS SurnamePlus, Case.Reference
FROM ([Case] INNER JOIN sub1 ON Case.CaseID = sub1.CaseID) INNER JOIN Client ON sub1.MinClient = Client.ClientID;

 

[lawman]

Plog.

That works perfectly.

Thanks