Identify by Product Category

[ba4crm]

Hello,
Using Access 2007, I have a table with 2 rows. Group and Product. A group can have one product (PPO or HMO), or both. I would like to get a new column that tells me if said group has both, HMO only, or PPO only.
From:
Group Product
A HMO
A PPO
B PPO
B HMO
C PPO
D HMO
E PPO
F PPO

To
Group Product
A Both
B Both
C PPO Only
D HMO Only
E PPO Only
F PPO Only

I have tried running multiple queries to split up the products into individual columns, without success.

Thanks

 

[MazeWorX]

There may be an easier way but 2 queries

SELECT tblProduct.Group, Min(IIf([Product]="HMO",IIf([Product]="PMO","Both",[Product]),[Product])) AS Test, Max(IIf([Product]="HMO",IIf([Product]="PMO","Both",[Product]),[Product])) AS Test2
FROM tblProduct
GROUP BY tblProduct.Group;

and the second based on the first of coarse change the names to suit

SELECT Query1.Group, IIf([Test]=[Test2],[Test],"Both") AS Group2
FROM Query1

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

Sorry posted the wrong first query

SELECT tblProduct.Group, Min(tblProduct.Product) AS Test, Max(tblProduct.Product) AS Test2
FROM tblProduct
GROUP BY tblProduct.Group

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[PHV]

Another way with a single query:

SELECT [Group], 'Both' AS Product FROM tblProduct GROUP BY [Group] HAVING Count(*)>1
UNION SELECT [Group], Min(Product) FROM tblProduct GROUP BY [Group] HAVING Count(*)=1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886