Running Total (count) problem

[jdemmi]

TABLE A, parent table, resembles:

RECORD ID, DATE 1, DATE 2, VALUE3, etc
1234 , 1/1/05, 8/3/05, BLA,
5678, , 2/1/05, 8/3/05, BLA,

TABLE B, child table, resembles:

RECORD ID, DATE3, VALUE4, etc
1234 , 1/1/05, BLA BLA,
1234 , 3/1/05, BLA BLA
1234 , 8/1/05, BLA BLA
5678 , 8/1/05, BLA BLA

I have created a simple report based on the 2 tables. The tables are joind correctly and the report works fine except for my running total.

In the report I am grouping by "RECORD ID" and displaying various columns of data from TABLE A for each "RECORD ID".

I attempted to add a runnig total to the report which COUNTS the # of call IDs which match some criteria (formula option of the running total). This running total (count) works, well sort of. When the relationship between TABLE A and B is 1 to 1 (like my RECORD ID 5678) the count works fine. But when the relationship between TABLE A and B is 1 to many (like my 1234 RECORD ID) then the count reflects the # of child records, which is NOT what I want to count

In a nutshell, using my sample table/records above. I would receive a count of 3 for RECORD ID 1234 and a count of 1 for RECORD ID 5678. I want to receive a count of 1 for both. Make sense?

I have tried to fix this every which way but loose. Any ideas?

Thanks in advance.

-- Jason
"It's Just Ones and Zeros

 

[lbass]

If you have to use the formula option because of a condition, then add an introductory clause to the formula like:

onfirstrecord or
{table.recordID} <> previous({table.recordID}) and///etc.

-LB

 

[jdemmi]

Thanks, but if I tried that correctly it did not work. My forumula condition now looks like this..

onfirstrecord;
{TABLEA.FIELD6} = "Level 1" and {@Response Time Result} > 15 OR
{TABLEA.FIELD6} = "Level 2" and {@Response Time Result} > 15 OR
{TABLEA.FIELD6} = "Level 3" and {@Response Time Result} > 240 OR
{TABLEA.FIELD6} = "Level 4" and {@Response Time Result} > 480

I also tried it like this.
{TABLEA.RECORDID} <> previous({TABLEA.RECORDID}) and
{TABLEA.FIELD6} = "Level 1" and {@Response Time Result} > 15 OR
{TABLEA.FIELD6} = "Level 2" and {@Response Time Result} > 15 OR
{TABLEA.FIELD6} = "Level 3" and {@Response Time Result} > 240 OR
{TABLEA.FIELD6} = "Level 4" and {@Response Time Result} > 480

I still get "extra" record in the count if the conditions are met in any/all of the child records. I only want to apply these conditions to the 1st child record (data which is displayed in my group header). The key may be that the RESPONSE TIME RESULT formula is using a date field in the child table (table B). But it IS using the "first" instance of the date field in TABLE B. I have validated the table numerous times.

-- Jason
"It's Just Ones and Zeros

 

[lbass]

It should be:

(
onfirstrecord or
{TABLEA.RECORDID} <> previous({TABLEA.RECORDID})
) and
(
{TABLEA.FIELD6} = "Level 1" and {@Response Time Result} > 15 OR
{TABLEA.FIELD6} = "Level 2" and {@Response Time Result} > 15 OR
{TABLEA.FIELD6} = "Level 3" and {@Response Time Result} > 240 OR
{TABLEA.FIELD6} = "Level 4" and {@Response Time Result} > 480
)

-LB

 

[jdemmi]

thanks, but it still did not work...

:-(

-- Jason
"It's Just Ones and Zeros

 

[lbass]

Please explain the results you are getting, perhaps by showing some sample data. Saying something doesn't work does not help. Look at your data in the detail section. Is it sorted so that evaluating the first instance of a recordID will return the correct result? Also, where are you placing the running total? It should be in a footer, not a header section.

-LB

 

[jdemmi]

Nevermind....I fixed it by setting the formula of the running total to:

if onfirstrecord = true
then
{REVHDP_CALLS_FULL_TEXT_2.LST_CALLPRIO} = "Level 1" and {@Response Time Result} > 15 OR
{REVHDP_CALLS_FULL_TEXT_2.LST_CALLPRIO} = "Level 2" and {@Response Time Result} > 15 OR
{REVHDP_CALLS_FULL_TEXT_2.LST_CALLPRIO} = "Level 3" and {@Response Time Result} > 240 OR
{REVHDP_CALLS_FULL_TEXT_2.LST_CALLPRIO} = "Level 4" and {@Response Time Result} > 480

-- Jason
"It's Just Ones and Zeros