count help

[amna77]

hi, in my query i have 11 fields, all those all fields have P and F. what i want to do is I want to count P and F, like how many P's and how many F's. any idea how would i do that.
I try to do count, but not working, I might be doing something wrong. please help me , thanks

 

[Andrew Fergus]

Hi, I got it to work by doing the following :

Create a new query -> design view -> ok -> add the table or query with the 11 fields -> add -> close, Click Query -> View Totals.

In the first field enter this :

P : IIf([1]="P",1,0)+ IIf([2]="P",1,0)+ IIf([3]="P",1,0)+ IIf([4]="P",1,0)+ IIf([5]="P",1,0)+ IIf([6]="P",1,0)+ IIf([7]="P",1,0)+ IIf([8]="P",1,0)+ IIf([9]="P",1,0)+ IIf([10]="P",1,0)+ IIf([11]="P",1,0)

But use your own variable names (I used [1] to [11])

Change the total line from "Group By" to "Sum".

In the second field enter this :

F : IIf([1]="F",1,0)+ IIf([2]="F",1,0)+ IIf([3]="F",1,0)+ IIf([4]="F",1,0)+ IIf([5]="F",1,0)+ IIf([6]="F",1,0)+ IIf([7]="F",1,0)+ IIf([8]="F",1,0)+ IIf([9]="F",1,0)+ IIf([10]="F",1,0)+ IIf([11]="F",1,0)

But use your own variable names (I used [1] to [11])

Again, change the total line from "Group By" to "Sum" to get a grand total of P's and F's - unless you want to see the count of P's and F's for each record, then leave it as group by.

A crude method, but it works.

HTH, Andrew.

 

[amna77]

Thankyou very much, its working, you are the best.
Thankyou.