Crosstab Text Null Values

[steve21nj]

I have been working on a crosstab query where my text value (not a number) will not return a 0 instead of a null value.

My expression that returns the crosstab value is:

Expr1: Nz(Count(IIf([PropertyType]=" ",0,[PropertyType])))

The PropertyType can be "Destroyed" or "Turn Over". Am I setting up the Expr1 wrong?

The SQL for my query is:

TRANSFORM Nz(Count(IIf([PropertyType]=" ",0,[PropertyType]))) AS Expr1
SELECT qsites.site
FROM (qryPropertyDetails RIGHT JOIN (qsites LEFT JOIN Detention ON qsites.key = Detention.AppSite) ON qryPropertyDetails.DetentionID = Detention.Key) LEFT JOIN qPropertyType ON qryPropertyDetails.PropertyTypeID = qPropertyType.PropertyTypeID
GROUP BY qsites.site
PIVOT qPropertyType.PropertyType;

I have attached two screen shots. In Jan7b I would like all the null values to show 0 instead of nothing. Is this possible?

 

[JHB]

[FONT=&quot]Expr1: Nz(Count(IIf([PropertyType]=" ",0,[PropertyType])))[/FONT]

I think it should be so, (I haven't tried it), then if count return null, then set it to 0.

[FONT=&quot]Expr1: Nz(Count([PropertyType]),0)[/FONT]

 

[steve21nj]

That worked great! Guess I was over thinking a simple thing.

With the crosstab, I was attempting to calculate the totals. I am able to do this fine in the report footer for each text box of the details section.

Is it possible to also sum each individual row? When I attempt to add together, it takes the correct values but doesn't add, simply meshes.

For example.

[B][B]Site     Personal     Medication   Money    Seized Property    Total[/B][/B]
[B]South       2              0          0            1            3[/B]
 
[B]But my display is showing like my attachment:[/B]
 
[B][B]Site     Personal      Medication  Money    Seized Property     Total[/B][/B]
[B]South       2             0         0            1               2001[/B]

It takes the values of 2, 0, 0, and 1 and places them on the line.

=([Personal]+[Medication]+[Money]+[Seized Property])

I tried it with the Nz function, changed the format to currency on both the report and query with no results.

 

[pr2-eugin]

The reason is because Nz(), based on my experience returns a String.. So when you use Field1+Field2+Friled3+Field4 it will give a concatenation of the String values returned.. So what I do is normally use a wrapper function.. that will convert the string to Integer.. something like..

CInt(Nz(FieldName, 0))

See if it works..

 

[steve21nj]

Works great....

the final result for the detail section:

=CInt(Nz([Personal],0)+CInt(Nz([Medication],0)+CInt(Nz([Money],0)+CInt(Nz([Seized Property],0)))))

final result for the footer:

=Sum([Personal])+Sum([Medication])+Sum([Money])+Sum([Seized Property])

thank you very much!

 

[pr2-eugin]

Glad to help Steve, I sometime find puzzled by how the data is displayed.. so tinkering around them lead me to this..

 

[JHB]

Is it possible to also sum each individual row?

Can't you do it in the crosstabel query at once like below?

TRANSFORM .... AS Expr1
SELECT qsites.site, Count([PropertyType]) AS [TotalPropertyType]
FROM ....

 

[steve21nj]

That also works great. I was attempting to further narrow down the results by status, making only "Processed" or "UnProcessed" for the criteria.

When I add this, it only shows the two sites where the property is marked either "Processed" or "UnProcessed", removing the rest of the data. Any thoughts as to why?

With Criteria:

TRANSFORM Nz(Count([PropertyType]),0) AS Expr1
SELECT qsites.site, Count(qPropertyType.[PropertyType]) AS TotalPropertyType
FROM tblStatus RIGHT JOIN ((qryPropertyDetails RIGHT JOIN (qsites LEFT JOIN Detention ON qsites.key = Detention.AppSite) ON qryPropertyDetails.DetentionID = Detention.Key) LEFT JOIN qPropertyType ON qryPropertyDetails.PropertyTypeID = qPropertyType.PropertyTypeID) ON tblStatus.StatusID = qryPropertyDetails.PropertyStatus
WHERE (((tblStatus.Status)="Processed")) OR (((tblStatus.Status)="UnProcessed"))
GROUP BY qsites.site
PIVOT qPropertyType.PropertyType;

Without Criteria:

TRANSFORM Nz(Count([PropertyType]),0) AS Expr1
SELECT qsites.site, Count(qPropertyType.[PropertyType]) AS TotalPropertyType
FROM tblStatus RIGHT JOIN ((qryPropertyDetails RIGHT JOIN (qsites LEFT JOIN Detention ON qsites.key = Detention.AppSite) ON qryPropertyDetails.DetentionID = Detention.Key) LEFT JOIN qPropertyType ON qryPropertyDetails.PropertyTypeID = qPropertyType.PropertyTypeID) ON tblStatus.StatusID = qryPropertyDetails.PropertyStatus
GROUP BY qsites.site
PIVOT qPropertyType.PropertyType;

Not sure if I need to redo my queries or not.

 

[JHB]

Maybe the following can do it, (haven't tried it), and then remove the criteria.

TRANSFORM Nz(iif(tblStatus.Status="Processed" OR tblStatus.Status="UnProcessed", Count([PropertyType]),0),0) AS Expr1
..
..
..

 

[steve21nj]

I get an error saying you tried to execute a query that does not include the specified expression as part of an aggregate function.

SQL:

TRANSFORM Nz(IIf(tblStatus.Status="Processed" Or tblStatus.Status="UnProcessed",Count([PropertyType]),0),0) AS Expr1
SELECT qsites.site, Count(qPropertyType.PropertyType) AS TotalPropertyType
FROM tblStatus RIGHT JOIN ((qryPropertyDetails RIGHT JOIN (qsites LEFT JOIN Detention ON qsites.key = Detention.AppSite) ON qryPropertyDetails.DetentionID = Detention.Key) LEFT JOIN qPropertyType ON qryPropertyDetails.PropertyTypeID = qPropertyType.PropertyTypeID) ON tblStatus.StatusID = qryPropertyDetails.PropertyStatus
GROUP BY qsites.site
PIVOT qPropertyType.PropertyType;

 

[steve21nj]

I added:
Status as a field,
total: Group by

Kinda does what i want, but it duplicates the values on the results.

TRANSFORM Nz(IIf([tblStatus].[Status]="Processed" Or [tblStatus].[Status]="UnProcessed",Count([PropertyType]),0),0) AS Expr1
SELECT qsites.site, Count(qPropertyType.PropertyType) AS TotalPropertyType
FROM tblStatus RIGHT JOIN ((qryPropertyDetails RIGHT JOIN (qsites LEFT JOIN Detention ON qsites.key = Detention.AppSite) ON qryPropertyDetails.DetentionID = Detention.Key) LEFT JOIN qPropertyType ON qryPropertyDetails.PropertyTypeID = qPropertyType.PropertyTypeID) ON tblStatus.StatusID = qryPropertyDetails.PropertyStatus
GROUP BY qsites.site, tblStatus.Status
PIVOT qPropertyType.PropertyType;

 

[pr2-eugin]

Did you try Expr1 as a Group By instead of expression?? I am not sure what I am talking, as I have not dealt with a lot of Cross Tab queries..

EDIT : Never mind.. I just saw your answer..

 

[steve21nj]

My results are still off by duplicating whenever a site has a property

 

[JHB]

It is because of the grouping on the tblStatus.Status
Then it will made a row for each type/value of status.
Do you have some sample data? If, then I can look at it, but I've only MS-Access 2000.

 

[steve21nj]

Sure. The query that I have been testing is: [qhelp3]

The Query should return this instead of the previous:

Site    Personal   Medication   Money  Seized Property  Total
""       0              0        0              0           0
""
""
North     1             1         0              0          2
South     2             0         0              1          3
""
""

 

[JHB]

You've to do it in 2 steps.
First create the below query, call it "qryProperty1Part":

SELECT qsites.site, qPropertyType.PropertyType, IIf([tblStatus].[Status]="Unprocessed" Or [tblStatus].[Status]="Processed",[tblStatus].[Status]) AS Status
FROM tblStatus RIGHT JOIN ((qryPropertyDetails RIGHT JOIN (qsites LEFT JOIN Detention ON qsites.key = Detention.AppSite) ON qryPropertyDetails.DetentionID = Detention.Key) LEFT JOIN qPropertyType ON qryPropertyDetails.PropertyTypeID = qPropertyType.PropertyTypeID) ON tblStatus.StatusID = qryPropertyDetails.PropertyStatus;

Then the second query:

TRANSFORM nz(Count([Status]),0) AS Udtryk1
SELECT qryProperty1Part.site, Count(qryProperty1Part.Status) AS TotalPropertyType
FROM qryProperty1Part
GROUP BY qryProperty1Part.site
PIVOT qryProperty1Part.PropertyType;

 

[steve21nj]

Thank you! I understand the reasoning behind the query and hope to apply this methodology in the future. The results are great. Thank you again!