Create Access crosstab from query with variable number of columns and multiple values-CodePudding

I have a query made by joining data from three tables in a MS-Access DB

simsID  Forename    Surname Class   AssessmentName  Mark    Percentage
1234    Joe Bloggs  13X Test1   20  50
1235    Fred    Bloggs  13Y Test1   31  77.5
1234    Joe Bloggs  13X Test2   30  60
1235    Fred    Bloggs  13Y Test2   10  20
1235    Fred    Bloggs  13Y Test3   20  33.3333333333333
1234    Joe Bloggs  13X Test3   34  56.6666666666667

I would like to display the data as follows

ID  Forename    Surname Class   Test1 Mark  Test1 % Test2 Mark  Test2 % Test3 Mark  Test3 %
1234    Joe Bloggs  13X 20  50  30  60  34  56.6666666666667
1235    Fred    Bloggs  13Y 31  77.5    10  20  20  33.3333333333333

The only way I can see is to do two crosstab queries and then join them. AllStudentData_Marks query

TRANSFORM Avg(AllStudentData.Mark) AS AvgOfMark
SELECT AllStudentData.simsID AS ID, AllStudentData.Forename AS Forename, AllStudentData.Surname AS Surname, AllStudentData.Class AS Class
FROM AllStudentData
GROUP BY AllStudentData.simsID, AllStudentData.Forename, AllStudentData.Surname, AllStudentData.Class
PIVOT AllStudentData.Assessments.AssessmentName & " Mark";

AllStudentData_Precentage query

TRANSFORM Avg(AllStudentData.Percentage) AS AvgOfPercentage
SELECT AllStudentData.simsID AS ID, AllStudentData.Forename AS Forename, AllStudentData.Surname AS Surname, AllStudentData.Class AS Class
FROM AllStudentData
GROUP BY AllStudentData.simsID, AllStudentData.Forename, AllStudentData.Surname, AllStudentData.Class
PIVOT AllStudentData.Assessments.AssessmentName & " %";

Join query

SELECT AllStudentData_Marks.*, AllStudentData_Percentage.*
FROM AllStudentData_Marks INNER JOIN AllStudentData_Percentage ON AllStudentData_Marks.ID = AllStudentData_Percentage.ID;

As expected this gives me a big table:

AllStudentData_Marks.ID AllStudentData_Marks.Forename   AllStudentData_Marks.Surname    AllStudentData_Marks.Class  Test1 Mark  Test2 Mark  Test3 Mark  AllStudentData_Percentage.ID    AllStudentData_Percentage.Forename  AllStudentData_Percentage.Surname   AllStudentData_Percentage.Class Test1 % Test2 % Test3 %
1234    Joe Bloggs  13X 20  30  34  1234    Joe Bloggs  13X 50  60  56.6666666666667
1235    Fred    Bloggs  13Y 31  10  20  1235    Fred    Bloggs  13Y 77.5    20  33.3333333333333

I would like to limit the amount of columns (ie not repeat the names) and have more sensible column names (Forename, Surname, etc), but the number of assessment columns is not fixed hence why I am using the wildcard in SELECT.

How can I limit this final query to just return my compact table with sensible headings? ie

ID  Forename    Surname Class   Test1 Mark  Test1 % Test2 Mark  Test2 % Test3 Mark  Test3 %
1234    Joe Bloggs  13X 20  50  30  60  34  56.6666666666667
1235    Fred    Bloggs  13Y 31  77.5    10  20  20  33.3333333333333

Thanks in advance for reading.

CodePudding user response：

Yes, two joined CROSSTABS is one way to pivot two sets of values. Another method described in http://allenbrowne.com/ser-67.html#MultipleValues.

And yet another approach involves a UNION query which is then used as source for CROSSTAB. Working with the sample dataset you posted, consider:

Query1:

SELECT simsID, Forename, Surname, Class, AssessmentName, Mark AS Data, "M" AS Cat FROM dataset
UNION SELECT simsID, Forename, Surname, Class, AssessmentName, Percentage, "P" AS Cat FROM dataset;

Query2:

TRANSFORM Sum(Query1.Data) AS SumOfData
SELECT Query1.simsID, Query1.Surname, Query1.Class
FROM Query1
GROUP BY Query1.simsID, Query1.Forename, Query1.Surname, Query1.Class
PIVOT [AssessmentName] & [Cat];

Might find this of interest Crosstab Query on multiple data points