# Creating multiple calculated fields with unique rows

• ### Question

• I'm trying to take data listed in rows on a table and convert that data to columns in query based on their value.

Table:

 StudentID AccommsCode 1964097 AF15 1964097 DS1.4 2050512 AF15 1856046 NOTES 2023525 AF15 2023525 C 2023525 L 2023525 NOTES

Currently I am using IIF statements like  ContentSupports: IIf([StudentAccommAssignments]![AccommsCode]='C','X','') and LangSupports: IIf([StudentAccommAssignments]![AccommsCode]='L','X',''). However, that is creating two records for students who have both accommodations. How can I get it to appear on one row?

Current result (multiple rows):

 LOCAL-STUDENT-ID ContentSupports LangSupports 1885956 X 1885956 X

Desired Results (one row):

 LOCAL-STUDENT-ID ContentSupports LangSupports 1885956 X X

Wednesday, October 11, 2017 4:28 PM

### All replies

• Hi Kay,

Have you tried using a Crosstab query?

Just a thought...

• Proposed as answer by Thursday, October 12, 2017 2:09 AM
Wednesday, October 11, 2017 4:48 PM
• Crosstab does the job. I guess I was trying to over complicate things.

Thank you

Wednesday, October 11, 2017 5:03 PM
• Hi,

You're welcome. Glad we could assist. Good luck with your project.

Wednesday, October 11, 2017 5:10 PM
• Now that I have created the crosstab query, how would I replace the '1' with a comment associated with that accommodation.

For instance, each student could have a different 'content support' accommodation. How can I get a query to show the specific comment associated with each student instead of just a '1'.

Student ID;  AccommCode; Comment;

12345;         DS1.3;           Four function calculator

23456;         DS1.3;           Multiplication Chart

Is there a way to make the query look like the example below?

Student ID;    DS1.3

12345;           Four function calculator

23456;            Multiplication Chart

Wednesday, October 11, 2017 6:12 PM
• Solved the problem.
Wednesday, October 11, 2017 7:01 PM
• Solved the problem.

LOL. Sorry for the delay. Glad to hear you were able to work it out. Cheers!
Wednesday, October 11, 2017 7:25 PM
• Solved the problem.

Hello,

We are glad that you have it resolved. I suggest you mark your solution or helpful post as answer to close this thread. If you have any new issue, please feel free to post new threads.

Regards,

Celeste

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Thursday, October 12, 2017 2:12 AM