Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2008

    Unanswered: Query regarding Crosstab queries

    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?


  2. #2
    Join Date
    Apr 2008
    Luckily, you just have 2 values, Yes and No. To quickly dedup, just put MAX(Survey3) and MAX(Survey4). Then, you'll get Yes and the No's will be ignored.

    Have fun!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts