Count Question

[jj1576]

I have a field testresults.resulttext in my report that displays either a name or the word "acceptable". I'm trying to count the number of times a name shows up provided at same time there is an acceptable result with it.

Example- this would count as 1 for Smith, R but wouldn't count for James, H:

TestResult.result text time

Smith, R 12:15:07 PM
Acceptable 12:15:07 PM
James, H 12:17:18 PM
Acceptable 12:24:42 PM

I have grouped it by time (by the second).

Thanks.

 

[lbass]

How is it that the same field can be populated with the same time but in a different record?

How do you know which name goes with which acceptable record? Is there a common field (other than time)?

-LB

 

[jj1576]

The times are the same because the results are entered from a hand held computer or phone and then synced up to the website. The questions are:

1. Name
2. Acceptable/Unacceptable

Then the operator hits "save" and the time stamp reflects this, thus making it the same for both.

Unfortunately, time is the only thing that links them.
I'm not sure its even possible to do this type of count, but I figured if it was this site was the best place to ask.

Thanks!

 

[lbass]

Okay, if you have a group based on {table.datetime} on change of second, then first create a formula like this:

//{@Acc}:
if {table.name} = "Acceptable" then 1

Then use a formula like this to count:

whileprintingrecords;
numbervar cnt;
if distinctcount({table.name},{table.datetime}) > 1 and
sum({@Acc},{table.datetime}) > 0 then
cnt := cnt + 1;

Then in the report footer use this formula:
whileprintingrecords;
numbervar cnt;

This only works if there can be only one pair for each second. Can there ever be more than one pair for a unique datetime? E.g., can the following ever happen?

Smith, R 12:15:07 PM
Acceptable 12:15:07 PM
James, H 12:15:07 PM
Acceptable 12:15:07 PM

-LB

 

[jj1576]

lbass,

Thanks. There is no chance of more then one pair per second. I entered the formula like so:

whileprintingrecords;
numbervar cnt;
if distinctcount({TestResults.ResultText},{TestResults.TestDate}) > 1 and
sum({@Acc},{TestResults.TestDate}) > 0 then
cnt := cnt + 1;

I get an error saying there must be a group that matches the field. It is referring to the line:

distinctcount({TestResults.ResultText},{TestResults.TestDate})

This is confusing, because testresults.testdate is the only group in the report- did I do something wrong?

Thanks.

 

[lbass]

Sorry, my fault. You have to specify the group interval, as in:

whileprintingrecords;
numbervar cnt;
if distinctcount({TestResults.ResultText},{TestResults.TestDate},"by second") > 1 and
sum({@Acc},{TestResults.TestDate},"by second") > 0 then
cnt := cnt + 1;

-LB

 

[jj1576]

That was the problem. Thanks for your help!