Performing expression on all (varying number of) fields

[jeremy.groups]

Hi,

I've created a crosstab query, sorting machines by row and parts by
column, with count of each parts as the value. Not every machine has
every part, so there are some null values in certain fields, which I
would like to replace with zeros.

The SQL for this query1 is

TRANSFORM Count(Audit.f5) AS CountOff5
SELECT Audit.f1
FROM Audit
GROUP BY Audit.f1
PIVOT Audit.f5;

where Audit is the table being queried. F1 contains the machines and
F5 contains the different part names.

How do I replace the empty fields with zeros?

This is easy with a second query, e.g. using
Expr1: IIf(IsNull([Fieldx]),"0",[Fieldx])

where each Fieldx would represent each part name

However, I don't want to do this separately for each part, because the
number of different types of parts will vary depending on the Audit -
i.e. there will be varying number of columns in query1 depending on the
contents of the source table (Audit).

Also, it's not possible to do Expr1:
IIf(IsNull(Query1.*),"0",Query1.*), which would effectively do what I'm
looking for.

Any ideas please? I imagine this is quite easy but it's been a while
since I've used MSAccess and I'm a bit rusty.

Thanks in advance

Jeremy

 

[Allen Browne]

Nz() converts Null into another value:

TRANSFORM CLng(Nz(Count(Audit.f5),0)) AS CountOff5
SELECT Audit.f1
FROM Audit
GROUP BY Audit.f1
PIVOT Audit.f5;

The CLng() typecasting helps Access get the data type right.

 

[John Spencer]

Have you tried the following.

TRANSFORM Clng(NZ(Count(Audit.f5),0)) AS CountOff5
SELECT Audit.f1
FROM Audit
GROUP BY Audit.f1
PIVOT Audit.f5;

Nz(Count(Audit.F5),0) will return zero if the count is null.

Access may force the values returned to string values when using the NZ
function. If it does, you can force the value back to a number by using the
CLng function. Of course, if it doesn't or if all you really need is the
display of a number, you can drop the CLng.

 

[jeremy.groups]

Brilliant! Simple when you know how! Thank you very much Allen and
John.