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]

With your example of a lengthy network path, would that be in a value column or in a row header column? I'm not quite sure what you mean when you describe this as metadata.

 

[gdesreu]

With your example of a lengthy network path, would that be in a value column or in a row header column? I'm not quite sure what you mean when you describe this as metadata.

It would be a row header column. Shortening the length in any of these fields can correct the erroneous data reporting in the value fields. I guess it depends how much Ive exceeded this hidden access limitiation. I can reproduce this error in pretty much any of our database as well so I no its not a corruption issue as well as the fact it goes away when I cull the header field data.

 

[gdesreu]

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.

Thanks, I would love that. What I think I will do when time permits is to recreate this issue and post the database for you guys to look at as well as how to fix it so you can see the issue first hand. The problem is I would need to go through the database to remove any proprietary and/or client references. So Ill have to do it during some down time.

 

[xenou]

It would be a row header column. Shortening the length in any of these fields can correct the erroneous data reporting in the value fields. I guess it depends how much Ive exceeded this hidden access limitiation. I can reproduce this error in pretty much any of our database as well so I no its not a corruption issue as well as the fact it goes away when I cull the header field data.

If these are row header columns then it's pretty clear. You cannot have row headers that are more than 64 characters.

 

[gdesreu]

If these are row header columns then it's pretty clear. You cannot have row headers that are more than 64 characters.

Our row headers are very short, its the length of the data in the row header fields that affects the value field. (reports Null where it should be a value.) Nothing exceeds for example
When nearing the XT field limit but not exceeding it the following erroneous result can be reported depending on how much data you store in the rest of your header fields.
Field1 (Row header)
C:\path\path\path\path\.path.xls
Result (Value column)
NULL - This is an error
To fix

Field1 (Row header)
C:\path.xls (shorten your data to something like this)
Result (Value column)
10.1 (This is now the correct value.

Alternative fix:
drop some fields (value or row headers) one at a time until the query returns the correct value.
I can reproduce this error fairly easily and its happened to us about 4 times so far where its caused SERIOUS data integrity issues. Unless you check the XT at 100% every time you run it, which isn't feasible there is chance that you report null values where there should be data exceedances. I cant tell you how bad that is when million dollar decision are being based on these XTs and the data is incorrect. Ill try to get a working database with the error and post it. We might be the first to experience this since we push the XTs to their limits fairly routinely. There are three apparent stages to crosstabs nearing the limit. (runs - Works correctly,runs - provides erroneous null values for sporadic records, does not run - throws too many headers error)​

 

[xenou]

Our row headers are very short, its the length of the data in the row header fields that affects the value field

It sounds like you are saying the row headers are very short, and at the same time that they are too long. Maybe you just have a way of confusing me. I would simply not want long row headers anyway, even without these problems.

C:\path\path\path\path\.path.xls

I would consider this a long row header, especially when it contains special characters.

In general I would prefer never to put colons, backslashes or periods in any row headers if at all avoidable (sticking to the general rules for standard sql identifiers - only alphanumeric and underscore characters and not starting with a number). Since you are having pretty serious problems you probably want to check that headers don't have MSAccess or VBA keywords as names, either - just to rule out the very weird that would be impossible to track if it were causing trouble.

If you are pushing crosstabs to their limit it might be time to consider a more robust database solution anyway. It might be possible to create the report without crosstabs, as another solution. To fix this bug, assuming another explanation cannot be found, you'd have to get in touch with Microsoft (which, actually, may be very receptive to investigating this, especially if you can provide a repeatable test case to demonstrate the problem).

Really, it may be worthwhile re-considering the whole structure here. That's a huge query for Access so why is this in Access and is there a better way to crunch this data?

 

[xenou]

Waking up this morning i also thought that another possibilities to try might be a preprocessing step - basically get some of the aggregation done first, so that the next step (the cross tab part) will be simplified. This might also help with verification of the results, and could give you more options to clean up headers for the next step.

 

[gdesreu]

It sounds like you are saying the row headers are very short, and at the same time that they are too long. Maybe you just have a way of confusing me. I would simply not want long row headers anyway, even without these problems.


I would consider this a long row header, especially when it contains special characters.

In general I would prefer never to put colons, backslashes or periods in any row headers if at all avoidable (sticking to the general rules for standard sql identifiers - only alphanumeric and underscore characters and not starting with a number). Since you are having pretty serious problems you probably want to check that headers don't have MSAccess or VBA keywords as names, either - just to rule out the very weird that would be impossible to track if it were causing trouble.

If you are pushing crosstabs to their limit it might be time to consider a more robust database solution anyway. It might be possible to create the report without crosstabs, as another solution. To fix this bug, assuming another explanation cannot be found, you'd have to get in touch with Microsoft (which, actually, may be very receptive to investigating this, especially if you can provide a repeatable test case to demonstrate the problem).

Really, it may be worthwhile re-considering the whole structure here. That's a huge query for Access so why is this in Access and is there a better way to crunch this data?

The very simple short answer is politics. They've been using this structure for years and for the most part it works. The issue is the end user demands all of the data be in one crosstab for GIS purposes and they don't really care about how errors happen, they only care that they don't ever happen.....which becomes my problem and why we are here. If it were up to me, which it isn't, I would never crosstab data in this manner but that's the process I am tasked with. This is sorta like when people name their files as full on sentences now.... all I can do is tell them that this isn't a good practice, but they keep doing it anyways because they aren't dealing with the repercussions of those decisions.

 

[gdesreu]

Waking up this morning i also thought that another possibilities to try might be a preprocessing step - basically get some of the aggregation done first, so that the next step (the cross tab part) will be simplified. This might also help with verification of the results, and could give you more options to clean up headers for the next step.

I thought this might help as well as Ive seen issues like this before crosstabbing from a query but not from a table containing the same data. Could you give an example of this to make sure I'm not missing something? Thanks

 

[gdesreu]

It sounds like you are saying the row headers are very short, and at the same time that they are too long. Maybe you just have a way of confusing me. I would simply not want long row headers anyway, even without these problems.


I would consider this a long row header, especially when it contains special characters.

In general I would prefer never to put colons, backslashes or periods in any row headers if at all avoidable (sticking to the general rules for standard sql identifiers - only alphanumeric and underscore characters and not starting with a number). Since you are having pretty serious problems you probably want to check that headers don't have MSAccess or VBA keywords as names, either - just to rule out the very weird that would be impossible to track if it were causing trouble.

If you are pushing crosstabs to their limit it might be time to consider a more robust database solution anyway. It might be possible to create the report without crosstabs, as another solution. To fix this bug, assuming another explanation cannot be found, you'd have to get in touch with Microsoft (which, actually, may be very receptive to investigating this, especially if you can provide a repeatable test case to demonstrate the problem).

Really, it may be worthwhile re-considering the whole structure here. That's a huge query for Access so why is this in Access and is there a better way to crunch this data?

To address your comment about the headers. None exceed the limit, but the more fields you have that are lengthy the more this error appears to rear its ugly head. If the limit is 65 and many of them are at 64, the qry runs but will produce sporadic erroneous data values. If you simply alter the data in your query like I did above without changing the query, it will run correctly. Fields have a limit but it appears the row itself also has a mysterious limit which appears to be a summation of whats in all the fields combined. I believe this because the error isn't related to any one field. It can be corrected by reducing the data reported in any of the fields.