Need some help figuring this one out.

[Fadius]

OK. I need to come up with some formula(s) to narrow down my selection. Here are the field I have in my report

Lastname, MR, Date of Duke, Counter, Score, Record Date, Record Plan

Now I only need t see the records that have a Counter of 1 and 2, Score is 30 or greater, the Date of Duke and the Record Date are within 7 days of each other, and Record plan is either MD Notified or Note sent to Physician.

Only if all the above criteria are met, do I want them to show up in the report.

There will be a record for for each Counter. Example:

Smith, 12345, 04/25/03, 2, 32.5, 04/30/03, MD Notified
Smith, 12345, 12/12/02, 1, 45.3, 12/14/02, MD Notified
Doe, 987654, 06/15/03, 1, 30.7, 06/15/03, MD Notified



Smith would get counted as he would have a counter of 1 and a 2, the dates are within 7 days, MD was notified, and score was over 30.

Doe would not get counted as he only had a counter of 1.


I hope this makes sense. I am really confused on this one.

 

[knitwit]

Have you tried using the record selection with your criteria? Just pick your fields and apply the criteria you list in your information. To take care of the date issue - create a variable Daycount with a formula of Date of duke - record date. Then under the record selection pick the variable where < 7

 

[Fadius]

My biggest stumbling block is how do I get it so only records that have both a counter of a 1 and a 2 to show? A customer would have to have to records, one would be a 1 and the other would be a 2.

 

[raufmohammed]

Hi,

You requirement needs the data to be compared on different records. Try the following.

Split your reports into two section. The main report retrieving the data only for the counter of 1 and 2. This you can achieve by grouping on primary key (in your case MR) and using the "having" clause of SQL statements. Something like

select MR from table where counter in (1,2) group by MR having count(*) > 2. This will give you only records having 1 & 2 provided the combination of MR and counter are unique.

Then pass the MR to the subreport and apply the rest of the condition which are fairly straight.

 

[lbass]

You could create a record selection formula like:

{table.score} > 30 and
abs(datediff("d",{table.DateofDuke},{table.RecordDate})) <= 7 and
{table.recordplan} in ["MD Notified","Note sent to Physician"]

Then group on a patient ID field (an ID would be better than a name, since multiple patients might have the same name) and then go to report->edit selection formula->GROUP and enter:

sum({table.count},{table.patientID}) = 3

This assumes that "count" is actually a database field. If it is a running total, then change the group selection formula to:

count({table.patientID},{table.patientID}) = 2

Either approach should return only those patients with at least two records.

-LB