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
2  1320  
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
NeoPa 32,556
Expert Mod 16PB
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: aaj |
last post by:
Hi
I am probably going to regret asking this because I'm sure you are going to
tell me my design is bad 8-) ah well we all have to learn....
anyway
I often use Nulls as a marker to see if...
|
by: mike |
last post by:
I'm doing what I thought was a simple GROUP BY summary of fairly simple
data and the my numbers aren't working out
Some results are showing up <NULL> when I know the data is in the
database
...
|
by: RCS |
last post by:
I have a crystal report which works fine in the Crystal design. The
crosstabs display the summarized fields as horizontal, but when I run the
same report in dotnet the crosstabs display the...
|
by: ss.wisch |
last post by:
Ok here's the thing... I have a vBulletin forum database... there is a
table in there called "post" which has all of my forums posts in it.
What I have are many posts with regular titles like "Oh...
|
by: Rhino |
last post by:
I am working with SQL Functions in DB2 for Windows/Linux/UNIX (V8.2.1) and
am having a problem setting input parameters for SQL Functions to null in
the Development Center.
My simple function,...
|
by: John Baker |
last post by:
Hi:
While the crosstabs capability appears very powerful, I don't see how I can get the
information into a form or anything else. Is there some way I can do a cross tabs, and
then use the...
|
by: PST |
last post by:
Here's a problem I'm trying to deal with:
I'm working on a Frontpage 2000 website for a boat handicapping
system, built in Access 97.
What I'm trying to accomplish is:
The user enters a...
|
by: Cliff72 |
last post by:
I need to fill in the nulls in the batch field the value from the
record immediately
preceding the null one ie replace the nulls with the preceding value
until I hit a record with a value in...
|
by: HSXWillH |
last post by:
I have a table, Times. fields are: Horse, Track, HTime
Basically, my goal is to find out the difference between any track's timing, using only horses that have run at each of the 2 user-selected...
|
by: lotus18 |
last post by:
Hello World!
I have 2 crosstabs on my crystal report. My crosstabs are using only one table. But, I want my second crosstab to retrieve only distinct values. Is this possible? If not, my other...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
