Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transposing data (2007)

    I am trying to figure out the best way to calculate this data. I think it would be to transpose the columns and rows, but I'm not sure. I have a table with the following Columns: ID, Q1, Q2, Q3, Q4. In the Q columns, possible answers are yes, no, n/a. I need a way to calculate a final result: For each ID, there will be one yes, no, or n/a. If any Q is answered no, then the final result will be no; if any Q is answered n/a (without any no's) the final answer will be n/a; if all Q's are answered yes, the final result will be yes. I hope that makes sense. I then need to calculate a percentage of client's with yes, no, and n/a.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transposing data (2007)

    A different data design would have been better (with only one Q column, and separate records for each reply), but with the existing design, you could do the following:

    - Create a query based on the table.
    - Add the fields from the table.
    - Create a calculated column

    Score: IIf([Q1]="No" Or [Q2]="No" Or [Q3]="No" Or [Q4]="No", "No", IIf([Q1]="N/A" Or [Q2]="N/A" Or [Q3]="N/A" Or [Q4]="N/A", "N/A", "Yes"))

    - Save this query.
    - You could use a crosstab query to tally the scores, or a report that groups by Score.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transposing data (2007)

    I've added a condition in case all of the Qs did not contain no or N/A or all yes's

    IIf([Q1]="NO" Or [Q2]="NO" Or [Q3]="NO" Or [Q4]="NO","NO",IIf([Q1]="N/A" Or [Q2]="N/A" Or [Q3]="N/A" Or [Q4]="N/A","N/A",IIf([Q1]="YES" And [Q2]="YES" And [Q3]="YES" And [Q4]="YES","YES","INVALID")))

Posting Permissions

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