Results 1 to 4 of 4
  1. #1
    pickslides is offline Novice
    Windows 10 Access 2013
    Join Date
    Dec 2015
    Location
    Melbourne, Australia
    Posts
    9

    CrossTab not working

    Hi there,



    I have this query, does not return what I am after, yet it does with the same logic in Excel with a Countifs

    Just looking it count the number of times the number 49 appears in field Absence Minutes as my cross tab value


    Code:
    TRANSFORM Sum(IIf([Absence Minutes]=49,1,0)) AS CountOfMins
    SELECT [Roll Mark Data].ID, [Roll Mark Data].Surname, [Roll Mark Data].Preferred, [Roll Mark Data].[Year Level]
    FROM [Roll Mark Data]
    GROUP BY [Roll Mark Data].ID, [Roll Mark Data].Surname, [Roll Mark Data].Preferred, [Roll Mark Data].[Year Level]
    PIVOT [Roll Mark Data].[Roll Date];


    Thanks, Q

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    What are you getting as a result? Suggest provide some example data, the result you are getting and the result you are expecting

    Max number of columns a cross tab can show is 255

  3. #3
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    aren't you supposed to have [Absence Minutes] in your SELECT statement somewhere?


    good luck with your project


    Cottonshirt

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It’s in the transform part of the query, the select part is to do with grouping

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

Similar Threads

  1. Replies: 4
    Last Post: 01-27-2020, 09:49 AM
  2. Replies: 5
    Last Post: 09-22-2017, 11:44 AM
  3. Replies: 2
    Last Post: 05-14-2017, 10:07 AM
  4. Replies: 4
    Last Post: 08-26-2015, 09:55 PM
  5. Replies: 3
    Last Post: 01-29-2013, 04:34 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