Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2011
    Posts
    3

    Unanswered: Display data from multiple records in one row

    Hi there,

    I have a database dealing with data for a college course. The course is made up of units, and the grade attained for each unit is made up of the marks given for assignments. Each unit can have from one to three assignments. Each assignment counts for a certain percentage of that unit's overall mark.

    I want to produce a report, or form, for each unit that shows what marks each student got for the assignments, and then calculates their overall unit grade, and allows me to enter a comment for each student like this:

    Student_name, assignment1_mark, assignment2_mark, overall_grade, comment
    Jo Bloggs, 58, 30, 41, retake assignment 2
    Su Lin, 54, 70, 64, recommend this student

    The overall_grade is calculated from the marks in the Student_Assignment table and the percentage_of_unit in the Assignment table, and there could be 1,2,or 3 assignments for that unit (I'm not asking for the calculation formula btw, but trying to work out how to get the data into a report where I can do the calculation)

    Currently data for each assignment is held in a separate record, and I'm trying to get information from 1-3 records into one row. I think it may be subquery(s) that I need, but I can't work it out. It's not too late to change the table structure if it's my normal form which is causing difficulties.

    Here are the relevant part of the tables:

    Table: Unit
    unit_id
    Unit_title


    Table: Students
    student_id
    name

    Table: Student_Unit
    student_id
    unit_id
    overall_grade
    comment

    Table: Assignment
    assignment_id
    unit_id
    title
    percentage_of_unit

    Table: Student_Assignment
    student_id
    assignment_id
    mark


    I can't work out if I'm approaching this wrongly, or if I just don't know enough about SQL! The admin for this is currently done on a series of separate spreadsheets, which I'm trying to turn into a coherent database.

    Thanks for any pointers

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    I don't really understand what it is that you can't do.

    Are you struggling to create a query / SQL statement to amalgamate the data that you already have in your relational database?

    Also, if that's the case, how are your end users to generate this information (button on an existing form, opening a report from the main selection screen)?

    How do you want the info to be displayed (list on a form, report)?
    Looking for the perfect beer...

  3. #3
    Join Date
    Aug 2011
    Posts
    3
    Yes, I am struggling to create a query as the basis for a report. And I think it would be generated from the main switchboard via a listbox where the user would select the unit. This would then feed the assignment_ids connected with that unit into the query.

    Currently the query
    SELECT student_id,assignment_id,mark
    FROM student_assignment
    WHERE (assignment_id=1 Or assignment_id=2);

    gives me

    student_id,assignment_id, mark
    1234, 1, 54
    1234, 2, 60
    5678, 1, 66
    5678, 2, 55

    but what I want is
    student_id, mark1, mark2
    1,54,60
    2,66,55

    where mark1 is the mark from assignment1 and mark2 is the mark from assignment2. Sometimes there may be 3 assignments per student, sometimes only 1. I would like to get the marks into separate controls on the report, not concatenated into one string.

    Sorry if I'm not being clear. I've been looking at subqueries but can't find anything to deal with varying numbers of assignment. Now wonder if I should look at creating a record set and looping through it somehow. Or am I over-complicating wildly?

    Thank you for your time ...

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The general structure of such a query is like this:
    Code:
    SELECT TOP 1 Students.student_id, Students.mark AS Mark1, Students_1.mark AS Mark2
    FROM Students INNER JOIN Students AS Students_1 ON Students.student_id = Students_1.student_id
    WHERE (((Students_1.assignment_id)>[Students].[assignment_id]))
    ORDER BY Students.assignment_id;
    The problem is that it is rather simple for 2 rows (Assignment1 and Assignment2 yielding Mark1 and Mark2) but it becomes more and more complex when the number of assignment grows. Having a variable number of assignments does not help either.
    Have a nice day!

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    I think you have either two choices, either read through Sinndho's example, learn a bit more SQL and expand that to work for three assignments.

    Or, alternatively, link some VBA to dynamically create the SQL query when the button on the switchboard is pressed...

    Up to you really, SQL is a lot more compact and quicker to write, but requires a much greater level of understanding to write properly. VBA is more verbose, but easier to understand what's going on because of that.
    Looking for the perfect beer...

  6. #6
    Join Date
    Aug 2011
    Posts
    3
    OK I seem to have managed it. I used this article as a basis How to create a crosstab query with multiple value fields .

    Made two crosstab queries with rows = student_id and columns = assignment_id, one showing the marks gained and the other showing the percentages.

    These Qs got the unit_id parameter from a listbox on a form - this determines which assignments are selected, and how many there are.

    Then needed a query to join these two crosstab Qs so that both marks and percentages would be shown, and I could therefore calculate overall grade.

    Used the unit id to create a recordset, then looped through this to construct the joining query SQL in VBA so the parameters could be fed in dynamically and the number of assignments varied.

    So now I get a query result like this (Assignment1 is the mark given for that assignment, PC1 is the percentage of the unit that mark counts for. Overall is a calculated field giving overall mark):

    student_id,Assignment1, PC1, Assignment2, PC2, Overall
    1,58,40%,43,60%,49
    2,54,40%,42,60%,46.8

    Thank you very much for your comments, they made me realise it wasn't a trivial problem and would require quite a bit of work! Sorry if this post is a bit of a braindump, happy to go into more detail if anyone would like.

  7. #7
    Join Date
    Dec 2012
    Posts
    3
    To Sinndho,

    can you just give me some instructions how to build the query if there are more assignments (3 or 4) each with 2 marks?

    Thanks
    Last edited by WimDC; 10-12-13 at 09:42.

Posting Permissions

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