CODEWITHSUNDEEP
sqlms-access
Shashank
Shashank

Reputation: 6337

selecting data in access by conditional merging

I have an Access query which return data in the form of:

enter image description here

What I want is to have this data on the basis of Study Id; if for any study id, text data i.e reasons are the same and Soption is at least 'true' for one record then it should show 'true', otherwise it should show 'false'.

i.e

enter image description here

Notice if both Soptions for a reason are 'no' then the Soption is retained as 'no', else Soption is set to 'yes' and the rows are combined as a single row.

How can I achieve this functionality in Access?

Upvotes: 0

Views: 55

Answers (1)

Johnny Bones
Johnny Bones

Reputation: 8402

SELECT StudyID, TextData, Max(SOption) as SOption2
FROM MyTable
GROUP BY StudyID, TextData
ORDER BY StudyID, TextData

When you group by, use "Max(SOption) as SOption2". Since Y is greater than N, it will return 'Yes' for any record that has both 'Yes' and 'No'.

Upvotes: 1

Related Questions