countiff based on multiple criteria

[ritimajain]

hi i have query which has field EXCHID,CELL,BAND,BLSTATE
I want count of cells in following situations
1. count if band=900 and blstate= is not MBL for specified EXCHID and CELL
2. Count if band=900 and blstate = MBL for specified EXCHID and CELL
3. count if band=1800 and blstate= is not MBL for specified EXCHID and CELL
4. Count if band=1800 and blstate = MBL for specified EXCHID and CELL
Give me a way of doing this like in report format .When i write SQL for this , access choked
SELECT Query1.EXCHID, Query1.CELL, Query1.BLSTATE, Query1.[BAND], Count(Query1.CELL) AS CountOfCELL


FROM Query1
GROUP BY Query1.EXCHID, Query1.CELL, Query1.BLSTATE, Query1.[BAND]
HAVING (((Query1.BLSTATE)="MBL") AND ((Query1.[BAND])="GSM900"));

please help i need it very urgently

[JoeM]

Here is how I typically do something like this:
1. Create a new query and add the EXCHID and CELL fields to it.
2. Create calculated fields for your four counts, basically create an IIF statement for each one that returns 1 if the condition is met, and a zero if it is not met. So when you finish this, you will have four separate calculated fields.
3. Make your query an Aggregate Query by clicking on the Totals button. This will add a Totals row under each field with the words "Group By" under each one.
4. Change the Totals row function from "Group By" to "Sum" for all four of your calculated fields.
5. View your results. You should see total counts for all four conditions for each EXCHID/CELL combination.

[ritimajain]

i m new to access please give me a example how to implement 2) option i mean how to write IFF Statment
here is my db D2.zip
https://app.box.com/s/kzomgrw55m7xsjwr4zb3
Please help

[JoeM]

I don't have the ability to download files from my current location, but your first condition should look something like this:

Condition1: IIf((Right([BAND],3)="900") And (NZ([BLSTATE],"ZZZ")<>"MBL"),1,0)

[ritimajain]

i m still new to access , i dont knw where to put this statement.. but i made a report based on query1 and add a text box to it and when i put this statement in control source of text box it gives me syntax error

and please can you please explain what this statement means it would be helpful for me to write other one too

Condition1: IIf((Right([BAND],3)="900") And (NZ([BLSTATE],"ZZZ")<>"MBL"),1,0)

[JoeM]

It is a calculated field that you want to put in your query. All 5 steps I gave you above you do directly in the query.
Check out Access' built-in help files for more information on "Calculated fields", and/or do some Google searches to return links like these: http://office.microsoft.com/en-us/ac...005188023.aspx

[ritimajain]

Condition1: IIf([BAND]="GSM900" And ([BLSTATE]<>"MBL" Or IsNull([BLSTATE])),1,0)
when i write this in query and add sum to total field nothing happen

[JoeM]

Did you follow all the other steps I outlined?
Can you post the actual SQL code you are currently using in your query (I should be able to tell from that if you are doing it correctly)?

[ritimajain]

i m able to solve my problem ..
i m using this SQL
SELECT Query3.EXCHID, Query3.MOfixed, Query3.CELL, Query3.[BAND], Query3.BLSTATE, IIf([BAND]="GSM900" And ([BLSTATE]<>"MBL" Or IsNull([BLSTATE])),1,0) AS [900 a], IIf([BAND]="GSM1800" And ([BLSTATE]<>"MBL" Or IsNull([BLSTATE])),1,0) AS [1800 a], IIf([BAND]="GSM900" And ([BLSTATE]<>"MBL" Or IsNull([BLSTATE])),1,0)+IIf([BAND]="GSM1800" And ([BLSTATE]<>"MBL" Or IsNull([BLSTATE])),1,0) AS [total a], IIf([BAND]="GSM900" And [BLSTATE]="MBL",1,0) AS [900 i], IIf([BAND]="GSM1800" And [BLSTATE]="MBL",1,0) AS [1800 i], IIf([BAND]="GSM900" And [BLSTATE]="MBL",1,0)+IIf([BAND]="GSM1800" And [BLSTATE]="MBL",1,0) AS [Total i]
FROM Query3;



SELECT Query4.EXCHID, Query4.CELL, Sum(Query4.[900 a]) AS 900_active, Sum(Query4.[1800 a]) AS 1800_active, Sum(Query4.[total a]) AS total_active, Sum(Query4.[900 i]) AS 900_inactive, Sum(Query4.[1800 i]) AS 1800_inactive, Sum(Query4.[total i]) AS total_inactive
FROM Query4
GROUP BY Query4.EXCHID, Query4.CELL;

THANXX 4 D HELP..!