Sum(IIF statement

[Jay]

I have a large table that I want to query 2 things from. Please be patient
because I am pretty new at this.

I need to count the number of times that something appears in some fields in
the table. Below is what I have tried (keep in mind there are many more
fields to count)...
test: [Exp1]=Sum(IIf([Account number] Is Not Null,1,0)+(IIf([Address] Is Not
Null,1,0))

I would like my output to look like:

KEY TEST
1 2
2 0
3 5
etc...

And then I want to look in each of those same fields and if there is data
concatenate it into one field on the query output and look something like
this:

KEY TEST TEST1
1 2 address, account number
2 0
3 5 address, account number, other field, other field2, etc...

Thanks in advance for any help you can provide

 

[KARL DEWEY]

What is the output field 'Key'? Where is it from?

 

[Marshall Barton]

I have a large table that I want to query 2 things from. Please be patient
because I am pretty new at this.

I need to count the number of times that something appears in some fields in
the table. Below is what I have tried (keep in mind there are many more
fields to count)...
test: [Exp1]=Sum(IIf([Account number] Is Not Null,1,0)+(IIf([Address] Is Not
Null,1,0))

I would like my output to look like:

KEY TEST
1 2
2 0
3 5
etc...

And then I want to look in each of those same fields and if there is data
concatenate it into one field on the query output and look something like
this:

KEY TEST TEST1
1 2 address, account number
2 0
3 5 address, account number, other field, other field2, etc...

Note that the aggregate functions (Count, Sum, etc) ignore
Null values, That means that your Sum(IIf(... Is Not Null,
1,0)) calculations can be done with a simple Count(address),
Count([account number]), etc.

OTOH, it looks like you want to check each record instead of
aggregate over a set of records, so I doubt that you want to
use a Sum or Count at all.

Maybe all you need is to use simple addition and tricky
concatenations something like:

SELECT Key,
IIf([Account number] Is Not Null,1,0)
+ IIf([Address] Is Not Null,1,0)
+ . . . As Test,
Mid((", " + [Account number])
& (", " + [Address])
& (", " + [other field]), 3) As Test1
FROM table