Count number of values within one field?? 1

[btj]

I have a field that is returning the status of various tests (which are divided into groups). The following values are present in this field:

1=Passed
2=Failed
3=Blocked

The field has a "number" data type. My hope was that I could count the number of times that "1", "2", or "3" appears, but I have not been successful in counting by test groups. I am able to get the total number of tests counted by the Passed/Failed/Blocked value - it is just per group that I can't get.

I have tried using the following formulaes to count the number of tests per group:
=Count(IIf([tcID],"Passed&quot)
=Count(IIf([tcID],"1&quot)
=DCount("[tcID]","1_tbltcMaster","[tcID] = 'Passed1'&quot
=DCount("[tcID]","1_tbltcMaster","[tcID] = '1'&quot

If anyone can help, I would really appreciate it. I know I am close, I just need a little help...

- Ben

 

[jebry]

Hi Ben!

Since the field is numeric use the following:

=DCount("[tcID]","1_tbltcMaster","[tcID] = 1&quot

hth
Jeff Bridgham
bridgham@purdue.edu

 

[btj]

Jeff,
I had tried that before. I pasted your formula again, just in case, but get a "#Error". Do you know why that would be?

 

[MichaelRed]

SELECT Count([tcID]) AS NumOccur
FROM RaceTime_2
WHERE (((([tcID]))=3));


But change the "3" in te where clause to the desired value. In the case where you want to count the occurance of each value, you need a seperate query for each, or build in the count as a parameter and executes it over the range of possible values, retrieving each count in turn.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[btj]

MichaelRed,
Could you expand upon your explanation? I am unsure if your stated Query will work for my purpose.

As an example, the report I am generating now will have two group levels:
1. Test Individual
2. Build of Test

The Results (tcStatus) of the tests are, then, reported in the Detail section.

So, per Tester (first group in report) I want to sum the number of Tests run by Test Individual and, then, I want to find the tcStatus (i.e. 1, 2, 3) of the Tests run.

Every function I have tried has not worked. I am willing to use a Query, but am unsure how I can get as specific as I need through a Query.

Any help you can provide would be greatly appreciated...

Thank you,
Ben

 

[MichaelRed]

Hmmmmmmmmmmmmmmm,

I did fail to replace the table name in the first post. this is corrected below.

SELECT Count([tcID]) AS NumOccur
FROM 1_tbltcMaster
WHERE (((([tcID]))=3));


For the rest, I fail to understand the 'new' question. The above SQL will properly return the count of the number of records in the tabe [1_tbltcMaster] where the field [tcID] has the value (of [3]). In a simplistic process, you could clone the query and replace [3] with the other values (1 & 2?) to have three results, each result is number of records in the specified domain. Rather than me attempting to do 'stuffffff' with your undefined db schema, you could TRY the query and see what results you do (or do not) get. This is about as simple as SQL gets, so I would hope you are able to follow it. Wheather it "works" for your purposes -is up to you.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[btj]

I got it working...thanks for your help and explanation.

- Ben