show monthly results

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Therese - 22 May 2006 11:45 GMT

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

Reply to this Message

Allen Browne - 22 May 2006 11:56 GMT

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", ...

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a query which returns a sum of order by type by month.
> The query returns a result like the following
[quoted text clipped - 21 lines]
>
> Therese

Reply to this Message

Therese - 22 May 2006 13:57 GMT

Thanks a lot, I have made it work now thanks to your guidance :)

Therese

Reply to this Message