locked
Creating multiple calculated fields with unique rows RRS feed

  • 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 Chenchen Li 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