show monthly results

  • Thread starter Thread starter Therese
  • Start date Start date
T

Therese

I have a query which returns a sum of order by type by month.
The query returns a result like the following

artnr | artname | quantity | month | name of month
------------------------------------------------------------------------------
1 | bbbb | 345 | 5 | may
3 | aaaa | 123 | 5 | may
7 | ccccc | 555 | 5 | may
1 | bbbb | 666 | 12 | december
4 | ggggg | 5 | 2 | february
4 | ggggg | 8 | 12 | december

and I want to make a report which shows the data as follows


artnr artname .. february ... may ... december
-----------------------------------------------------------------------------------
1 bbbb 345 666
3 aaaa 123
4 ggggg 5 12


Is this possible to do? And in that case, how?

I am grateful for all answers :)

Therese
 
1. Create a new query, using the one you already have as the input table.

2. Change it to a Crosstab query (Crosstab on Query menu.)
Access adds Total and Crosstab rows to the design grid.

3. Add artnr and artname fields to the grid.
Accept Group By in the Total row.
Choose Row Heading in the Crosstab row.

4. Drag [name of month] into the grid.
Accept Group By in the Total row.
Choose Column Heading in the Crosstab row.

5. Drag quantity into the grid.
Choose First (or perhaps Sum) in the Total row.
Choose Value in the Crosstab row.

6. To get the month names into the right order, type the names of the months
into the query's Column Headings property (in the Properties box.) Put each
name in quotes, and separate with commas, e.g.:
"January", "February", ...
 
Back
Top