PC Review


Reply
Thread Tools Rate Thread

CrossTab Query Access Report -

 
 
Arun vijay
Guest
Posts: n/a
 
      13th Feb 2011
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.

 
Reply With Quote
 
 
 
 
John Spencer
Guest
Posts: n/a
 
      14th Feb 2011
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.
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Crosstab query is no longer crosstab when I include field (in repo Ben8765 Microsoft Access Reports 1 15th Oct 2009 10:01 PM
Crosstab query - want report to look like crosstab alison.justice Microsoft Access Queries 1 31st May 2009 05:35 AM
Crosstab form based on Crosstab query? Penny Microsoft Access Forms 1 31st Mar 2006 07:36 PM
Crosstab query with criteria, dynamic columns and crosstab report question joshblair Microsoft Access Queries 5 13th Jan 2006 10:34 PM
Parameter Query referencing a crosstab query to use in a report. Lee Stafford via AccessMonster.com Microsoft Access Reports 0 4th Mar 2005 09:14 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:45 AM.