Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CrossTab report covering >1 year (Access 2000)

    I have a table of sales transactions covering a 3 year time period. I want to be able to produce a cross tab report that sums the sales for each month of the 3 years but shows them separately in correct order across the page Including year and month. eg Jan 2003 comes before april 2003 and all a specific year are grouped together in the correct order. Hope this makes sense and thanks in advance
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: CrossTab report covering >1 year (Access 2000)

    Try fiddling around with the PIVOT clause.

    eg.
    TRANSFORM Sum(tblPayment.Amount) AS SumOfAmount
    SELECT tblPayment.PaymentMethod, Sum(tblPayment.Amount) AS [Total Of Amount]
    FROM tblPayment
    GROUP BY tblPayment.PaymentMethod
    PIVOT Format([DatePaid],"mmm yy") In ("Jan 03","Feb 03","Mar 03","Apr 03","May 03","Jun 03","Jul 03","Aug 03","Sep 03","Oct 03","Nov 03","Dec 03");

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CrossTab report covering >1 year (Access 2000)

    Thanks for the amazingly quick response. I am guessing that you mean in the Forms - create Pivot Table Wizard section - If so I have a new problem - When I select the appropriate fields and click on next, I get a message "The class argument in the CreateObject function of the Visual Basic Procedure you're trying to run is invalid"
    A - am I guessing correctly on needing to be in the Pivot Table area
    B - If so , any idea what the error message means
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: CrossTab report covering >1 year (Access 2000)

    No, I mean the Query - Create Crosstab query.
    You will need to go into the SQL of the query after you have used the query wizard to initially create the crosstab query and change or introduce the PIVOT clause like my first reply suggests.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CrossTab report covering >1 year (Access 2000)

    Thanks for clarification (and again the speed)-
    did that but when I try to run the query, it is looking for ODBC data links (select Data Source- while the data was originally created via ODBC, to speed things up, I create a local (SP) table in my database and update it when I first go in. It is this updated local table that is the source for my cross tab query
    To explain more fully: I want Item Number and Item Name as the rows in my cross tab and sum of quantity as the data with it grouped by date using yor suggested formt. The resultant SQL is below

    TRANSFORM Sum(TblItemSales.Quantity) AS SumOfQuantity
    SELECT TblItemSales.ItemNumber, TblItemSales.ItemName, Sum(TblItemSales.Quantity) AS [Total Sold]
    FROM TblItemSales
    GROUP BY TblItemSales.ItemNumber, TblItemSales.ItemName
    PIVOT Format([Date],"mmm yy") In ("Jan 03","Feb 03","Mar 03","Apr 03","May 03","Jun 03","Jul 03","Aug 03","Sep 03","Oct 03","Nov 03","Dec 03","Jan 04","Feb 04","Mar 04","Apr 04","May 04","Jun 04","Jul 04","Aug 04","Sep 04","Oct 04","Nov 04","Dec 04")

    TIA
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CrossTab report covering >1 year (Access 2000)

    Got it - thanks to your help Pat - its not as pretty as yours but it works

    TRANSFORM Sum(TblItemSales.Quantity) AS SumOfQuantity
    SELECT TblItemSales.ItemNumber, TblItemSales.ItemName, Sum(TblItemSales.Quantity) AS [Total Of Quantity]
    FROM TblItemSales
    GROUP BY TblItemSales.ItemNumber, TblItemSales.ItemName
    PIVOT Format([Date],"yyyy/ mm");
    Where its not as pretty as yours is that it shows 2003/8 then 2003/9 then 2003/10 etc but it is in right order and is very useable
    Thanks again for your help
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •