Transforming access data in rows to columns

  • Thread starter Thread starter Jack
  • Start date Start date
J

Jack

Hi,
I am using access 2000. Currently my query has the following output.
SalesMonth Sum custnum Year slsman
1 52866 C000012 2008 907802
2 47186 C000012 2008 907802
3 41526 C000012 2008 907802
4 31059 C000012 2008 907802
5 44594 C000012 2008 907802
6 40526 C000012 2008 907802
7 65059 C000012 2008 907802
8 39396 C000012 2008 907802
9 29963 C000012 2008 907802
10 47835 C000012 2008 907802
11 28944 C000012 2008 907802
12 18341 C000012 2008 907802

However I need to transform the above such that the output is as follows:

slsman custnum Year Month1 Month2 Month3 Month4 Month5 Month6 Month7
Month8 Month9 Month10 Month11 Month12

907802 c000012 52866 47186 41526 31059 .....

I appreciate any help for resolution. Thanks.
 
Looks like an ideal situation for a Crosstab query.

TRANSFORM SUM(Q.[Sum]) as MonthlyTotal
SELECT slsman, custnum, [Year]
, Sum(Q.[Sum]) as AnnualTotal
FROM [YourQueryOrTable] as Q
GROUP BY slsman, custnum, [Year]
PIVOT "Month" & Format(SalesMonth,"00")

In the query design mode
== Add your current query
== Select Query: Crosstab query from the menu
== Add the fields that you wish to see to the selection grid
== under Sum (really bad name for a field since it is a function) set Group by
to Sum and Crosstab to Value

== for slsman, custnum, [Year] set Crosstab to Row Heading
== for Salesmonth set Crosstab to Column Heading
If you want Month plus number then you will need to edit the field to
"Month" & SalesMonth
and if you want them in order you will need to add the format statement so
that the value in the field "cell" is
"Month" & Format(SalesMonth,"00")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top