Group Counting in a Report

[June7]

Apparently, between the crosstabs and UNION, all data is converted to text which can't be summed or averaged. I tried using CInt to convert values back to number but CInt can't handle the nulls and empty strings in field. I can get this much to work:



Select Command, Commander, Inspector, CDbl(Nz(FSL,0)) AS FSLCt, InspCt, Total, FSL0, FSL1, FSL2, FSL3, FSL4
From [Q_Ratio_D1_Avg_Comm_Crosstab]
UNION Select Command, Commander, Inspector, CDbl(Nz(Total_Insp,0)), Blank, Number, FSL0, FSL1, FSL2, FSL3, FSL4
From [Q_Ratio_D1_Avg_Comm_Insp_Crosstab];

Trying to use CDbl and Nz on InspCt, Total, Blank, Number fields causes type mismatch error. Maybe because in the crosstabs InspCt, Blank, Number are calculated as empty string. Why? Use Null and then the CDbl an Nz functions should work on these fields as well.

So glad I don't have need for crosstab queries!

[Huddle]

Maybe I uploaded the wrong db. I renamed it and reuploaded it. The Querie name is Q_Ratio_Avg_Union

[June7]

Ooops! You must have seen my post before I edited it. I accidentally opened the old db after downloading the revision in post 15. I did the edit within 5 minutes of original posting (and a minor edit this AM).

Review my last post again.

[Huddle]

To use Null would it look like Blank: "" rather than Blank: " "? If not how do you make it Null?

[June7]

An empty string is:
Blank: ""

This is a space (which Access probably trims so the result is empty string):
Blank: " "

This is a field with Null value:
Blank: Null

I don't allow empty strings in tables and would never calculate a field as empty string.

[Huddle]

Changing it gives me a "Data type mismatch" in the union query. The other queries work fine. I even went back to my old union query and got the data mismatch error.

[Huddle]

SUCCESS!!!! I turned one of the crosstab queries into a select querie. Replaced the Null with zeros. Went back to my old Union formula. Everything transferred right and now works.