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?

 

[xenou]

It sounds like you want to just have shorter headers and all is well. Though I personally wouldn't feel very comfortable about this query either.

To preprocess the data, just do a grouped sum() query (or whatever the aggregate function is that you are using in your value field - sum/avg/min/max).

I haven't done a cross tab in Access for a while but it should be as simple as taking out the cross tab part, more or less. You will end up with a "normal" aggregate query, which you can feed to a cross tab on the next step. You could also collect that data in a table, which would be useful (as an auditable step).

 

[gdesreu]

It sounds like you want to just have shorter headers and all is well. Though I personally wouldn't feel very comfortable about this query either.

To preprocess the data, just do a grouped sum() query (or whatever the aggregate function is that you are using in your value field - sum/avg/min/max).

I haven't done a cross tab in Access for a while but it should be as simple as taking out the cross tab part, more or less. You will end up with a "normal" aggregate query, which you can feed to a cross tab on the next step. You could also collect that data in a table, which would be useful (as an auditable step).

I think that's the next logical step to try and shouldn't be too difficult. I can recreate the error I had, then dump this data to a table and see if the XT bombs versus the intermediate table or not. At the very least this will tell me if I need the intermediate step or not. Ill post back the results when I get that done. - thanks

 

[gdesreu]

It sounds like you want to just have shorter headers and all is well. Though I personally wouldn't feel very comfortable about this query either.

To preprocess the data, just do a grouped sum() query (or whatever the aggregate function is that you are using in your value field - sum/avg/min/max).

I haven't done a cross tab in Access for a while but it should be as simple as taking out the cross tab part, more or less. You will end up with a "normal" aggregate query, which you can feed to a cross tab on the next step. You could also collect that data in a table, which would be useful (as an auditable step).

Better late than never. I have put together a very remedial database, 1 table and one crosstab query that illustrates this error perfectly. There are three identical samples and the only difference between them is I altered the comment length of the comment1 field. Sample 1 and 2 are completely erroneous returns of the data and only sample 3 is correct. Now that I see it in its simplest form, I'm aghast that Microsoft has no stop gap measure for this. This is like entering 2 x 2 on your calculator and it returning 76 as the answer.
What is the best way to post this database so people can see that this is actually happening?

 

[xenou]

If you have access to a file share of some kind that is the best way (these used to be pretty common and nowadays it seems like a lot of people also already have shareable drivespace with Google or Microsoft without even having to use DropBox or 4Shared).

 

[gdesreu]

Here is a dropbox link to the database. If there is a way to identify the rows where this error has occurred that would be fantastic, otherwise the data isn't useable from a scientific standpoint. In fact its the worse case scenario, we think its ok when in fact its producing sporadic erroneous results! Please let me know if you cant download the file. Thanks for taking a look!
https://www.dropbox.com/sh/67nv7sk1l90pgbw/AACsZWNO7y3ZJKdn9pNTMwRRa?dl=0

 

[Micron]

Just read that there is s 2000 character limit for a cross tab ROW. Suggest keeping this in mind when examining the db

 

[gdesreu]

Just read that there is s 2000 character limit for a cross tab ROW. Suggest keeping this in mind when examining the db

That sounds about right, is there a way to count this character limit with VBA?

 

[Micron]

I would say yes, but I'd be interested in looking at the db first/anyway to see if anything else might be recommended. About to do that now.

 

[gdesreu]

I would say yes, but I'd be interested in looking at the db first/anyway to see if anything else might be recommended. About to do that now.

Appreciate it, thanks!

 

[Micron]

Now I'm not sure I understand the issue. When I look at the query, for sample 1 there are no SDomain or DomGroup (etc) values in the table. Not sure if you're saying the lack of data in those fields on the first and second records of the query is the problem. There can't be - there's no value in the table in any record for the Sample1 group for that query column/field. Not only that, it seems the value of 560 is going on the wrong record. It's on the sample3 line, but it seems to me that the value is unique to sample1. If any of that is correct, you might have to do this in steps to get the desired outcome, but I don't know what that is now because you say that if you modify certain record values (shorten them) you get what you want. So does the posted db represent the correct or incorrect results?