How to Consolidate Rows?

[AccessHunter]

CASE_ID BEFORE BETWEEN AFTER	   
53222     N       N      Y
53222     Y       N      N
53222     Y       N      N
53222     Y       N      N
53222     Y       N      N
53222     Y       N      N
53222     Y       N      N
54233     Y       N      N
54233     Y       N      N
54233     Y       N      N
54233     Y       N      N
54375     Y       N      N
54375     Y       N      N
54375     Y       N      N
54375     Y       N      N
54375     Y       N      N
54375     Y       N      N
54375     Y       N      N
54375     Y       N      N
54992     Y       N      N
54992     Y       N      N
54992     Y       N      N
54992     Y       N      N
54992     Y       N      N
54992     Y       N      N
54992     Y       N      N

Please help.

I have a query with the above data. I want to consolidate it and have a row for each distinct Case_ID based on the following condition,

BEFORE column = 'Y', if any of the rows for that Case_ID has a 'Y', else BEFORE column = 'N'

BETWEEN column = 'Y', if any of the rows for that Case_ID has a 'Y', else BEFORE column = 'N'

AFTER column = 'Y', if any of the rows for that Case_ID has a 'Y', else BEFORE column = 'N'.

So for Case_ID = 53222 abd 54233, the consolidated rows should look like,

53222, Y, N, Y
54233, Y, N, N

Thanks in advance

[NeoPa]

Try :

SELECT [Case_ID],
       Max([Before]) AS [MaxBefore],
       Max([Between]) AS [MaxBetween],
       Max([After]) AS [MaxAfter]
FROM [Query]
GROUP BY [Case_ID]

BTW I'm assuming your post should have read :

BEFORE column = 'Y', if any of the rows for that Case_ID has a 'Y', else BEFORE column = 'N'

BETWEEN column = 'Y', if any of the rows for that Case_ID has a 'Y', else BETWEEN column = 'N'

AFTER column = 'Y', if any of the rows for that Case_ID has a 'Y', else AFTER column = 'N'.

[AccessHunter]

It worked, thank you so much.

[NeoPa]

No problem :)
Pleased to help.