Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Crosstab - No Data In Field (Access 2003)

    Thank God the lounge is back!!!!
    Help-----------

    I am running a cross tab query for a report. At times there is not data in a field and when this happens I get an error. It says sthe MIcrosoft Jet Database engine does not recognise [<>] as a valid field name or expression. How can I get over this?

    This is the query inSQL

    TRANSFORM Count(tbl_Applications.ProjectID) AS CountOfProjectID
    SELECT tbl_Applications.StaffName, tbl_Applications.Region, Count(tbl_Applications.ProjectID) AS [Total Of ProjectID]
    FROM lup_Criteria INNER JOIN tbl_Applications ON lup_Criteria.CriteriaID = tbl_Applications.CriteriaID
    WHERE (((tbl_Applications.Status)="active") AND ((lup_Criteria.Criteria)="Caseload"))
    GROUP BY tbl_Applications.StaffName, tbl_Applications.Region, lup_Criteria.Criteria
    PIVOT tbl_Applications.SupervisionStandard;

    Thanks in advance

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

    Re: Crosstab - No Data In Field (Access 2003)

    The easiest solution is to specify exactly which values you want to returm for the column field:
    - Open the crosstab query in design view.
    - Click in an empty part of the upper half of the query window.
    - Activate the Properties window.
    - Enter the list of values in the Column Headings property, separated by commas, for example

    "High","Medium","Low"

    The crosstab query will only show these column headings, regardless of whether there are data.

    You can also replace null values in SupervisionStandard with a custom text:

    ...
    PIVOT Nz(tbl_Applications.SupervisionStandard,"Unknown") ;

    and add the custom text to the Column Headings property:

    "High","Medium","Low","Unknown"

    You'd have to adust the design of the report for this.

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Crosstab - No Data In Field (Access 2003)

    Hi Hans
    Thanks for your reply. I have tried my best to get my head around this but I am still having trouble. When I have a Supervision Standard that has no entry. As you would say a null value I need a 0 zero returned into the column.
    At the moment there is no one with an M3 code so the report wont work. But it could be any of the codes at anytime.

    The supevision standards are "M1";"M2";"M3";"DC1";"DC2";"C1";"C2";"C3";"R";"I"; "<>"

    Be patient with me Hans its been a while since I worked with crosstabs and I always did have trouble with them.

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

    Re: Crosstab - No Data In Field (Access 2003)

    Does it help if you change the end of the SQL from
    <code>
    PIVOT tbl_Applications.SupervisionStandard;
    </code>
    to
    code]
    PIVOT tbl_Applications.SupervisionStandard,"0")
    [/code]
    This should replace blanks in the SupervisionStandard field with 0 (or whatever you spedify as second argument in the Nz function).

  5. #5
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Crosstab - No Data In Field (Access 2003)

    Using the SQL below I managed to get the report to run even with null values. It would be nice to put zeros in where there is no data but it isnt life threatening. I am not sure how to incorporate your last suggestion to do this thoug?


    TRANSFORM Count(tbl_Applications.ProjectID) AS CountOfProjectID
    SELECT tbl_Applications.StaffName, tbl_Applications.Region, Count(tbl_Applications.ProjectID) AS [Total Of ProjectID]
    FROM lup_Criteria INNER JOIN tbl_Applications ON lup_Criteria.CriteriaID = tbl_Applications.CriteriaID
    WHERE (((tbl_Applications.Status)="active") AND ((lup_Criteria.Criteria)="Caseload"))
    GROUP BY tbl_Applications.StaffName, tbl_Applications.Region
    PIVOT Nz(tbl_Applications.SupervisionStandard,"Unknown") In ("M1","M2","M3","DC1","DC2","C1","C2","C3","R","I" ,"Unknown");
    Attached Images Attached Images

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

    Re: Crosstab - No Data In Field (Access 2003)

    To return 0 values instead of blanks in the crosstab query, change

    TRANSFORM Count(tbl_Applications.ProjectID) AS CountOfProjectID

    to

    TRANSFORM Nz(Count(tbl_Applications.ProjectID),0) AS CountOfProjectID

  7. #7
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Crosstab - No Data In Field (Access 2003)

    Thanks Hans That worked.

    You are a champ!

    And amazingly I got it to work on another similar report. I'm on a roll now.

Posting Permissions

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