Sorting/Transposing in Access

[BiotinX]

All -

I'm having a hard time sorting data I am receiving from an online grading system. I've attached an xls of a small example of how I receive the data and how I need to sort the data. I would appreciate it if you had time to help out with this. I'm not sure if this sorting and transposing is best done with access or with a excel maro.

Thanks!



access sorting help.zip

[ranman256]

this is not sorting, you are restructuring the table, BUT in access you can create something 'similar' with a CROSSTAB QUERY.
In Excel, you can build a PIVOT TABLE.

But neither will restructure in the method you showed. No data is shown that way.

[Bob Fitz]

I'm having a hard time sorting data I am receiving from an online grading system. I've attached an xls of a small example of how I receive the data and how I need to sort the data. I would appreciate it if you had time to help out with this. I'm not sure if this sorting and transposing is best done with access or with a excel maro.

There may be a better way but take a look at qry123 in the attached db.

[BiotinX]

That helps a bunch. So you make a query for each question and then a query to I guess summarize the results? I guess I'll need to make two different databases, because some of our exams have 25 questions while others have 75 questions. I very much appreciate your time.

[ArviLaanemets]

And you want the query table with (6*75=450) + fields? To look at! Or even to print out!

Read or link received data into Access database table designed specifically for this. Then update other Access database tables based on gotten data - I think you need tables like:
tblStudents: StudentID, ForeName, LastName, ...;
tblTests: TestID, ...;
tblTestQuestions: TestQuestionID, TestID, QuestionText, QuestionAnswer, MaxPoints, ...;
tblStudentTests: StudentTestID, StudentID, TestID, TestDate, ...;
tblStudentTestAnswers: StudentTestAnswerID, StudentTestID, TestQuestionID, StudentAnswer, AwardedPoints.

To look how specific student did test directly in app, have a single-type form based on tblStudents, where user can select a student, with unbound combo where user can select a test. The form will have an continuous subform linked with main form through StudentID and unbound combo. The subform displays row-wise all questions from test, and all info about student answers.

To have a report printed out (or to look at), you create an Access report, grouped by students. Report is based on query, which returns info about test, student, test questions and student answers. When the report is called, the filter for query is set to limit returned data to specific test. Student info is displayed in group header, all questions and answers info is displayed row-wise in group body, and test totals for this student are displayed at group footer. When designing the report, you can place report fields in way to ensure best readability.

Neither form with subform or report will have restrictions on number of questions in test. How many of them you have, so many you get.

You can have other reports based on same data, like test totals report (a row of totals for every student who did the test),all tests results from specific student over some period, etc.