Crosstab – No Data In Field (Access 2003)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

Does it help if you change the end of the SQL from

PIVOT tbl_Applications.SupervisionStandard;

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).

Reply | Quote

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”);

Reply | Quote

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

Reply | Quote

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.

Reply | Quote