Record Selection 1

[kinvie]

I am using Cyrstal Reports 10.0 to connect to our electronic medical record with an ODBC connection. I am trying to get patients that come for appointments during a certain timeframe and who may have one of three diagnosis. My formula includes some OR statements.

{RPTAPPT.APPTDATE} >=Date(DateAdd("d",+1,(CurrentDate))) and
{RPTAPPT.APPTDATE} < Date(DateAdd("d",+3,(CurrentDate))) and
{RPTAPPT.APPTSTATUS} = 0.00 and
({HTN_EXTRACT.HOMELOCATION} = 1270480543003540 or {CAD_EXTRACT.HOMELOCATION} = 1270480543003540 or
{DIABETES_EXTRACT.HOMELOCATION} = 1270480543003540)

The problem is that I never get patients who have just the second or third Homelocation, I only get patients who have the first homelocation and may or maynot have the second or third.

Is there a way for me to get what I really want, patients who are at any of these three homelocations?

Thanks in advance for your help.

 

[lbass]

Try:

{RPTAPPT.APPTDATE} >=CurrentDate + 1 and
{RPTAPPT.APPTDATE} < CurrentDate + 3 and
{RPTAPPT.APPTSTATUS} = 0.00 and
1270480543003540 in [{HTN_EXTRACT.HOMELOCATION},{CAD_EXTRACT.HOMELOCATION},{DIABETES_EXTRACT.HOMELOCATION}]

-LB

 

[kinvie]

Thanks LB but when I try this I get only patients that have all three of the homelocations and not ones that have only one of the three. This is driving me crazy because I thought your method would work.

 

[dgillz]

What does "have all three of the home locations" mean? Isn't this field there for every patient? Do you have null values?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[lbass]

The formula I showed should not just return people who have all three, but instead should return those with any of the three. How are your tables linked? What kind of joins, and linking from what fields to what fields?

-LB

 

[kinvie]

The first table is the OBS_Extract table, this is then left join linked to these three tables [{HTN_EXTRACT.HOMELOCATION},{CAD_EXTRACT.HOMELOCATION},{DIABETES_EXTRACT.HOMELOCATION}] The tables are linked by a PID (patient identifier). All patients I want are in the OBS_Extract tables and then I want those patients who also appear in any one of the three left joined tables. Patients may not be in all three tables, they are for sure in the obs_Extract and may be in any combination of the other three.

Thanks for helping me out with this, I am at a complete loss.

 

[lbass]

You didn't mention how the rptappt table is linked.

-LB

 

[kinvie]

It is equal joined linked to the obsextract table by the PID. It is the first table and then everything flows from their.

 

[lbass]

I think the problem is that when you select on the outer tables, you are basically undoing the left joins. Instead, use a command as your datasource. I would start by creating a report that uses only one of the three tables, along with the rptappt table and the OBS_extract table. Add your selection criterion on just the one table, and add the fields you need in the report to the detail section (sloppily if you like). Then copy the resulting query from database->show SQL query into a new report where you choose your datasource->add command. I would set it up like this:

select 'HTN' as type, HTN_EXTRACT.`homelocation`, HTN_EXTRACT.`otherfield`
from HTN_EXTRACT
where HTN_EXTRACT.`HOMELOCATION` = 1270480543003540
union all
select 'DIABETES' as type, DIABETES_EXTRACT.`homelocation`, HTN_EXTRACT.`otherfield`
from DIABETES_EXTRACT
where DIABETES_EXTRACT.`HOMELOCATION` = 1270480543003540
union all //add query for third table

Your table fields and your from clause will include the other two tables that you mentioned in each component of the union all, and the where clause will include your other selection criteria. Basically, once you paste in the query for one table, you can then copy it in each part of the union all, and just change the table name throughout that component.

-LB

 

[kinvie]

IT took me a little bit to get this to work but it worked great and I was able to get this request done. I really appreciate your help!!!