CrossTab Query Access Report -

Discussion in 'Microsoft Access Reports' started by Arun vijay, Feb 13, 2011.

  1. Arun vijay

    Arun vijay Guest

    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.
     
    Arun vijay, Feb 13, 2011
    #1
    1. Advertisements

  2. Arun vijay

    John Spencer Guest

    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.
    >
     
    John Spencer, Feb 14, 2011
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. astro
    Replies:
    3
    Views:
    163
    Duane Hookom
    Jul 26, 2004
  2. Lee Stafford via AccessMonster.com

    Parameter Query referencing a crosstab query to use in a report.

    Lee Stafford via AccessMonster.com, Mar 4, 2005, in forum: Microsoft Access Reports
    Replies:
    0
    Views:
    245
    Lee Stafford via AccessMonster.com
    Mar 4, 2005
  3. Creating Crosstab Reports from Crosstab Queries

    , Feb 17, 2007, in forum: Microsoft Access Reports
    Replies:
    4
    Views:
    385
    Guest
    Feb 25, 2007
  4. Guest
    Replies:
    2
    Views:
    282
    Guest
    Nov 17, 2007
  5. Ben8765
    Replies:
    1
    Views:
    492
    KARL DEWEY
    Oct 15, 2009
Loading...

Share This Page