CODEWITHSUNDEEP
ms-access
agnieszka
agnieszka

Reputation: 15355

MS Access: how to display horizontally data returned by query?

I've got a query that returns Headcount and Newcomers values for months like this:

Month      Headcount   Newcomers
January    12          2
February   14          1
...

and so on.

I would like to display them in a report this way:

            January     February   ...
Headcount   12          14
Newcomers   2           1

Is it possible in MS Access? To create a query that would return such data using my query or to create a report that could display data returned by my query in this way? I've read about pivot tables and crosstab queries but this is not what I am looking for, as they do not simply rotate the matrix.

Upvotes: 1

Views: 5895

Answers (2)

JeffO
JeffO

Reputation: 8043

I wasn't able to pull into a single query.

Call one query: Table1_Crosstab_HeadCount with this sql

TRANSFORM Max(Table1.HeadCount) AS MaxOfHeadCount
SELECT "HeadCount" AS Value_Type
FROM Table1
GROUP BY "HeadCount"
PIVOT Table1.Month;

Call second query: Table1_Crosstab_Newcomers with this sql

TRANSFORM Max(Table1.NewComers) AS MaxOfNewComers
SELECT "Newcomers" AS Value_Type
FROM Table1
GROUP BY "Newcomers"
PIVOT Table1.Month;

Create a 3rd union query (pardon my Select *)

select * from Table1_Crosstab_HeadCount
UNION ALL
Select * from Table1_Crosstab_Newcomers;

Use the third query for your report. Note: if you don't have a record for a particular month, it won't be available as a field in the report designer.

Upvotes: 1

HardCode
HardCode

Reputation: 6766

You'll have to write a new query to sum by Month and Group By on the field that determines Headcount and Newcomers

Upvotes: -1

Related Questions