Is There A Problem With Crosstab Queries And Rounded Numbers?

[mcl]

I have a crosstab query which does a distribution of temperature data. I
round the temps and then have a query that gives me a count of temps by
month and temp. I then use that query in a crosstab. Things work OK until I
get below 0. (All working in degrees F and BTW this is for Andrews AFB.
There are 490701 total obs between 1943 and 1996)
Here is the below 0 output. I assume it will not line up right:
Tempf Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Annual

1 14 2 16
0 5 2 5
-0 7 2
-1 1 7
-2 13 14
-3 7 7
-4 8 8
-5 1 1
0 0 0 0 0 0 0 0 0 0 0 0 0

I have no idea how the above will display but first of all there are two
rows for 0. I assume that for +0 and -0 (ie. 0.3 and -.03) although the way
round works they should all be just 0. The 2 days of 0 for Jul does not
exist in the database. It's a phantom. I've checked and rechecked. I thought
I had discovered some bad data in the database but that's not true. Also,
look at the annual totals. In some cases it doesn't add up correctly.
Going back to the phantom 2 for July, it depends on how much data I
include. I set up the query where it asks for beginning and ending years.
The phantom 2 didn't show until I went from 1943 to 1994. From 1943 to 1993
there are no problems. There is only one 0 row and all the totals add up.
When I go from 1943 to 1994 or greater the problem shows up. BUT, if I have
it go from say 1990 to 1996 again it's OK. There seems to be some
complicated relationship with how many records I include. When I say
compliacted I mean complicated. I ran it for 1980 to 1990 and for 1990 to
1996 with no problem. When I tried 1980 to 1996 it showed up again. Note
that my year criteria parameters do are >= []and <= []so it's not like I'm
skipping anything.
Also, I assume it gives me the last line telling me there are 0 nulls?

 

[Michel Walsh]

Hi,

Only to remember that Boolean logic, in SQL, has three values, True
( -1, in Jet), False ( 0) and Unknown ( Null ). A test with Null generally
returns Null. A WHERE clause keeps the record only if the criteria is TRUE
(not kept for False, neither for Null). So, if you have null data moving,
unchecked, around, you may get strange result, indeed. Also, COUNT(*) counts
the NULL, COUNT(FIeldName) does not count the nulls in the result.

Have you check the months ( or the date format, or they are nice
date_time, no null allowed ) ? If you have fixed you columns headings, you
may have some data slipping through invalid month name (only to be checked
if you use month name as raw data, unlikely if you use date_time as raw
data).


Hoping it may help,
Vanderghast, Access MVP

I have a crosstab query which does a distribution of temperature data. I
round the temps and then have a query that gives me a count of temps by
month and temp. I then use that query in a crosstab. Things work OK until I
get below 0. (All working in degrees F and BTW this is for Andrews AFB.
There are 490701 total obs between 1943 and 1996)
Here is the below 0 output. I assume it will not line up right:
Tempf Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Annual

1 14 2 16
0 5 2 5
-0 7 2
-1 1 7
-2 13 14
-3 7 7
-4 8 8
-5 1 1
0 0 0 0 0 0 0 0 0 0 0 0 0

I have no idea how the above will display but first of all there are two
rows for 0. I assume that for +0 and -0 (ie. 0.3 and -.03) although the way
round works they should all be just 0. The 2 days of 0 for Jul does not
exist in the database. It's a phantom. I've checked and rechecked. I thought
I had discovered some bad data in the database but that's not true. Also,
look at the annual totals. In some cases it doesn't add up correctly.
Going back to the phantom 2 for July, it depends on how much data I
include. I set up the query where it asks for beginning and ending years.
The phantom 2 didn't show until I went from 1943 to 1994. From 1943 to 1993
there are no problems. There is only one 0 row and all the totals add up.
When I go from 1943 to 1994 or greater the problem shows up. BUT, if I have
it go from say 1990 to 1996 again it's OK. There seems to be some
complicated relationship with how many records I include. When I say
compliacted I mean complicated. I ran it for 1980 to 1990 and for 1990 to
1996 with no problem. When I tried 1980 to 1996 it showed up again. Note
that my year criteria parameters do are >= []and <= []so it's not like I'm
skipping anything.
Also, I assume it gives me the last line telling me there are 0 nulls?

 

[mcl]

The months in the data are 1-12 only. I get the "alpha" months by joining
with a simple look up table 1-12, Jan-Dec. You get the idea.
I'm answering at home right now. Will check things out some more when I get
back to work Tuesday (Monday is a holiday).

Hi,

Only to remember that Boolean logic, in SQL, has three values, True
( -1, in Jet), False ( 0) and Unknown ( Null ). A test with Null generally
returns Null. A WHERE clause keeps the record only if the criteria is TRUE
(not kept for False, neither for Null). So, if you have null data moving,
unchecked, around, you may get strange result, indeed. Also, COUNT(*) counts
the NULL, COUNT(FIeldName) does not count the nulls in the result.

Have you check the months ( or the date format, or they are nice
date_time, no null allowed ) ? If you have fixed you columns headings, you
may have some data slipping through invalid month name (only to be checked
if you use month name as raw data, unlikely if you use date_time as raw
data).


Hoping it may help,
Vanderghast, Access MVP

I have a crosstab query which does a distribution of temperature data. I
round the temps and then have a query that gives me a count of temps by
month and temp. I then use that query in a crosstab. Things work OK

until

I
get below 0. (All working in degrees F and BTW this is for Andrews AFB.
There are 490701 total obs between 1943 and 1996)
Here is the below 0 output. I assume it will not line up right:
Tempf Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Annual

1 14 2 16
0 5 2 5
-0 7 2
-1 1 7
-2 13 14
-3 7 7
-4 8 8
-5 1 1
0 0 0 0 0 0 0 0 0 0 0 0 0

I have no idea how the above will display but first of all there are two
rows for 0. I assume that for +0 and -0 (ie. 0.3 and -.03) although the way
round works they should all be just 0. The 2 days of 0 for Jul does not
exist in the database. It's a phantom. I've checked and rechecked. I thought
I had discovered some bad data in the database but that's not true. Also,
look at the annual totals. In some cases it doesn't add up correctly.
Going back to the phantom 2 for July, it depends on how much data I
include. I set up the query where it asks for beginning and ending years.
The phantom 2 didn't show until I went from 1943 to 1994. From 1943 to 1993
there are no problems. There is only one 0 row and all the totals add up.
When I go from 1943 to 1994 or greater the problem shows up. BUT, if I have
it go from say 1990 to 1996 again it's OK. There seems to be some
complicated relationship with how many records I include. When I say
compliacted I mean complicated. I ran it for 1980 to 1990 and for 1990 to
1996 with no problem. When I tried 1980 to 1996 it showed up again. Note
that my year criteria parameters do are >= []and <= []so it's not like I'm
skipping anything.
Also, I assume it gives me the last line telling me there are 0 nulls?

 

[Otis B. Driftwood]

Hello Marc,

If you have access to any version of sql server 2000 (including
msde) get your Access data into it (or link your Access tables)
and use the RAC utility.RAC is somewhat similar to Access
crosstab query but much more powerful with many features and
options.RAC can also easily solve many different data manipulation
problems without any complicated sql coding.But if need be you can
extend RAC with any valid server sql since they are highly integrated.
RAC is a native S2k utility with its own gui or can be executed in
batch just like any S2k stored procedure.

RAC v2.2 and free tool QALite new v1.00.12 @
www.rac4sql.net