Replace a Value in Crostab Query

  • Thread starter Thread starter cleech
  • Start date Start date
C

cleech

Hello All,
I am using a crostab query to produce data similar to the following
table. What I would like to do is have the results display
differently. The only thing I want to do is have the "X"s display as
"-"s.
1 2 3 4 7 9 11
X X X X X X X
A X X X X X X
U X X X X X X
A X X X X X X
X X X X X X X
X X X X X X X
X X X X X X X
X X X X X X X
X X X X X X X
X X X X X X X
X
X X X
X X X
I X X X X X X


Here is the SQL statment I am using..

TRANSFORM First(dbo_tblConditions.strConditionStatus) AS
FirstOfstrConditionStatus
SELECT dbo_tblConditions.strFunction AS Function,
dbo_tblConditions.strItemChecked AS [General Cat]
FROM dbo_tblConditions
GROUP BY dbo_tblConditions.strFunction,
dbo_tblConditions.strItemChecked
ORDER BY dbo_tblConditions.strFunction
PIVOT dbo_tblConditions.intBuildNum;

I do not want to change the data in the underlying tables. Further, I
would not want to use a report b/c the data (column headings) are not
static. First(dbo_tblConditions.strConditionStatus) is what displays
the "X".

Any help is greatly appreciated. Please let me know if you need
additional info.
 
Try changing the first line to

TRANSFORM IIf(IsNull(First(dbo_tblConditions.strConditionStatus)), Null,
"-") AS
 
Try changing the first line to

TRANSFORM IIf(IsNull(First(dbo_tblConditions.strConditionStatus)), Null,
"-") AS

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)




Hello All,
I am using a crostab query to produce data similar to the following
table.  What I would like to do is have the results display
differently.  The only thing I want to do is have the "X"s display as
"-"s.
1 2 3 4 7 9 11
X X X X X X X
A X X X X X X
U X X X X X X
A X X X X X X
X X X X X X X
X X X X X X X
X X X X X X X
X X X X X X X
X X X X X X X
X X X X X X X
X
X X X
X X X
I X X X X X X
Click to expand...
Here is the SQL statment I am using..
Click to expand...
TRANSFORM First(dbo_tblConditions.strConditionStatus) AS
FirstOfstrConditionStatus
SELECT dbo_tblConditions.strFunction AS Function,
dbo_tblConditions.strItemChecked AS [General Cat]
FROM dbo_tblConditions
GROUP BY dbo_tblConditions.strFunction,
dbo_tblConditions.strItemChecked
ORDER BY dbo_tblConditions.strFunction
PIVOT dbo_tblConditions.intBuildNum;
Click to expand...
I do not want to change the data in the underlying tables.  Further, I
would not want to use a report b/c the data (column headings) are not
static.  First(dbo_tblConditions.strConditionStatus) is what displays
the "X".
Click to expand...
Any help is greatly appreciated.  Please let me know if you need
additional info.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks. I changed it up a bit and it works great. Here is the new
line
TRANSFORM IIf((First(dbo_tblConditions.strConditionStatus))="X","-",
(First(dbo_tblConditions.strConditionStatus)))

And the Results.

1 2 3 4 7 9 11
- - - - - - -
A - - - - - -
U - - - - - -
A - - - - - -
- - - - - - -
- - - - - - -
- - - - - - -
- - - - - - -
- - - - - - -
- - - - - - -
-
- - -
- - -
I - - - - - -
 
Back
Top