The query below has the potential to produce duplicate values in the alias field. Is there a way to tweak this query, so when a produced value (in this case |Bayer|) repeats, the output value only appears once in the AssociatedManufacturers field? I understand that the table is not normalized, but I must work with the table I have.
Table:
| ClientID | Midol | Aleve | Tylenol |
|---|---|---|---|
| 1 | Yes | Yes | Yes |
| 2 | Yes | Yes | No |
Query:
SELECT DrugsTaken.ClientId, IIf([Midol]="Yes", " |Bayer| ",Null) & IIf([Aleve]="Yes"," |Bayer| ",Null) & IIf([Tylenol]="Yes", " |J&J| ",Null) AS AssociatedManufacturers
FROM DrugsTaken;
As written, the result of the query for ClientID 1 is: "|Bayer| |Bayer| |J&J|", but I'd like it to be "|Bayer| |J&J|".
Help with the query would be greatly appreciated!
CodePudding user response：
Test if Midol OR Aleve = Yes.
IIf([Midol]="Yes" Or [Aleve]="Yes"," |Bayer| ",Null) & IIf([Tylenol]="Yes", " |J&J| ",Null)