I have created a crosstab query which almost works perfectly. There is just one thing that I am trying to do which would really be the cherry on top. The query searches for completed surveys and where there is one it puts a Yes in the respective column in the query. However, this means that if one person has completed a number of different surveys, each survey will appear as a seperate entity. I am wondering if there is a way I could consolidate all entries by any single person in the same line of a query.
For example, the query at the moment would display data like this:
URN First Name Survey3 Survey4
52 John Yes No
78 Mandy Yes No
78 Mandy No Yes
So you can see from this that Mandy appears twice. Ideally, she would just have one line which would read:
78 Mandy Yes Yes
How could I go about doing this? Is this a case of using SQL?
I have tried changing the query so that the relationships mean that each person only appears once. However I am not sure how to then do the rest. I would ideally have a column with a statement which would be the equivalent of 'if survey = 3, then if survey completed = yes, otherwise = no'. I know how to do the IIf query which would be the second part, but how can I also include the other part of the statement?