I've been trying to create an access report for a cross tab query.
I've come across various posts providing solution for creating reports
for queries which have date field(months or weeks or years) for a
pivot column. Coming to my specific problem, the crosstab query
resembles as below

TRANSFORM Sum([Amount])
SELECT ProductName
FROM Employees,Sales
WHERE Employees.EmpId=Sales.EmpId
GROUP BY ProductName
PIVOT EmployeeName;

and works just fine while executing the query. Employee Name being the
Column Header, and Product Name being the row header. It might sound a
bit out of place, but while the no of employees may not exceed a max
of 15, the no of products may increase with no restrictions. Currently
assuming there are around 30 products.
Now is it possible to create an access report using this query.

Yes, it is possible. The problem being if your employee list changes the
report will have errors if someone is dropped and won't show new employees.

I believe that Duane Hookom has posted solutions to having variable columns.
Try searching on his name plus crosstab query


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

On 2/13/2011 12:56 AM, Arun vijay wrote:
> I've been trying to create an access report for a cross tab query.
> I've come across various posts providing solution for creating reports
> for queries which have date field(months or weeks or years) for a
> pivot column. Coming to my specific problem, the crosstab query
> resembles as below
>
> TRANSFORM Sum([Amount])
> SELECT ProductName
> FROM Employees,Sales
> WHERE Employees.EmpId=Sales.EmpId
> GROUP BY ProductName
> PIVOT EmployeeName;
>
> and works just fine while executing the query. Employee Name being the
> Column Header, and Product Name being the row header. It might sound a
> bit out of place, but while the no of employees may not exceed a max
> of 15, the no of products may increase with no restrictions. Currently
> assuming there are around 30 products.
> Now is it possible to create an access report using this query.
>