Query Trouble - WHERE Statement Returns no Results

[tubbers]

I'm having trouble getting the results I want from a Crystal Report. I'm using v10 and connecting to a SQL Server mdb.

I have a table, "administrations", which contains patient info, medications they've taken, etc. I need to create a report which lists the patients who have had two particular medications during their stay at the hospital.

I started out with the following Selection Formula:

{AHI_ADMINISTRATION.Order_Description} like ["*folic acid*", "*pyridoxine*"]

which returns the right data but it also returns patients who have only had one of the medications, like this

[b]Patient ID    Date             Medication[/b]
Patient 1     5/15/06 1:00pm   Folic Acid 1MG
Patient 1     5/15/06 1:08pm   Pyridoxine 100MG

Patient 2     5/15/06 2:30am   Folic Acid 1MG

Patient 3     5/16/06 7:09am   Pyridoxine 100MG

Patient 4     5/16/06 8:20am   Folic Acid 1MG
Patient 4     5/16/06 2:30pm   Pyridoxine 100MG

I need only Patient 1 and Patient 4's administrations to be returned.

I also tried using the table twice (with an alias) but either I didn't set it up correctly or it was the wrong method to use.

I'm hoping that I'm only missing one little bit of the puzzle to make this report work. Any suggestions?

 

[synapsevampire]

Without knowing the database and your sql abilities, I'll offer a Crystal based solution.

Group by the patient.

Create a formula of and place it in the details:

//ValidPts
if instr({AHI_ADMINISTRATION.Order_Description}, "folic acid") > 0
and
instr({AHI_ADMINISTRATION.Order_Description} , "pyridoxine") > 0 THEN
1
ELSE
0

Then in the Report->Selection Formula-Group place:

sum({@ValidPts},{table.patient}) > 0

-k

 

[tubbers]

I've done what you said, but I didn't get any results which isn't correct.

I've had a few years limited experience working with SQL Server 2000 and my SQL query skills aren't too bad. Is there a better solution than the Crystal-based solution you provided?

The fields in the table that I need are as follows:
site_patient_id (patient identifier)
order_description (name and doseage of medication)
admin_datetime (time of the medication administration)
dose_val (actual amount administered)
admin_dose_units (unit type administed (MG, ML, tab, etc))

The table, with data, looks something like this:

site_      order_             admin_   dose_   admin_dose_
patient_id description        datetime val     units

12345      folic acid 1mg     5/15/06   1      mg
54676      gabapentin 40mg    5/15/06   40     mg
54676      pyridoxine 100mg   5/15/06   100    mg
12345      pyridoxine 100mg   5/15/06   100    mg
78645      folic acid 1mg     5/15/06   1      mg
78645      albuterol inhaler  5/15/06   1      puf 
25486      ipratropium        5/16/06   3      ml
12345      folic acid 1mg     5/16/06   1      mg
12345      pyridoxine 100mg   5/16/06   100    mg

 

[Turkbear]

Hi,
Not sure about SqlServer Syntax, but try a command object ( or view) sometthing like:

Select site_patient_id,count(order_description)
from
Table
where
order_description like ["*folic acid*", "*pyridoxine*"]
group by site_patient_id
having count(distinct order_description) = 2;

Use this as a left-most table and link it to the original table by the site_patient_id and enforce the = link ( both).
( The view/command object will then act like a selection criteria formula, limiting the records from the table to just those site_patient_ids that have 2 distinct order_descriptions)

I do not know if SqlServer uses the 'Having' clause...( My example would work in Oracle, with a minor revision to the LIKE clause)





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

 

[synapsevampire]

Sorry, I coded for both in a single description, use:

//@ValidFolic
if instr({AHI_ADMINISTRATION.Order_Description}, "folic acid") > 0
THEN
1
ELSE
0

//@ValidPyri
if instr({AHI_ADMINISTRATION.Order_Description}, "pyridoxine") > 0
THEN
1
ELSE
0

Now in the group selection formula use:

sum({@ValidFolic},{table.patient}) > 0
and
sum({@ValidPtri},{table.patient}) > 0

-k