+ Post New Thread
Results 1 to 7 of 7
MIS Systems Thread, Some SQL wizardry required in Technical; I would like to produce, from SIMS .net, a spreadsheet with a row for each year 11 student, and then ...
  1. #1

    Join Date
    Jun 2005
    Location
    Preston, Lancashire
    Posts
    634
    Thank Post
    11
    Thanked 6 Times in 6 Posts
    Rep Power
    21

    Some SQL wizardry required

    I would like to produce, from SIMS .net, a spreadsheet with a row for each year 11 student, and then a column for each group that exists in year 11. For example: English Set 1, English Set 2, Maths Set 1, etc.. Then next to each student, in each column, a BOOL yes or no whether or not they belong to that group.

    Has anyone any ideas what kinda report I could design in SIMS to achieve this? Do I even make sense??

    Thanks,

    Andy

  2. #2
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    929
    Thank Post
    56
    Thanked 162 Times in 114 Posts
    Rep Power
    67
    hi andy,

    what do you need to do with the information?

    i ask as if you only want to look at the information and not manipulate it you can do this in a report using analysis as the output.

  3. Thanks to penfold_99 from:

    andy (17th March 2009)

  4. #3

    Join Date
    Jun 2005
    Location
    Preston, Lancashire
    Posts
    634
    Thank Post
    11
    Thanked 6 Times in 6 Posts
    Rep Power
    21
    Am after mailmerging it - lots of "IF student is in this set THEN write this text ELSE don't". If it comes to it, I might end up doing as you say, and just having to create my own table and tick the boxes myself. Did something similar with 60 kids last year, hoping I can get away with not having to do it for 200 kids this year!



    Andy

  5. #4
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    929
    Thank Post
    56
    Thanked 162 Times in 114 Posts
    Rep Power
    67
    what you could do it export a list of all the students and all the classes using the report generator to export to excel, then on a second sheet use a vlookup to look for the group of that column and set a value.

  6. Thanks to penfold_99 from:

    andy (17th March 2009)

  7. #5

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,970
    Thank Post
    673
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350

    Lightbulb

    There's probably easier ways than this, i just happen to be playing with pivot tables at the moment.

    What exactly are you trying to do with the spreadsheet afterwards? I have a marksheet similar to what you want that we use for sixth formers to show whether they are studying the higher or lower level of each subject. This would make more sense if you need to maintain links to data in SIMS.

    For now, design a report that lists the students name (i just pick name and reg as it's convenient), then add the class name from the classes subsection. Filter it for Y11 students. There's no need to sort really. Send the output to Excel just make sure that 'suppress duplicates' is not ticked.

    Run the report, then in excel from the data menu choose pivot table, accept all the defaults. (i'm using office 2k3, don't have a clue on 2k7 yet - it's probably easier!!) from the pivot table field choose box, drag students to the column on far left. also drag students to the data area in the middle. drag class to the row at the top.

    this should give you a '1' for each subject where a student takes that class. you get sub-totals at the end of columns and row.

    you can right click on the table and get it to fill blank cells with NO. If you really need the Yes part then, you have to copy paste the data but just the values into a new sheet and then can do a find and replace on it.

    it depends on what you are trying to achieve after, but this should be a good start.
    Attached Images Attached Images

  8. Thanks to vikpaw from:

    andy (17th March 2009)

  9. #6

    Join Date
    Jun 2005
    Location
    Preston, Lancashire
    Posts
    634
    Thank Post
    11
    Thanked 6 Times in 6 Posts
    Rep Power
    21
    Thanks to both of you. Vikpaw, that looks almost exactly like my spreadsheet of last year! Just the job. Will go play with that.



    Andy

  10. #7

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,970
    Thank Post
    673
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    sorry i got sidetracked whilst replying. i see what you are after now.

    i'd be tempted to say, use individual reports from Assessment Manager as you can run them off by class, and it's a live record. or, even better use SIMS Profiles and have fixed comment banks for each subject, plus it'll allow you to have multiple different comments for each subject the student does.

    it sounds like you are producing some kind of report / feedback system. so use the built in functionality. the spreadsheet is just an interim step in a manual solution.

SHARE:
+ Post New Thread

Similar Threads

  1. Hosting Required
    By garethedmondson in forum General Chat
    Replies: 6
    Last Post: 11th February 2009, 09:16 AM
  2. Help required for Interview
    By Simon_123 in forum Educational IT Jobs
    Replies: 1
    Last Post: 16th December 2008, 08:53 PM
  3. some [more] asp help required please
    By RabbieBurns in forum Web Development
    Replies: 9
    Last Post: 17th September 2008, 03:40 AM
  4. New printer required
    By alexknight in forum Hardware
    Replies: 14
    Last Post: 9th June 2008, 10:24 AM
  5. MS SQL 2005 - Dump SQL
    By tom_newton in forum Windows
    Replies: 7
    Last Post: 23rd January 2008, 05:10 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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