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?

 

[Micron]

Forgot to mention that just about everything you read on cross tabs will probably tell you there is a 3 field limit for row headings (the wizard will limit you to 3) even though it's possible to add more than 3. The problem with too many row headings is the cross referencing of data becomes too complex. You have 23 row heading fields - far more than the supposed limit. I don't claim to be a ct query expert by any means (have tried to avoid their complexity all these years) but that may be the reason why the allocation of some of the data points in your query don't seem to match those of the table. So taking this and my last post into consideration, perhaps what you need to do is take a step back and determine what it is you actually want to achieve. That's something you haven't expressed here; the focus has been on Nulls where supposedly, there should be data. I say supposedly because it appears to me that you're getting nulls for a good reason, but I guess the jury is still out on that.

 

[gdesreu]

Forgot to mention that just about everything you read on cross tabs will probably tell you there is a 3 field limit for row headings (the wizard will limit you to 3) even though it's possible to add more than 3. The problem with too many row headings is the cross referencing of data becomes too complex. You have 23 row heading fields - far more than the supposed limit. I don't claim to be a ct query expert by any means (have tried to avoid their complexity all these years) but that may be the reason why the allocation of some of the data points in your query don't seem to match those of the table. So taking this and my last post into consideration, perhaps what you need to do is take a step back and determine what it is you actually want to achieve. That's something you haven't expressed here; the focus has been on Nulls where supposedly, there should be data. I say supposedly because it appears to me that you're getting nulls for a good reason, but I guess the jury is still out on that.

My goal is to identify which rows have reported erroneous data or stop the query from running and identify which record is erroneous. Cross-tabs have been in use for over 30 years with far more than 3 headers so clients expect to see their requested data in that format. I wish I only ever had three fields of header data to deal with but when dealing with scientific data that isn't even close enough to figure out which sample you are looking at much less where it came from, how deep it was taken, what the temp was outside, the grain size, the weight recorded etc., On the data, there should never be nulls reported where there should be data. It should either report the data correctly or not at all. This is a cardinal sin in scientific data. If the query is not capable of handling the data it should stop like it does with the too many headers warning. If it runs without issue and yields sporadic incorrect results then we have reported incorrect results to clients who have paid hundred of thousands of dollars for the correct scientific answers. This is not forgivable from either the client or my corporate standpoint. That's why I'm here trying to find a viable solution. Not reporting data to clients in the format they have used for over 30 years isn't an option.

 

[gdesreu]

Forgot to mention that just about everything you read on cross tabs will probably tell you there is a 3 field limit for row headings (the wizard will limit you to 3) even though it's possible to add more than 3. The problem with too many row headings is the cross referencing of data becomes too complex. You have 23 row heading fields - far more than the supposed limit. I don't claim to be a ct query expert by any means (have tried to avoid their complexity all these years) but that may be the reason why the allocation of some of the data points in your query don't seem to match those of the table. So taking this and my last post into consideration, perhaps what you need to do is take a step back and determine what it is you actually want to achieve. That's something you haven't expressed here; the focus has been on Nulls where supposedly, there should be data. I say supposedly because it appears to me that you're getting nulls for a good reason, but I guess the jury is still out on that.

Also, I just modified the above database to include only 3 headers. I can still get it to produce sporadic incorrect results by including only the sample_ID, comment1 and comment2 as headers, and then using lengthy strings of characters in comment1 and comment2. It appears that access cannot handle lengthy text fields in crosstabs despite having a 255 limit on the field.

 

[Micron]

Not sure I can help further because I can't see where you answered questions from post 30, the most important maybe being "So does the posted db represent the correct or incorrect results?" I shouldn't have to tweak your data or db objects without knowing what to look for, especially after asking, but I did anyway. With this

TRANSFORM Max(TBL_demonstration.result) AS MaxOfExpr4
SELECT TBL_demonstration.comment1, TBL_demonstration.Comment2, TBL_demonstration.Samp_ID
FROM TBL_demonstration
GROUP BY TBL_demonstration.comment1, TBL_demonstration.Comment2, TBL_demonstration.Samp_ID
ORDER BY TBL_demonstration.Samp_ID
PIVOT TBL_demonstration.target;

I get what looks like a satisfactory result, but is that what you did and is that what it should look like??

I find that it's enough to add a period or semicolon at the end of one sample1 comment to produce an extra record for sample1, although I started by adding extra text in comment 1 field. Looking further, I found your original data included a period at the end of a comment. When I removed it, I got a different result again.

So here's what I don't like about your db:
- Your table has no PK and I believe you'd need one in the query to produce better results.
- the table field you want to use as the query column isn't indexed/no dupes but looks like it should be
Other considerations which may be of a lesser but contributing issue:
Not understanding the business as well as you do, also ...
- try moving comments into their own table
- general design principles dictate that a form/report or table shouldn't have to be modified to accommodate a new member for the attributes of an entity (e.g. in your case, if a new element needed to be tested for, you'd have to modify your table). This suggests that something(s) needs to go into its (their) own table(s). In support of this is the fact that there are many records for the same sample with the only variation being the target and result, so the targets probably should be on their own as well as the comments. Thus it isn't the table that would produce many records for a test; rather it would be a query that you'd use as the basis for a ct query. This may even eliminate the problem you have since you wouldn't be basing your ct query on a table that doesn't appear to be correctly designed. Who knows, this might even eliminate the affects of periods/semicolons, etc. when a select query is the basis of your ct query.

Last but not least, if you don't intend to follow that advice (or can't) or even if you do and execute everything properly, you still may be faced with an issue due to the number of row headers you're using. So based on the results I got as previously stated here, the empty fields in a record are not nulls, they're a splitting of the data for whatever reason. Then the answer may be to use a Union query on your ct query to eliminate the empty spots.

As for your stated goal, I don't see that as being possible due to the fact that all it seems to take is a period in a comments field to mess things up. Seems to me the goal should be to eliminate all factors that contribute to the undesired result, be that table design, table relationships (you have none in this case) or whatever else one might try.

That's about all I can think of.

 

[gdesreu]

Not sure I can help further because I can't see where you answered questions from post 30, the most important maybe being "So does the posted db represent the correct or incorrect results?" I shouldn't have to tweak your data or db objects without knowing what to look for, especially after asking, but I did anyway. With this

TRANSFORM Max(TBL_demonstration.result) AS MaxOfExpr4
SELECT TBL_demonstration.comment1, TBL_demonstration.Comment2, TBL_demonstration.Samp_ID
FROM TBL_demonstration
GROUP BY TBL_demonstration.comment1, TBL_demonstration.Comment2, TBL_demonstration.Samp_ID
ORDER BY TBL_demonstration.Samp_ID
PIVOT TBL_demonstration.target;

I get what looks like a satisfactory result, but is that what you did and is that what it should look like??

I find that it's enough to add a period or semicolon at the end of one sample1 comment to produce an extra record for sample1, although I started by adding extra text in comment 1 field. Looking further, I found your original data included a period at the end of a comment. When I removed it, I got a different result again.

So here's what I don't like about your db:
- Your table has no PK and I believe you'd need one in the query to produce better results.
- the table field you want to use as the query column isn't indexed/no dupes but looks like it should be
Other considerations which may be of a lesser but contributing issue:
Not understanding the business as well as you do, also ...
- try moving comments into their own table
- general design principles dictate that a form/report or table shouldn't have to be modified to accommodate a new member for the attributes of an entity (e.g. in your case, if a new element needed to be tested for, you'd have to modify your table). This suggests that something(s) needs to go into its (their) own table(s). In support of this is the fact that there are many records for the same sample with the only variation being the target and result, so the targets probably should be on their own as well as the comments. Thus it isn't the table that would produce many records for a test; rather it would be a query that you'd use as the basis for a ct query. This may even eliminate the problem you have since you wouldn't be basing your ct query on a table that doesn't appear to be correctly designed. Who knows, this might even eliminate the affects of periods/semicolons, etc. when a select query is the basis of your ct query.

Last but not least, if you don't intend to follow that advice (or can't) or even if you do and execute everything properly, you still may be faced with an issue due to the number of row headers you're using. So based on the results I got as previously stated here, the empty fields in a record are not nulls, they're a splitting of the data for whatever reason. Then the answer may be to use a Union query on your ct query to eliminate the empty spots.

As for your stated goal, I don't see that as being possible due to the fact that all it seems to take is a period in a comments field to mess things up. Seems to me the goal should be to eliminate all factors that contribute to the undesired result, be that table design, table relationships (you have none in this case) or whatever else one might try.

That's about all I can think of.

You don't have to tweak any data. The example database has three Identical samples. I literally loaded the same sample three times, then simply changed the comment1 field and the sample ID in two of them to demonstrate the error.
Sample1 is incorrect
Sample2 is incorrect
Sample3 is correct.
All three of the above crosstab rows should be identical except for those two fields. I've demonstrated that access is producing erroneous data even in a remedial database. If there is a header limitation then that is what I need to determine and then check all of our data against to ensure our data is 100% correct. I don't see that information reported in access limitations tables.
This example database represents an extremely remedial version of our data. As the example illustrates, 1 simple data table and one simple crosstab yields data failure so we can therefore eliminate our proprietary databse system as the cause. Our database structure is completely relational (our comments are on separate tables). The example table simply emulates the final output of the amalgamated data for our crosstab purposes. In the database I posted which is a completely separate simple database (1 table, 1 query) and I am able to easily reproduce the error. I can easily produce the error with only two headers as well. So if we can figure out exactly why the example database fails then I can apply that to our systems. It appears to be some sort of character limit to the header fields. If any of the headers or combination of headers exceeds a certain limit, it fails. Its the certain limit rule I am after. You can see this yourself by only using two headers (sampleID and comment1) and simply increasing the length of the comment until it fails. Alternatively you could have more headers but less lengthy and still producer the error but it doesn't appear to be a simple addition of the characters found in the headers as Ive gotten it to fail with different summations of character length. It seems to be a sporadic error. If we don't think its solvable here that's ok. I don't want to waste anyone's time on something that cant be fixed. I figured I would try first before heading over to Microsoft for answers.

 

[xenou]

I don't know of any limits per se on crosstabs. It does appear to be a problem that crops up when the length of your comment field increases beyond a certain point - possibly a bug rather than a defined limit.

 

[gdesreu]

I don't know of any limits per se on crosstabs. It does appear to be a problem that crops up when the length of your comment field increases beyond a certain point - possibly a bug rather than a defined limit.

I agree. This particular bug is most likely going to get me fired unless I can figure out a way to identify that it has occurred. We have people across the nation working out calculations based on the results of these XTs and if I have it wrong it means all their labor and reported conclusions are entirely incorrect. This is like your calculator 7 button entering random numbers every time you press it instead of 7. At least I have a functioning example of the error. Do you know how to escalate this to Microsoft by any chance?

 

[Micron]

Sorry if things are really that serious for you. As I mentioned back in post 26, I read elsewhere that there is a 2000 character per row limit, but I can't recall the source at the moment. If that is the only cause, then perhaps you could run a select query either on or before the ct query and concatenate the entire row and get the character count. What you'd do with that I'm not sure - maybe just flag the row as suspect or invalid. I'd have to go back to your db and prior comments to see if I can make sense of what I saw, since I thought I was messing up results simply by adding a period. When I say messing up, I saw data being split into other rows where all the fields were null except for the one value, which just happened to be a null from a related row. That's why I suggested a union query amongst other suggestions and observations. The problem for me is that if I have questions or suggestions and they're not addressed, I figure I'm not on track at all, or can't make progress. It's easy to lose interest in the problem when that happens. That's my nature, so no apologies for it.

I might have time to do that review later, but for now I have to go out.

 

[xenou]

I only contacted MS once. I think I might have telephoned them or just emailed support. Not sure. It should be possible to get in touch with them both ways, and even by chat I'm guessing.

You may want to post a link to UtterAccess forum. They have most of the Access MVPs listening in there.