Results 1 to 7 of 7
  1. #1
    coach32 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    47

    Showing Columns & Rows in Crosstab query

    I have the following code for one of my Crosstab queries. My question relates to the 'PIVOT' part of the code. Am I able to include another PIVOT argument that will show all the rows? Do I add 'AND to the argument? I need to show all rows and columns. Thank you for any assistance you can offer.


    Code:
    TRANSFORM CLng(Nz(Count([01 REG COMP - 32477].Acty_Cntct_Typ_Nm),0)) AS CountOfActy_Cntct_Typ_Nm
    SELECT [01 REG COMP - 32477].Intractn_Sts, Count([01 REG COMP - 32477].Acty_Cntct_Typ_Nm) AS [Total Of Acty_Cntct_Typ_Nm]
    FROM [01 REG COMP - 32477]
    GROUP BY [01 REG COMP - 32477].Intractn_Sts
    PIVOT [01 REG COMP - 32477].Rating In ("P1","P2","P3","P4","P5","P6","P7","P8","P9","PO ASAP");

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't think so. Did you try? Not really sure what you mean by 'all rows and 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
    coach32 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    47
    I used the PIVOT statement to show all columns even if they were zero values; but if the Row category has a zero for all column references, the row doesn't show at all. I need all rows to show whether they have a value or not as I am basing my report on the data in this query.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Zero is a value and should show. Do you mean Null? Provide sample of source data.
    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
    coach32 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    47
    When I run the query I would like to see -
    Status P1 P2 P3 P4
    Unallocated 12 11 0 16
    Pending 15 12 11 0 (This it does do)
    In Progress 0 0 0 0 (This is doesn't show)
    If there is an occasion when the status has no counts of P1 to P4, the row just doesn't show. I need to show the bosses that there are no counts of data for the Status, in this case, 'In Progress'. So in simple terms, I need to show a row of zero's. So the zero is a value because it's showing that the number of records that meet the criteria is zero.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did not answer my questions. Can't help if you don't provide what is requested.
    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.

  7. #7
    coach32 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    47
    My apologies for not explaining myself well enough. After much investigation I have been able to find a solution which reads
    Code:
    TRANSFORM CLng(Nz(Count([01 REG COMP - 34095].Acty_Cntct_Typ_Nm),0)) AS CountOfActy_Cntct_Typ_Nm 
    SELECT Status.Status, Count([01 REG COMP - 34095].Acty_Cntct_Typ_Nm) AS [Total Of Acty_Cntct_Typ_Nm] FROM [01 REG COMP - 34095] 
    RIGHT JOIN Status ON [01 REG COMP - 34095].Intractn_Sts = Status.Status 
    GROUP BY Status.Status 
    PIVOT [01 REG COMP - 34095].Rating In ("P1","P2","P3","P4","P5","P6","P7","P8","P9","ASAP");
    . This has given me all columns and rows even if the count is nothing.

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

Similar Threads

  1. Calculating columns in crosstab query
    By GraemeG in forum Queries
    Replies: 11
    Last Post: 06-05-2011, 06:43 PM
  2. '.' showing as '_' in crosstab query
    By AndyK in forum Queries
    Replies: 3
    Last Post: 04-13-2011, 10:41 AM
  3. Replies: 1
    Last Post: 01-24-2011, 03:03 PM
  4. Replies: 7
    Last Post: 09-24-2010, 07:23 AM
  5. Replies: 1
    Last Post: 02-05-2010, 08:33 AM

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