Student Scores display in multiple columns

[Mychael14]

Hi everyone could you please help me with this. I'm really new in Access and I'm trying to learn it by my self.
My first question is, how can I display the scores of a student in horizontal? (Bad english)
I have two tables, one with students information and another for students' score.
When I query may table using this

SELECT Student.StudentName, WrittenScores.Score
FROM Student INNER JOIN WrittenScores ON Student.[ID] = WrittenScores.[StudentID];

It resulted to this


All I want to do is something like this

[CJ_London]

you need to use a crosstab query, but you will need an additional column for the column header

you would set
studentname>group by, row heading
someothercolumn>group by, column heading
score>sum or first depending on someothercolumn>value

someothercolumn might be a testid or date for example


Alternatively look at using a pivot query, but you will still need a minimum of three columns - column, value and the rest as rows

[ranman256]

Build a Crosstab query.

[Mychael14]

Build a Crosstab query.

Thanks for your reply... I'll search google for that or maybe you can teach how to do that

[Mychael14]

Thank you ranman256 and ajax I was able to solve the problem with your help. Can you pls help me again... this time I have three tables similar to my first question, I want to achieve something like this but using CrossTab does not allow me. It needs one ColumnHeading and value option. Thank you very much.

[CJ_London]

you have to create separate crosstabs for each table. Then create a 4th table or query which lists all the names.

Then in a 5th query, left join this fourth table to the three crosstabs on name. You need a left join in case you have names in table1 which are not in the other tables, similarly for tables 2 and 3.

[Mychael14]

Hi Ajax, I merge all crosstabs using this query

Select * 
FROM Query1,QUERY2,QUERY3
WHERE QUERY1.STUDENTNAME = QUERY2.STUDENTNAME AND QUERY1.STUDENTNAME = QUERY3.STUDENTNAME

This resulted to this

[Mychael14]

I'm tired. How can I remove the other columns for name?

[June7]

Don't use the * wildcard for all tables, explicitly reference fields in the SELECT clause.

SELECT Query1.*, Query2.1, Query2.2, Query2.3, Query3.1, Query3.2, Query3.3

Should probably use JOIN clause instead of the WHERE. Join Query2 and Query3 to Query1.

[Mychael14]

Hi June7, but Query1 and Query2 is from a table with no definite number of rows. When I query those table I may have more than 3 columns. What can you say?

[CJ_London]

when you use a crosstab as a part of a new query, the crosstab runs before you can view in the new query, so you will know exactly what columns you have

On the other hand, in each crosstab you can set the column headings - if not already visible, view properties - column headings would then be

1,2,3

[Mychael14]

I have to explain my self

I have three tables holding the scores/marks of students and users may add several quizzes, if they do, this will increasing the number of rows in my 3 tables that will increase the number of columns in crosstab. Then I want to join the queries to have a complete view of the scores of each student.

[June7]

So you are doing 3 crosstabs on the 3 score tables - join those 3 crosstabs to the Student table. Set each join so that will "Include all records from Student and only those from ..."

Yes, crosstabs are dynamic for the columns. Review: http://allenbrowne.com/ser-67.html

[Mychael14]

So you are doing 3 crosstabs on the 3 score tables - join those 3 crosstabs to the Student table. Set each join so that will "Include all records from Student and only those from ..."

Yes, crosstabs are dynamic for the columns. Review: http://allenbrowne.com/ser-67.html

But what if a user add more quizzes? Let say students have 4 quizzes in WrittenWorks (the Query1), therefore I will have 4 columns for scores in CrossTab. But the query below will show only 3 columns for WrittenScores.

SELECT Student.StudentName, Query1.[1], Query1.[2], Query1.[3], Query2.[1], Query2.[2], Query2.[3], Query3.[1]
FROM ((Student LEFT JOIN Query1 ON STUDENT.STUDENTNAME = QUERY1.STUDENTNAME) LEFT JOIN Query2 ON STUDENT.STUDENTNAME = QUERY2.STUDENTNAME)LEFT JOIN Query3 ON STUDENT.STUDENTNAME = QUERY3.STUDENTNAME;

[June7]

Will have to adjust the column heads. That's the problem with CROSSTABs. I have never used them as the basis for a report.