Looking at your dB, you were very close.
Here is the raw data for both areas:
Here is your query and a modified version of the same query to change "continuing" to "ongoing"
Here is the SQL of the modified query:
Code:
SELECT DISTINCT Sheet1.CommunityName,
IIf(Min([Q-1])="Continuing","Ongoing",Min([Q-1])) AS Qtr1,
IIf(Min([Q-2])="Continuing","Ongoing",Min([Q-2])) AS Qtr2,
IIf(Min([Q-3])="Continuing","Ongoing",Min([Q-3])) AS Qtr3,
IIf(Min([Q4])="Continuing","Ongoing",Min([Q4])) AS Qtr4
FROM Sheet1
GROUP BY Sheet1.CommunityName;
BTW, in the table "Sheet1", the first field is named "NO".
"NO" is a reserved word in Access (Jet) and shouldn't be used as object names. Better would be "Num", "ID", "LocID"..........