MS Acces query to populate cell with column header text where that column's value = "Yes&q-CodePudding

I have a table that looks like this:

Patient Name	Injury A	Injury B
Bob	Yes	Yes
Mary	Yes	No

I'd like to write a query that populates a column, concatenating the Injury header text, separated by commas, where there is a "Yes" in the one of the Injury columns, as shown below.

Patient Name	Injury A	Injury B	All Injuries
Bob	Yes	Yes	Injury A, Injury B
Mary	Yes	No	Injury A

Any help is appreciated.

CodePudding user response：

Use IIf() expression.

SELECT Table1.PatientName, Table1.InjuryA, Table1.InjuryB, 
IIf([InjuryA]="Yes","InjuryA",Null) & IIf([InjuryA]="Yes" And [InjuryB]="Yes",", ",Null) & IIf([InjuryB]="Yes","InjuryB",Null) AS AllInjuries
FROM Table1;