How to construct a crosstab query table

0
votes

I have data in an Access query in this format:

Access Query Format

Depending on how/when the scores were entered, which can be random as above. Fixture is always parent to 2 teams.

I would like to present it as follows:

  • Fixture1: Team A 1-2 Team B
  • Fixture2: Team C 3-1 Team D

I think I can still write code to do that, but I was thinking it should possibly be done in a query rather than code...?

I have tried a Crosstab query but I am ending up with something like this:

Current Crosstab query result

This is my current Crosstab query:

TRANSFORM Sum(FixtureResultGoalQ.Score) AS SumOfScore SELECT FixtureResultGoalQ.FixtureID, Teamname, Sum(FixtureResultGoalQ.Score) AS [Total Of Score] FROM FixtureResultGoalQ GROUP BY FixtureResultGoalQ.FixtureID PIVOT FixtureResultGoalQ.TeamName;

Is this something I should be able to do in a (Crosstab) query?

Thanks.

1
sqlms-accesscrosstab

1 Answers

0
votes

Remove the TEAMNAME out of your select:

TRANSFORM Sum(FixtureResultGoalQ.Score) AS SumOfScore SELECT FixtureResultGoalQ.FixtureID, Sum(FixtureResultGoalQ.Score) AS [Total Of Score] FROM FixtureResultGoalQ GROUP BY FixtureResultGoalQ.FixtureID PIVOT FixtureResultGoalQ.TeamName;