Select Statement If survey both an Initial and Termination Survey

[talibm]

We have created a survey data collection form. The Survey type field is single select field. It has values initial, 30 day, 90 day, 6 month, 9 month and Termination. I am trying to select just the data for Surveys that have an initial and termination surveys. We have calculated scoring for each individual form when it is submitted. I want on the scores from the initial and terminations to compare scores after termination. Any help is much appreciated. thanks
talib

 

[SkipVought]

Hi,

We would need more information about your database/table structure, key field(s), maybe some sample data along with expected results from the sample data.

What happens in vagueness, stays in vagueness!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[AndyMc]

If you put into the 'Report / Selection Formulas / Record' the formula:

{yourTable.yourSurveyField} in ["initial","termination"]

this will only allow those two type of surveys into your report.

 

[talibm]

Thanks guys for you response and I apologize for the lack of details. We have a crystal report that has two table, episode_history and worker_rating1. The episode_history table contains program data and the worker_rating1 table contains the survey data. The database is Cache. The episode_history_table is linked to the worker_rating1 table by episode number and patient id number. Each survey has summary calculations for{cc_worker_rating1.w_survey_admin_nbr_Value}. We only want the initial survey scores only if there is a matching termination for the client and vice versa. We only want termination score if the client has an initial survey. Below is the selection criteria that we have in the report.

{cc_worker_rating1.w_rating_date} in {?BeginRateDate} to {?EndRateDate} and
{cc_worker_rating1.w_survey_admin_nbr_Value} in ["Initial", "Termination"] and
{episode_history.program_value} = {?Program(s)}

This pulls in survey scores for an individual survey without a termination survey and it also pulls a termination survey without an initial survey. What we are trying without much success is to pull survey with that have both initial and termination surveys;
YES John Smith Initial
Termination

NO Elmer Fudd Initial

NO Susie Jones Termination

YES May West Initial
Termination
thanks
talib

 

[talibm]

The report is group by program value and client PATID number. summaries are for each client and program in the respective group footers. thanks

 

[lbass]

Go to report->selection formula->GROUP and enter:

Distinctcount({cc_worker_rating1.w_survey_admin_nbr_Value},{table.clientPatID})=2

This will only return patients with both initial and termination values. To compare values you could write two conditional formulas like this:

//{@initialrating}:
If {table.val}="Initial" then {table.rating}

//{@termrating}:
If {table.val}="Termination" then {table.rating}

Then you can calculate the difference:

//{@ratingchange}:
{@termrating}-{@initialrating}

Replace my mock field names with actual field names, of course.

If you need to summarize across patients, be sure to use running totals since patients with only one rating are still in the dataset and will contribute to the standard inserted summaries, while running totals will only summarize group selected data.

-LB

 

[talibm]

Thank you LB. Worked like a charm.I really appreciate you help'
talib
Stay safe and healthy