Results 1 to 2 of 2
  1. #1
    fret hack is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    13

    Crosstab trouble with count function

    Hello All,

    Im trying to create a crosstab query that shows a "0" value for rows not represented in the data. The design view of the query looks like this
    Click image for larger version. 

Name:	Design..JPG 
Views:	7 
Size:	56.3 KB 
ID:	37737

    The SQL is here:

    TRANSFORM IIf(IsNull(Count([qQSTATS_master]![InOut]))=True,0,Count([qQSTATS_master]![InOut])) AS [Value]
    SELECT tsupPTypeSTATS.PTID, tsupPTypeSTATS.PType
    FROM tsupPTypeSTATS LEFT JOIN qQSTATS_master ON tsupPTypeSTATS.PType = qQSTATS_master.[Permit Type]


    GROUP BY tsupPTypeSTATS.PTID, tsupPTypeSTATS.PType
    PIVOT qQSTATS_master.InOut In ("In","Out");

    And the datasheet result is here:
    Click image for larger version. 

Name:	Datasheet.JPG 
Views:	7 
Size:	28.8 KB 
ID:	37738

    The trouble that I am having is that there is no data for the rows "LT", "LA", "MPGW" and LOC"; however, there is a "1" in the "In" column where I would like to have a "0". The "Out" column is calculated just fine.

    Any ideas?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Q1 should show all the counts an correct NZ there.
    Q2 should be the Xtab query using Q1

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

Similar Threads

  1. Replies: 2
    Last Post: 06-30-2016, 06:38 PM
  2. Trouble Shooting Union and Distinct Count Queries
    By EcologyHelp in forum Queries
    Replies: 5
    Last Post: 04-21-2016, 09:45 AM
  3. Trouble with Crosstab Query
    By cbende2 in forum Access
    Replies: 3
    Last Post: 06-18-2015, 11:44 AM
  4. Crosstab with sum and count function?
    By alibanks in forum Queries
    Replies: 1
    Last Post: 01-31-2015, 09:25 PM
  5. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 PM

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