Crosstab issue

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to develop a cross tab query - the first time I tried it, I got a
column with the <>. I used the NZ function as the column name -
NZ([field],"new heading") - but when I do that, it makes some of the values
move columns, then if I try to arrange the column headings in the query
properties, access dumps them all into one column. Any ideas?
 
Here is what I get:

TRANSFORM Count([Master Recommendation Table].[Issue Reference Number]) AS
[CountOfIssue Reference Number]
SELECT [Master Recommendation Table].[Audit Group Name]
FROM [Master Recommendation Table]
WHERE ((([Master Recommendation Table].[Issue Status Name])="open"))
GROUP BY [Master Recommendation Table].[Audit Group Name], [Master
Recommendation Table].[Issue Status Name]
PIVOT [Master Recommendation Table].[Report Rating];


TRANSFORM Count([Master Recommendation Table].[Issue Reference Number]) AS
[CountOfIssue Reference Number]
SELECT [Master Recommendation Table].[Audit Group Name]
FROM [Master Recommendation Table]
WHERE ((([Master Recommendation Table].[Issue Status Name])="open"))
GROUP BY [Master Recommendation Table].[Audit Group Name], [Master
Recommendation Table].[Issue Status Name]
PIVOT Nz([Report Rating],"No Rating");

I get similar number, but in different columns when I view the results from
these two.

Biggles

[MVP] S.Clark said:
Post the SQL.


--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting


Biggles said:
I am trying to develop a cross tab query - the first time I tried it, I got a
column with the <>. I used the NZ function as the column name -
NZ([field],"new heading") - but when I do that, it makes some of the values
move columns, then if I try to arrange the column headings in the query
properties, access dumps them all into one column. Any ideas?
Click to expand...
Click to expand...
 
Hi Biggles,

This might explain the differences you see...

I assume Report Rating is a number?

When you used

PIVOT [Master Recommendation Table].[Report Rating];

your column headings were "sorted across" as numbers
(with [Report Rating]'s that were Null, getting "<>" as heading).

When you changed your Pivot clause to

PIVOT Nz([Report Rating],"No Rating");

in applying Nz with string alternative to Null,
all your "numbers" are type cast to *number strings*,
so your column headings are "sorted across" as strings,
and *number strings* sort differently than numbers.

Imagine that [Report Rating] could only be the following:

Null
1
2
12

So your column headings might appear as

<> 1 2 12

As "strings" (Nz([Report Rating],"No Rating") they would sort as

"1"
"12"
"2"
"No Rating"

So your column headings might appear as

1 12 2 No Rating

Is this the effect you are seeing?

Please respond back if I have misunderstood.

Gary Walter

Biggles said:
Here is what I get:

TRANSFORM Count([Master Recommendation Table].[Issue Reference Number]) AS
[CountOfIssue Reference Number]
SELECT [Master Recommendation Table].[Audit Group Name]
FROM [Master Recommendation Table]
WHERE ((([Master Recommendation Table].[Issue Status Name])="open"))
GROUP BY [Master Recommendation Table].[Audit Group Name], [Master
Recommendation Table].[Issue Status Name]
PIVOT [Master Recommendation Table].[Report Rating];


TRANSFORM Count([Master Recommendation Table].[Issue Reference Number]) AS
[CountOfIssue Reference Number]
SELECT [Master Recommendation Table].[Audit Group Name]
FROM [Master Recommendation Table]
WHERE ((([Master Recommendation Table].[Issue Status Name])="open"))
GROUP BY [Master Recommendation Table].[Audit Group Name], [Master
Recommendation Table].[Issue Status Name]
PIVOT Nz([Report Rating],"No Rating");

I get similar number, but in different columns when I view the results from
these two.

Biggles

[MVP] S.Clark said:
Post the SQL.


--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting


Biggles said:
I am trying to develop a cross tab query - the first time I tried it, I got a
column with the <>. I used the NZ function as the column name -
NZ([field],"new heading") - but when I do that, it makes some of the values
move columns, then if I try to arrange the column headings in the query
properties, access dumps them all into one column. Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
I'm sorry but I cannot tell where these
values line up with which columns from your post.

What version of Access are you using?
I have seen 97 do strange things with
crosstabs to the point that I wonder if
anyone should use them in 97 except in
their simplest form.

If it is 97, and if values are aligned correctly
w/o the NZ, I would accept the "<>" for null,
and just change labels on my form or report
for that column to "No Rating."

If it is not 97, then, if these are a distinct
set of column headings, have you tried using
the IN clause in your Pivot clause?

PIVOT
Nz([Master Recommendation Table].[Report Rating],"No Rating")
IN ('No Rating', 'Needs', 'Out', 'Sat', 'Unrated', 'Unsat');

I'm sorry I cannot be of more help.

Good luck,

Gary Walter
Biggles said:
Report Rating is a string, here are the values I get:

Raw (No NZ)

Audit Group Drop <> Needs Out Sat Unrated Unsat
Business Bank 10 4
California 1 1
FWS 12 8 2
Service Company 17 17 2 12 4 2
Texas 2

TRANSFORM Count([Master Recommendation Table].[Issue Reference Number]) AS
[CountOfIssue Reference Number]
SELECT [Master Recommendation Table].[Audit Group Name]
FROM [Master Recommendation Table]
WHERE ((([Master Recommendation Table].[Issue Status Name])="open"))
GROUP BY [Master Recommendation Table].[Audit Group Name]
PIVOT [Master Recommendation Table].[Report Rating];

Using the NZ
Audit Group Drop Needs No Rating Outs Sat Unrated Unsat
Business Bank 10 4
California 1 1
FWS 12 8 2
Service Company 17 17 2 12 4 2
Texas 2

TRANSFORM Count([Master Recommendation Table].[Issue Reference Number]) AS
[CountOfIssue Reference Number]
SELECT [Master Recommendation Table].[Audit Group Name]
FROM [Master Recommendation Table]
WHERE ((([Master Recommendation Table].[Issue Status Name])="open"))
GROUP BY [Master Recommendation Table].[Audit Group Name]
PIVOT NZ([REPORT RATING],"No Rating");

Any ideas?
Biggles
Click to expand...
I used the NZ function as the column name -
NZ([field],"new heading") - but when I do that, it makes some of the values
move columns, then if I try to arrange the column headings in the query
properties, access dumps them all into one column
Click to expand...
 
use Null (with no single quotes)

so..to send "<>" to the last column

IN ( 'Needs', 'Out', 'Sat', 'Unrated', 'Unsat', Null);

at least this works in Access 200x
(untested in 97)

and, as a general rule, these type of queries are
used to provide sources for forms or reports
(or controls on them).
In any of these, you can design them so columns
appear in any order you want. I don't recall
a single Access app where it made sense (*to
me*) to just open a query for the user.

meaning no offense and hoping this helped,

Gary Walter

Biggles said:
Ok, decided to follow what you mentioned about the report. If I wanted to
put the column headings all in one order using the column headings property,
how could I signify a null value, i.e. <>. I found that using "<>" does not
work.

Biggles

Gary Walter said:
I'm sorry but I cannot tell where these
values line up with which columns from your post.

What version of Access are you using?
I have seen 97 do strange things with
crosstabs to the point that I wonder if
anyone should use them in 97 except in
their simplest form.

If it is 97, and if values are aligned correctly
w/o the NZ, I would accept the "<>" for null,
and just change labels on my form or report
for that column to "No Rating."

If it is not 97, then, if these are a distinct
set of column headings, have you tried using
the IN clause in your Pivot clause?

PIVOT
Nz([Master Recommendation Table].[Report Rating],"No Rating")
IN ('No Rating', 'Needs', 'Out', 'Sat', 'Unrated', 'Unsat');

I'm sorry I cannot be of more help.

Good luck,

Gary Walter
Click to expand...
Click to expand...
 
Thanks, I will remember that for later. However, I found an easier method -
convince the users they don't need anything that has a null in the record.

Biggles

Gary Walter said:
use Null (with no single quotes)

so..to send "<>" to the last column

IN ( 'Needs', 'Out', 'Sat', 'Unrated', 'Unsat', Null);

at least this works in Access 200x
(untested in 97)

and, as a general rule, these type of queries are
used to provide sources for forms or reports
(or controls on them).
In any of these, you can design them so columns
appear in any order you want. I don't recall
a single Access app where it made sense (*to
me*) to just open a query for the user.

meaning no offense and hoping this helped,

Gary Walter

Biggles said:
Ok, decided to follow what you mentioned about the report. If I wanted to
put the column headings all in one order using the column headings property,
how could I signify a null value, i.e. <>. I found that using "<>" does not
work.

Biggles

Gary Walter said:
I'm sorry but I cannot tell where these
values line up with which columns from your post.

What version of Access are you using?
I have seen 97 do strange things with
crosstabs to the point that I wonder if
anyone should use them in 97 except in
their simplest form.

If it is 97, and if values are aligned correctly
w/o the NZ, I would accept the "<>" for null,
and just change labels on my form or report
for that column to "No Rating."

If it is not 97, then, if these are a distinct
set of column headings, have you tried using
the IN clause in your Pivot clause?

PIVOT
Nz([Master Recommendation Table].[Report Rating],"No Rating")
IN ('No Rating', 'Needs', 'Out', 'Sat', 'Unrated', 'Unsat');

I'm sorry I cannot be of more help.

Good luck,

Gary Walter
Click to expand...
Click to expand...
Click to expand...
 
Back
Top