By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,393 Members | 1,534 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,393 IT Pros & Developers. It's quick & easy.

Replacing Nulls in Crosstabs

P: 5
Hello, I am using Access 2000. The data in my database is normalized, with a column for the month and then a column for the data. In order to display this, I have to run a crosstab to put the months across the top. When I do this, I get all null values in my data, which prevents my summary formulas etc. from working. I have tried various things in the crosstab formula to stop this from happening but have not been successful, nz() doesn't work etc. What I have done as a work around is write another query that calls the crosstab, and put the nz() formulas in there for each month. Clearly, this is less than ideal, as it is doubling the number of queries that I have, and I am also losing the benefit of having my data normalized in the first place, as now I have to edit 12 columns instead of one. Is there any way of getting around this? I have many queries and the delay in processing time is becoming noticable. Thanks, Donna
Jan 4 '07 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hello, I am using Access 2000. The data in my database is normalized, with a column for the month and then a column for the data. In order to display this, I have to run a crosstab to put the months across the top. When I do this, I get all null values in my data, which prevents my summary formulas etc. from working. I have tried various things in the crosstab formula to stop this from happening but have not been successful, nz() doesn't work etc. What I have done as a work around is write another query that calls the crosstab, and put the nz() formulas in there for each month. Clearly, this is less than ideal, as it is doubling the number of queries that I have, and I am also losing the benefit of having my data normalized in the first place, as now I have to edit 12 columns instead of one. Is there any way of getting around this? I have many queries and the delay in processing time is becoming noticable. Thanks, Donna
Donna we will need to see the crosstab query. nz should have worked. Post the query as you originally had it and tell us exactly what the problem was with it.

Mary
Jan 5 '07 #2

NeoPa
Expert Mod 15k+
P: 31,661
I'm not sure, but I seem to recall that Cross-Tab queries return their results as strings.
If your query is not returning the expected data then I'm afraid Nz will have no effect as the empty results will be empty strings ("") rather than Nulls.
These are not the same.

NB. As Mary says in her post, we need the SQL of your existing query(s) if we're to be of any real help.
Jan 5 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.