×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Select Statement If survey both an Initial and Termination Survey

Select Statement If survey both an Initial and Termination Survey

Select Statement If survey both an Initial and Termination Survey

(OP)
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

RE: Select Statement If survey both an Initial and Termination Survey

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Select Statement If survey both an Initial and Termination Survey

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.

RE: Select Statement If survey both an Initial and Termination Survey

(OP)
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





RE: Select Statement If survey both an Initial and Termination Survey

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

RE: Select Statement If survey both an Initial and Termination Survey

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

RE: Select Statement If survey both an Initial and Termination Survey

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close