Default value in crosstab query

[Bart Tse]

Hello all,

I have a crosstab query (SQL below) that will display yes/no values fine
except when there is no data for that particular spot(Null I'm guessing). Is
there a way to default the cells to "NO"? Maybe another function besides Max
or something I can give Max?
Thanks
TRANSFORM Max(IIf([Attended],"Yes","No")) AS TheValue
SELECT [Six Sigma Focus Group Attendence].Name AS ID, [Six Sigma Focus Group
Attendence].[Company Represented]
FROM [Six Sigma Focus Group Attendence]
GROUP BY [Six Sigma Focus Group Attendence].Name, [Six Sigma Focus Group
Attendence].[Company Represented]
PIVOT [Six Sigma Focus Group Attendence].[Meeting Date];

 

[Dale Fye]

Bart,

You might want to try using the NZ function. Something like:

TRANSFORM Max(IIf(NZ([Attended], False),"Yes","No")) AS TheValue
SELECT [Six Sigma Focus Group Attendence].Name AS ID, [Six Sigma Focus Group
Attendence].[Company Represented]
FROM [Six Sigma Focus Group Attendence]
GROUP BY [Six Sigma Focus Group Attendence].Name, [Six Sigma Focus Group
Attendence].[Company Represented]
PIVOT [Six Sigma Focus Group Attendence].[Meeting Date];

Dale

 

[Allen Browne]

Try adding Nz() around the field in the TRANSFORM clause.

I'm not sure I understand what this query is supposed to do, but try:
TRANSFORM Nz(Max(IIf([Attended],"Yes","No")), "No") AS TheValue

 

[Bart Tse]

Thanks so much, that worked great

Try adding Nz() around the field in the TRANSFORM clause.

I'm not sure I understand what this query is supposed to do, but try:
TRANSFORM Nz(Max(IIf([Attended],"Yes","No")), "No") AS TheValue

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a crosstab query (SQL below) that will display yes/no values fine
except when there is no data for that particular spot(Null I'm guessing).
Is
there a way to default the cells to "NO"? Maybe another function besides
Max
or something I can give Max?
Thanks
TRANSFORM Max(IIf([Attended],"Yes","No")) AS TheValue
SELECT [Six Sigma Focus Group Attendence].Name AS ID, [Six Sigma Focus
Group
Attendence].[Company Represented]
FROM [Six Sigma Focus Group Attendence]
GROUP BY [Six Sigma Focus Group Attendence].Name, [Six Sigma Focus Group
Attendence].[Company Represented]
PIVOT [Six Sigma Focus Group Attendence].[Meeting Date];