Using IIF With an aggregate function

[SK-2006]

Hi,
Can anyone please help me out with the IIF concept of MS-ACCESS.
I have a table with the following data:
Name Age Place Dept
AAA 13 Mumbai Accounting
BBB 12 Pune Banking
CCC 15 Delhi Finance
DDD 30 Mumbai Accounting
EEE 25 Delhi Finance

Now i need a query output like
Expected output:
---------------
Dept Total-count Mumbai Pune Delhi

Accounting 2 2 0 0
Banking 1 0 1 0
Finance 2 1 0 1
Now i m using the query:
*******************
SELECT [Dept], sum(test-table.dept) AS ['total count'], Sum(IIf([place]="Mumbai",1,0)) AS Mumbai, Sum(IIf([place]="Pune",1,0)) AS Pune, Sum(IIf([place]="Delhi",1,0)) AS Delhi
FROM [Test-Table]
GROUP BY [Dept];
*******************
and the output which i am getting is:
Dept Total-count Mumbai Pune Delhi

Accounting 2 0 0
Banking 0 1 0
Finance 1 0 1
can anyone let me know as to what change should be there in the query so that i get the correct values in Total-count field with the sum of horizontal counts?

Thanks,
SK.

 

[ByteMyzer]

Try:
SELECT [Dept], count(test-table.dept) AS ['total count'], Sum(IIf([place]="Mumbai",1,0)) AS Mumbai, Sum(IIf([place]="Pune",1,0)) AS Pune, Sum(IIf([place]="Delhi",1,0)) AS Delhi
FROM [Test-Table]
GROUP BY [Dept];

 

[Pat Hartman]

I would use a crosstab query.

 

[SK-2006]

using iif with an aggregate function

Hi,
Thank You for your replies. I had tried with count. this works fine but there is one problem with using count
Eg:
if my table has the following values:
Name Age Place Dept
aaaa 23 Mumbai Accounting
ccc 50 Delhi Finance
fff 20 Delhi Accounting
hhh 10 Delhi Banking
iiii 40 Mumbai Finance
jjj 50 Pune Accounting
kkk 60 Pune Banking
lll 70 Pune Finance
mmm 80 Banking (note the place field is null)
nnn 90 Finance (note the place field is null)
ooo 10 Accounting (note the place field is null)


I am using the query:
SELECT [Dept], count([Test-table].[Place]) AS [Total-count], sum(IIf([place]='Mumbai',1,0)) AS Mumbai, sum(IIf([place]='Pune',1,0)) AS Pune, sum(IIf([place]='Delhi',1,0)) AS Delhi, sum(iif(isnull([place]),1,0)) AS [Blank]
FROM [Test-Table]
GROUP BY [Dept];

This gives me an output:
Dept Total-count Mumbai Pune Delhi Blank
Accounting 3 1 1 1 1
Banking 2 0 1 1 1
Finance 3 1 1 1 1

Here the total-count column doesnt work properly as it doesnt consider the blank field totals for summing up.
Can anyone will please help me out.
how will the cross-tab query work if possible?

Thanks,
SK-2006

 

[Pat Hartman]

Use the crosstab query wizard to build the query.