Results 1 to 6 of 6
  1. #1
    beginner33 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    11

    Reference to non-existing Columns in Crosstab query

    Hi!

    I am not an expert, so I have probably a stupid problem. But it is driving me crazy 😊

    Example:
    I have a Table with, for example repair cases on my equipment. I have created a crosstab query which counts me a number of those repairs cases in each quarter of a year. So my crosstab query use to have one row (Number_of_cases) and 4 Columns (Quarter1, Quarter2, Quarter3, Quarter4).
    So far so good, but since right now is beginning of a year (February), number of cases in Quarter2, Quarter3, and Quarter4 is zero. Consequently those columns are not even created in my crosstab query, because they are empty. Therefore all other objects like Reports, Graphs referring to this non-existing column are now reporting an errors, but works fine in the end of a year 😊.


    How can I avoid that, without silly trick, creating one “shallow” case for each quarter in my original table?
    I want that all columns are created even if they contains 0.

    Thanks in advance for your help!

    Beginner 33

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If you specify column headings your will get a column in your cross tab regardless of the underlying data.

    Have a read here for more info about crosstab queries http://allenbrowne.com/ser-67.html
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    beginner33 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    11
    The query that makes troubles, looks like:

    TRANSFORM Count(Number_of_cases_finished.Datum) AS CountOfOpen_date
    SELECT Number_of_cases_finished.Contractor_ID, Count(Number_of_cases_finished.Datum) AS [Total Of Open_date]
    FROM Number_of_cases_finished
    GROUP BY Number_of_cases_finished.Contractor_ID
    PIVOT "Qtr " & Format([End_date],"q");

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Modify:

    PIVOT "Qtr " & Format([End_date],"q") IN ("Qtr1", "Qtr2", "Qtr3", "Qtr4");
    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
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can also do it in the query properties - in the ribbon, click on the property sheet option and add them there
    Click image for larger version. 

Name:	Capture.JPG 
Views:	12 
Size:	46.7 KB 
ID:	32650

  6. #6
    beginner33 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    11
    It works just fine. Thanks!

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

Similar Threads

  1. Replies: 10
    Last Post: 02-14-2018, 06:21 AM
  2. Crosstab query with no existing entry
    By Omenphaux in forum Queries
    Replies: 1
    Last Post: 06-06-2012, 03:04 PM
  3. Showing Columns & Rows in Crosstab query
    By coach32 in forum Queries
    Replies: 6
    Last Post: 09-11-2011, 07:01 PM
  4. Calculating columns in crosstab query
    By GraemeG in forum Queries
    Replies: 11
    Last Post: 06-05-2011, 06:43 PM
  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