Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    <> sign in crosstab (Access2000-03)

    I am running Crosstab query
    TRANSFORM Nz(Count(qry_Inactive.DESCR1),0) AS CountOfDESCR
    SELECT qry_Inactive.NAME_DISPLAY AS NAME_DISPLAY, qry_Inactive.Cus_ID AS CUS_ID, Count(qry_Inactive.DESCR1) AS [Total Of DESCR1]
    FROM qry_Inactive
    GROUP BY qry_Inactive.NAME_DISPLAY, qry_Inactive.CUS_ID
    PIVOT qry_Inactive.DESCR;

    I am missing one record where DESCR1 is ZERO.
    When I am executing this crosstab query it showing one column with header "<>" that has all zeros.
    Maybe this is why my record is misiing but how can I get rid of this column?

    Thanks

  2. #2
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: <> sign in crosstab (Access2000-03)

    I think I need to add that DESCR field is a product of the select query
    WHERE
    ((T1.DESCR)="071" Or (T1.DESCR)="080" ))
    and it happened that for one Cus_ID there is no DESCR that are = 071 and 080.
    So I want to see this field anyway with zeros in both 071 and 080 columns...Thanks

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: <> sign in crosstab (Access2000-03)

    You will see <> as a column heading in a crosstab query if there are null values (blanks) in the Column Header field of the crosstab.
    If you want to include some values in the column header even if there are no corresponding records, you will have to specify the Column Headings property of the crosstab query. This will only work if you have a fixed set of column headings, it won't work if the column headings vary all the time.
    To set column headings:
    - Open the crosstab query in design view.
    - Click in an empty part of the upper half of the query window.
    - Activate the Properties windows (View | Properties).
    - Click in the Column Headings property and enter a list of the column headings you want, in the desired order, separated by commas.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: <> sign in crosstab (Access2000-03)

    I see...
    It happened when I had altered quesry to WHERE
    ((T1.DESCR)="071" Or (T1.DESCR)="080" Or Or (T1.DESCR) Is Null))

    Well...
    Now I am still facing a dilemma on how to retrieve correct data.

    Like I said there are some IDs that have
    "<>071" and <>"080"
    but I am still have to display those.

    Data looks like this
    ID 071 080
    AA 1 0
    BB 0 1
    CC 0 0

    so CC does not show at all because of both values are Null. Is there solution?
    Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: <> sign in crosstab (Access2000-03)

    Do you have a table that lists all unique IDs? Or can you create a query that lists the unique IDs?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •