Pivot Table to Report

[JayDawg]

Is there any way to create a pivot table in a report? I have a query
with the fields Date, Name, and Data, and I am trying to create a
report that has the dates running across the top (like fields) grouped
by month, with the names running down the left, and the data values
under each appropriate month.

I am totally stumped on how to do this, other than using a Pivot Table,
but that will not suffice as this has to be created as a report.

[JayDawg]

I guess what I am saying is that a Pivot Table has the ability to group
data and make it into a field, can a report do the same?

JayDawg wrote:

Is there any way to create a pivot table in a report? I have a query
with the fields Date, Name, and Data, and I am trying to create a
report that has the dates running across the top (like fields) grouped
by month, with the names running down the left, and the data values
under each appropriate month.

I am totally stumped on how to do this, other than using a Pivot Table,
but that will not suffice as this has to be created as a report.

[Bob Quintal]

"JayDawg" <he******@cox.net> wrote in
news:11**********************@c74g2000cwc.googlegr oups.com:

I guess what I am saying is that a Pivot Table has the ability
to group data and make it into a field, can a report do the
same?

JayDawg wrote:

Is there any way to create a pivot table in a report? I have
a query with the fields Date, Name, and Data, and I am trying
to create a report that has the dates running across the top
(like fields) grouped by month, with the names running down
the left, and the data values under each appropriate month.

I am totally stumped on how to do this, other than using a
Pivot Table, but that will not suffice as this has to be
created as a report.

Pivot table? you mean a Crosstab query in Access.
Here's my method.

Create a query that has the following columns
Names, values, and a number that represents the number of months
ago, so that this month is 0, may is 1, april is 2 etc.

convert to a summary query, group on names, monthsAgo and
sum/count/avg your values (pick 1) set a condition
Change the query type to CrossTab Query in design view.
This will add one row to the grid.
Select "Row Heading" as the value under names, "Column Heading"
under MonthsAgo and Value under Data.

Build your report on this query. You'll have the data you want
under the 0, 1, 2.... headers.

Now write a little code that changes the label captions back to
June, May, April,,, etc. .

This works great if you are willing to live with a constant
number of months, for example the last 12 months of data, every
month.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[JayDawg]

Bob thanks for the help I didnt have to do much other than create the
Cross Tab Query, which put all the fields right where I wanted them. I
had never done a cross tab before, but boy is that useful, thanks
again...

Bob Quintal wrote:

"JayDawg" <he******@cox.net> wrote in
news:11**********************@c74g2000cwc.googlegr oups.com:

I guess what I am saying is that a Pivot Table has the ability
to group data and make it into a field, can a report do the
same?

JayDawg wrote:

Is there any way to create a pivot table in a report? I have
a query with the fields Date, Name, and Data, and I am trying
to create a report that has the dates running across the top
(like fields) grouped by month, with the names running down
the left, and the data values under each appropriate month.

I am totally stumped on how to do this, other than using a
Pivot Table, but that will not suffice as this has to be
created as a report.

Pivot table? you mean a Crosstab query in Access.
Here's my method.

Create a query that has the following columns
Names, values, and a number that represents the number of months
ago, so that this month is 0, may is 1, april is 2 etc.

convert to a summary query, group on names, monthsAgo and
sum/count/avg your values (pick 1) set a condition
Change the query type to CrossTab Query in design view.
This will add one row to the grid.
Select "Row Heading" as the value under names, "Column Heading"
under MonthsAgo and Value under Data.

Build your report on this query. You'll have the data you want
under the 0, 1, 2.... headers.

Now write a little code that changes the label captions back to
June, May, April,,, etc. .

This works great if you are willing to live with a constant
number of months, for example the last 12 months of data, every
month.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[w_a_n_n_a_l_l_ -]

FYI: If you want to make it easier to work with your crosstabs in report,
and you have this option, it's a good idea to add the list of possible
values after the PIVOT statement

TRANSFORM ...
SELECT ...
FROM ...
GROUP BY ...
PIVOT MyValueField In (0, 1, 2, 3, 4)

This would work where the only possible values for the pivot field are 0
through 4.

If you know them ahead, put them in, so that the report designer does not
have to run the crosstab query in order to know the fields. Those pivot
values become the field names, right?

It also seems to help performance a bit, but I'm not certain because I've
never had a big enough problem to bother doing close comparisons. If
someone knows for sure, I'd love to hear.

[w_a_n_n_a_l_l_ -]

So sorry. that should read
TRANSFORM ...
SELECT ...
FROM ...
GROUP BY ...
PIVOT MyColumnHeadingField In (0, 1, 2, 3, 4)

THOSE values become the column headings. the values of course are the
values shown under each of those columns.