And formula

[Thant]

All,
I know this is probably simple but Im having a bit of a brain freeze today. I have a report listing drugs that a patient is on. What I need to find is a listing off all patients that had a combination of Plavix and any other drug. Im trying to use the select expert but am unsure of the best way to write this formula. Any help would be appreciated.

 SELECT DISTINCT `tblAC`.`Date Filled`, `tblAC`.`DOB`, `tblAC`.`Drug Name`, `tblAC`.`Med Rec #`, `tblAC`.`Pat Phone`, `tblAC`.`Patient Name`
 FROM   `tblAC` `tblAC`
 ORDER BY `tblAC`.`Patient Name`

Thant

 

[Turkbear]

Hi,
Is there a separate record for each patient/drug combination or does the 'Drug Name' field have more than one drug in it?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Thant]

There is a seperate record for each drug

IE.

PAT John Doe
Date of last fill| Drug 1| Med Rec #|
Date of last fill| Drug 1| Med Rec #|
Date of last fill| Drug 2| Med Rec #|
Date of last fill| Drug 3| Med Rec #|
Date of last fill| Drug 1| Med Rec #|

hope this makes sense
Thant

 

[lbass]

Allow all types of drugs into your report and then create a formula like this:

//{@Plavix}:
if {table.drug} = "Plavix" then 1

Then go to report->selection formula->GROUP and enter:

sum({@Plavix},{table.patientname}) > 0 and
distinctcount({table.drugname},{table.patientname}) > 1

-LB

 

[Thant]

LB
I tried your formula and it returned no results. I do know that the condition I am looking for does exsist within the data set.

if{tblAC.Drug Name}="PLAVIX 75MG TABLET" then 1

and then

DistinctCount ({tblAC.Drug Name}, {tblAC.Patient Name}) > 1 and
Sum ({@PLAVIX 75MG TABLET}, {tblAC.Patient Name}) > 0

I think I understand what your formula is trying to do but am unsure as to why it is not returning any results.

Thanks
Thant

 

[Thant]

All,
I think i can officially kick myself for not seeing my answer. After playing around with this I found that a self referencing join was the way to go. I set a selection formula for tbl.drugname = plavix and added the tbl.drugname1 not = to plavix. This seems to have created the data the way I need it.

if anyone thinks my logic does not work please let me know

Thanks
Thant