Showing Table Field Names Down the Left Side of a Report

[Jay]

Hi,

I'm having difficulty getting this to work even though I thought it would
be straightforward.

I have a table in a database that is used for storing weekly inventory
counts of some supplies we use. The table has a field for the date the
counts were taken and then one field for each of the supplies that are
counted.

When I create a report with this table as the recordsource it is
straightforward to make the report look like the following:

Date BoxCount PalletCount DividerCount ...
10/1 5000 20 50
10/8 5500 23 60
10/15 4500 15 45


In this case Date, BoxCount, PalletCount & DividerCount are all field names
in the table.

What I would like to do though is to format the report so it looks like
this:

10/1 10/8 10/15
BoxCount 5000 5500 4500
PalletCount 20 23 60
DividerCount 50 60 45
....

I had thought that I could accomplish this by creating a crosstab query
based on the table and pivoting on the Date field, but that doesn't seem to
allow me to use the field names as the row headers down the left side of
the report.

This seems so straightforward that I feel certain I am overlooking
something simple, but I have not been able to figure it out.


Thanks for any help,

Jay

 

[JoyAA]

You are only allowed to choose one value when using a pivot query. Therefore,
a query like this will give you the results you want for one Inventory Item.

TRANSFORM Sum(Inventory.Boxes) AS SumOfBoxes
SELECT "Boxes" AS Item
FROM Inventory
GROUP BY "Boxes"
PIVOT Inventory.Date;

If you have a static list of inventory items, you can create a query for
each and union the results in another query which can populate your report.
However, if you want to do this dynamically, you might search on transpose
and see if any of the posts help you. Here are a couple to get your started:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=323717&SiteID=17

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608