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

 

[hymn]

=Abs(Sum([Name]=f))
Not tried or tested but something like it

Hope this helps
Hymn

 

[lespaul]

please post your SQL.

Leslie

 

[Doogles]

It depends on what "count" you're trying to get. Is it (for example):

1) the number of P's in all 11 fields of 1 record, or
2) the number of P's in 1 field of all the records, or
3) the number of P's in all 11 fields of all the records

Here's a solution for each:

1) SELECT Person, Abs(([F1]="P")+([F2]="P")+([F3]="P")+([F4]="P")+...([Fn]="P")) AS PTotal, Abs(([F1]="F")+([F2]="F")+([F3]="F")+([F4]="F")+...([Fn]="F")) AS FTotal
FROM YourTable;
2) SELECT count(*) FROM YourTable WHERE F1="P";
3) SELECT Sum(Abs(([F1]="P")+([F2]="P")+([F3]="P")+([F4]="P")+...([Fn]="P"))) AS PTotal, Sum(Abs(([F1]="F")+([F2]="F")+([F3]="F")+([F4]="F")+...([Fn]="F"))) AS FTotal
FROM YourTable;

Hope this helps!

 

[amna77]

Thank you very much for your help.

Thanks

 

[amna77]

Hi Doogles , actually i put in the expression you said, 3rd one. but its giving me an erron, can you please cheak what i am doing here wrong. thanks

=Sum(Abs(( [1-Work Order /Kan Ban Card Present]="P")+( [2-PIR at machine / Line] ="P")+( [3-PIR filled out per schedule] ="P")+([4-SPC Chart data entered] ="P")+ ([5-SPC Out of Control Action Taken if required including range]="P")+( [6-F / P at Machine / Line & Tagged]="P")+( [7-W / I available]="P")+( [8-Training Records]="P")+( [9-If Gages / Fixtures required are they in Calabration]="P")+( [10-Gages / Fixtures in calbration + / 3 days]="P")))) AS PTotal, Sum(Abs(( [1-Work Order /Kan Ban Card Present]="F")+( [2-PIR at machine / Line] ="F")+( [3-PIR filled out per schedule] ="F")+([4-SPC Chart data entered] ="F")+ ([5-SPC Out of Control Action Taken if required including range]="F")+( [6-F / P at Machine / Line & Tagged]="F")+( [7-W / I available]="F")+( [8-Training Records]="F")+( [9-If Gages / Fixtures required are they in Calabration]="F")+( [10-Gages / Fixtures in calbration + / 3 days]="F")))) AS FTotal


Message comes up "The expression you entered has too many closing parentheses". what should i do, what i am doing wrong here.
Thanks for help.

 

[PHV]

Replace this:
]="P")))) AS PTotal
By this:
]="P"))) AS PTotal
And this:
]="F")))) AS FTotal
By this:
]="F"))) AS FTotal

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GingerR]

you have one too many parens before "As PTotal" there should only be three.
Same for "As FTotal", there should only be three parentheses at the end.

 

[amna77]

Now I have 3 parentheses at the end, but still giving me an error, it says" The expression you entered contains invalid syntax". and its higligthing the AS before P and Ftotal. Any idea why its like that.
Thanks



Expr1: Sum(Abs(([1-Work Order /Kan Ban Card Present]="P")+([2-PIR at machine / Line]="P")+([3-PIR filled out per schedule]="P")+([4-SPC Chart data entered]="P")+([5-SPC Out of Control Action Taken if required including range]="P")+([6-F / P at Machine / Line & Tagged]="P")+([7-W / I available]="P")+([8-Training Records]="P")+([9-If Gages / Fixtures required are they in Calabration]="P")+([10-Gages / Fixtures in calbration + / 3 days]="P"))) AS PTotal,Sum(Abs(( [1-Work Order /Kan Ban Card Present]="F")+( [2-PIR at machine / Line] ="F")+( [3-PIR filled out per schedule] ="F")+([4-SPC Chart data entered] ="F")+ ([5-SPC Out of Control Action Taken if required including range]="F")+( [6-F / P at Machine / Line & Tagged]="F")+( [7-W / I available]="F")+( [8-Training Records]="F")+( [9-If Gages / Fixtures required are they in Calabration]="F")+( [10-Gages / Fixtures in calbration + / 3 days]="F"))) AS FTotal

 

[Doogles]

I'm assuming you would like to get a result for the PTotal and one for the FTotal. So, take Expr1 and break it into to expressions like this:

Expr1: Sum(Abs(([1-Work Order /Kan Ban Card Present]="P")+([2-PIR at machine / Line]="P")+([3-PIR filled out per schedule]="P")+([4-SPC Chart data entered]="P")+([5-SPC Out of Control Action Taken if required including range]="P")+([6-F / P at Machine / Line & Tagged]="P")+([7-W / I available]="P")+([8-Training Records]="P")+([9-If Gages / Fixtures required are they in Calabration]="P")+([10-Gages / Fixtures in calbration + / 3 days]="P"))) AS PTotal

Expr2:Sum(Abs(( [1-Work Order /Kan Ban Card Present]="F")+( [2-PIR at machine / Line] ="F")+( [3-PIR filled out per schedule] ="F")+([4-SPC Chart data entered] ="F")+ ([5-SPC Out of Control Action Taken if required including range]="F")+( [6-F / P at Machine / Line & Tagged]="F")+( [7-W / I available]="F")+( [8-Training Records]="F")+( [9-If Gages / Fixtures required are they in Calabration]="F")+( [10-Gages / Fixtures in calbration + / 3 days]="F"))) AS FTotal

Hope this helps!

 

[GingerR]

Since this is getting all complicated, to troubleshoot just try building the query in the query grid and go from there.

 

[PHV]

In query grid you don't use the AS keyword:
FTotal: Sum(Abs((....)))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244