Results 1 to 8 of 8
  1. #1
    ultra5219 is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Feb 2013
    Posts
    45

    Unhappy display report using crosstab query with Parameter...


    hi guys
    im having trouble to open report by using crosstab query with parameter.. i set the criteria by open up design view on crosstab query..then set parameters " Enter Group Code" as Text
    the problem is when i try to create report based on crosstab query with criteria.. unable to create on Report wizard.

    how to create report based on by crosstab query with criteria?
    teach me the steps..sorry im still beginer on access..
    Thanks...

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Review http://allenbrowne.com/ser-67.html

    Stabilizing a crosstab to run perpetually in a report can be tricky. Will your crosstab always have the same columns?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ultra5219 is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Feb 2013
    Posts
    45
    yeah i tried already.. always have same columns.. but the problem, i cant create crosstab query report with criteria: "Enter Group Code" but it i create the crosstab report without any Criteria.. its works fine.. the problem, it dosent work with crosstab with criteria..
    any ideas?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Just the Allen Browne link I referenced. I never built a report with crosstab query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ultra5219 is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Feb 2013
    Posts
    45
    well... im creating report using crosstab query base on timetable.. is there any other way how to display report "Timetable"??

  6. #6
    sberti is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    33

    Report from parameter crosstab query problem

    Hello,
    I'm having a problem creating a Report from a Parameter Crosstab Query too.

    My crosstab query works as desired. It has 2 parameters each from a Form Combobox.

    But when I try to make a Report using the Report Wizard, the 'Available Fields' is blank.
    I've tried it with my Parameter Form and Crosstab Query both open or closed. That doesn't make a difference.

    I made a report from this crosstab query with the parameters removed and it worked, but this is not ideal. To produce the same end-user result, I will have to make 24 separate queries and reports.

    This is my crosstab query:

    PARAMETERS
    [Forms]![frmPenID]![cmbPenId] Text ( 255 ),
    [Forms]![frmPenID]![cmbYearClass] Text ( 255 );

    TRANSFORM Sum([qryHarvestbyPenID-SizePcsLbs].LBS) AS SumOfLBS

    SELECT
    [qryHarvestbyPenID-SizePcsLbs].HarvestNo,
    [qryHarvestbyPenID-SizePcsLbs].PenID,
    [qryHarvestbyPenID-SizePcsLbs].YearClass,
    [qryHarvestbyPenID-SizePcsLbs].ProcessDate,
    Sum([qryHarvestbyPenID-SizePcsLbs].LBS) AS SumOfLBS1

    FROM [qryHarvestbyPenID-SizePcsLbs]

    GROUP BY
    [qryHarvestbyPenID-SizePcsLbs].HarvestNo,
    [qryHarvestbyPenID-SizePcsLbs].PenID,
    [qryHarvestbyPenID-SizePcsLbs].YearClass,
    [qryHarvestbyPenID-SizePcsLbs].ProcessDate

    PIVOT [qryHarvestbyPenID-SizePcsLbs].Size;

    I'd appreciate any suggestions on what changes I can make to make a dynamic report from this query. Thanks.

  7. #7
    sberti is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    33

    Solved

    Hi, Well I've solved this on my own. It might not be the best solution, but it works for me.

    This works because I have only 5 'Sizes' that I need to turn into Columns
    Instead of using a Crosstab Query, I used a Select Query with and IIF() statement for each Column.

    This is my query now:

    SELECT
    [qryHarvestbyPenID-SizePcsLbs].HarvestNo,
    [qryHarvestbyPenID-SizePcsLbs].PenID,
    [qryHarvestbyPenID-SizePcsLbs].YearClass,
    [qryHarvestbyPenID-SizePcsLbs].ProcessDate,
    IIf([Size]="+2.5",[Pieces],0) AS 2_5,
    IIf([Size]="3-4",[Pieces],0) AS [3-4],
    IIf([Size]="4-6",[Pieces],0) AS [4-6],
    IIf([Size]="6-8",[Pieces],0) AS [6-8],
    IIf([Size]="8-10",[Pieces],0) AS [8-10]

    FROM [qryHarvestbyPenID-SizePcsLbs];

    Then I created a Button on my Parameter Form that runs a Macro to Open my Query and then Open my Report.

    It's working. Whew!

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Sorry, ultra, I should have remembered the solution that sberti discovered.

    If you do have only a few group codes then that approach could work for you. Actually, can have up to 255 columns in a query so that's your limit.

    I found another thread on same issue and poster said this link gave them solution http://support.microsoft.com/default...b;en-us;328320
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-21-2011, 03:22 PM
  2. Crosstab Query Parameter
    By BLD21 in forum Queries
    Replies: 1
    Last Post: 06-06-2011, 09:08 AM
  3. Parameter in Crosstab Query
    By RandyG in forum Queries
    Replies: 4
    Last Post: 09-30-2009, 06:40 AM
  4. Replies: 6
    Last Post: 02-20-2009, 11:50 AM
  5. Replies: 4
    Last Post: 01-05-2006, 02:36 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums