Null Values in Crosstab

[gdesreu]

This is a pretty serious issue with access and I'm wondering if there is a way to trap this. Basically, when you near the limit of headers either with fields or length of fields, access Crosstab queries will happily run and produce data but for numerous records it will report null values instead of the actual value. This is as serious as a heart attack for the end user. I'd rather prevent the query from running if this is the case rather than run without an issue and produce incorrect data. Is there a way to trap this error or stop the query from running rather than produce erroneous null values where my data should be?

 

[alansidman]

Look at this video as it shows how to create a crosstab without using the wizard. This may help you to solve your issue.

http://www.datapigtechnologies.com/flashfiles/crosstab.html

 

[Micron]

If you get Null for some fields, it must be a valid result from the intersection of the underlying row/field for that query field?
Or the Null is a result of a calculation that involves a Null (thus the result of the calculation is Null).

You might be able to substitute null for a default value using the Nz function, but that's just a guess since I know not where the Null results are or where they might be propagating from.

 

[gdesreu]

Look at this video as it shows how to create a crosstab without using the wizard. This may help you to solve your issue.

http://www.datapigtechnologies.com/flashfiles/crosstab.html

Thanks alan, but I don't use the wizards for the crosstab. I can reproduce the error in any database. I'm trying to figure out how to trap this monster.

 

[gdesreu]

If you get Null for some fields, it must be a valid result from the intersection of the underlying row/field for that query field?
Or the Null is a result of a calculation that involves a Null (thus the result of the calculation is Null).

You might be able to substitute null for a default value using the Nz function, but that's just a guess since I know not where the Null results are or where they might be propagating from.

I think this is a true glitch in Access that I can reproduce. I have valid values that crosstab correctly. As you add data to a crosstab and begin approaching access field limits either in number of fields or the length in the fields, the crosstab runs but will provide sporadic erroneous null values where there is a valid value that was previously reporting correctly. I realize this is an obscure issue that almost no DB user will encounter but I only see it because of the way my company requires scientific data to be reviewed. Since I cant change that I'm trying to at least trap the error so as not to give a scientist making million dollar decisions on the basis of this crosstab data... erroneous results. In this case, I fixed the crosstab by simply reducing the characters in one of the fields. Since its a sporadic error and it provides data for many other results the error is almost impossible to detect on a mass scale unless I revalidate the data at 100% which isn't feasible. I basically need it to run correctly or not at all until I can figure out which field is causing the offense. I'm guessing the solution for this is to figure out what the access limitation for this is at the point it begins providing erroneous results and stop it from running.

 

[Micron]

In this case, I fixed the crosstab by simply reducing the characters in one of the fields

By any chance are any of the fields that return null of the memo/long text type, or are of that type and are involved in any links between tables involved in this ct query?

I asked, but you shed no light on whether or not calculations are involved.

 

[xenou]

field limit is 255, field name limit is 64. I think you could validate that in advance by counting the lengths of the values that will become your column headers and making sure they do not exceed 64, and counting the number of the values that will become your column headers to make sure they will not exceed 255.

 

[gdesreu]

By any chance are any of the fields that return null of the memo/long text type, or are of that type and are involved in any links between tables involved in this ct query?

I asked, but you shed no light on whether or not calculations are involved.

no they are all number fields. Example 10.1 returns null if nearing the field limit. And the field that returns null can be corrected by reducing the lengths of say a meta data txt field in the same row. For example, query returns null...for the Field2 10.1 value, I change Field1 from "Sediment near shore" to "SE" or basically shorten any of the text fields the query will return correctly. The problem is not knowing that values are returning null when there are reported values. Its an unforgiveable error in my work environment and I'm not sure how to detect that its happened.

 

[gdesreu]

field limit is 255, field name limit is 64. I think you could validate that in advance by counting the lengths of the values that will become your column headers and making sure they do not exceed 64, and counting the number of the values that will become your column headers to make sure they will not exceed 255.

I believe when you exceed these you get the "too many headers" error..which is fine ID rather have it stop, but you can be under those values and have rows that have lengthy meta data as an example and you wont get the too many headers error, it will run normally but provide erroneous results. Maybe I am misunderstanding how to perform the count but I don't believe its related to the column header limits, I believe its related to how many characters are present in each XT row. For example, rows with small amounts of meta data in fields return fine, rows with lengthy meta data return but with erroneous data values. (Just about the worst error I can think of in a Science setting) For example, if I store a lengthy network path in a txt field and I'm near the column limit, it runs but with error, if I reduce the filepath to just a filename, the same exact query runs perfectly fine. This reminds me of how you can store a file with a deep folder structure but you cant copy it or move it....(easily)

 

[Micron]

Well, seems that neither I or Xenou have experienced this before, but we're trying.
If "Sediment Near Shore" is a table field value you're using as a query column header, then I have no suggestion on how to deal with the fact that shortening the field value helps. If it's a table field name, then there are 2 things you could try.
1) avoid the use of spaces and special characters (save for possibly the underscore _ ) in ALL object names. They are problematic for a number of reasons, albeit I've never heard of this situation being one of them. This I would fix (if practical) regardless.
2) Keep the field names with spaces but assign alias names that follow the recommended naming practice of no spaces or special characters. I believe Access will use the alias for a query field name instead of the actual name, but not 100% certain as it's been so long since I had to use aliases.
Aside from that or any other potential design problem we can't see, perhaps your only solution is to migrate the data to Excel, or use Excel to pull it it, perhaps via MSQuery - seeing as how you're trying to transpose row type data to columnar data anyway.

If none of that helps, then I think the only other thing I can offer is to take a look at your db and see if anything comes up.