1. ## 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. ## 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. ## 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
•