Each patient has a table called Clininfo.db
3 key fields and a field Called ClinicalCode (also keyed)
Each patient can have anywhere from 0 to max # of choices available (36 I
think) to put in each table

I want to be able to query the ClinInfo table for how many patients have 1
or more than 1 of these criteria.
For example: asking the question How many patients have had ALI and CVL
(Arterial lines and Central Venous Lines). This is an "AND" Query. I have
arbitrarily put a limit on 5 clinical criteria (for my own sanity).

I have put together a manual query that works. It looks like this:

Query
ANSWER: :ktables:ClinInfoAnswer.db

:ktables:Clininfo.DB | Status | Year | Unique# |
:ktables:Clininfo.DB | ClinicalCode |
:ktables:Bioinfo.DB | Status | Year | Unique# | HospitalNumber | FirstName
:ktables:Bioinfo.DB | LastName | Birthdate | Sex |
:ktables:Medinfo.DB | Status | Year | Unique# | AdmitDateTime |
EndQuery

This Query actually works when I put data in instead of variables.
In my code I would do a CASE ENDCASE and have code with 5, 4, 3, 2 or 1
query line(s) on the clininfo table depending on how many variables.
I was thinking there must be an easier way to do this.
Any ideas?
Thanks,
Craig Futterman

Craig,

The problem is probably that not all 5 rows are always involved. Rather
than doing the five rows, why not one row with either:

a) a join to a criteria table in which the ClinicalCodes of interest are
added

OR

b) a single variable which is built to look like: stCode = "ALI OR CVL"

....?

Liz

"Craig" <craig.futterman (AT) nospam (DOT) comcast.net> wrote:

Liz and Michael,
Thanks for your replies.
If I'm not mistaken, the criteria table gives you an "or" query.
I need an "And" query.
Craig

On Jan 29, 6:01 pm, "Liz McGuire" <l... (AT) paradoxcommunity (DOT) com> wrote:

Craig wrote:

Two ways come to mind. Untested.

Way 1: Criteria table

Create a criteria table riv:critcodes with one column clinicalcodes.

Put the choices in there. Get the number of choices (somenumber)

select c."status", c."Year", c."unique#", b.HospitalNumber, b.FirstName,
b.LastName, b.BirthDate, b.Sex, m.AdmitDateTime, count(*) numcodes
from ":ktables:Clininfo.db" c,
":ktables:Bioinfo.DB" b,
":ktables:MedInfo.DB" m,
"riv:crit.db" crit
where c.status = b.status
and c."Year" = b."Year"
and c."Unique#" = b."Unique#"
and c.status = m.status
and c."Year" = m."Year"
and c."Unique#" = m."Unique#"
and c.clinicalcode = crit.clinicalcode
group by c."status", c."year", c."unique#", b."HospitalNumber",
b."FirstName",
b."LastName", b."BirthDate", b."Sex", m."AdmitDateTime"
having count(*) > ~somenumber

~somenumber doesn't have to be the number of criteria they've entered. You
could use this to do a search to see who's had, say, any 5 of the 9 codes
they enter, or whatever.

Way 2: Without Criteria table

select c."status", c."Year", c."unique#", b.HospitalNumber, b.FirstName,
b.LastName, b.BirthDate, b.Sex, m.AdmitDateTime, count(*) numcodes
from ":ktables:Clininfo.db" c,
":ktables:Bioinfo.DB" b,
":ktables:MedInfo.DB" m
where c.status = b.status
and c."Year" = b."Year"
and c."Unique#" = b."Unique#"
and c.status = m.status
and c."Year" = m."Year"
and c."Unique#" = m."Unique#"
and c.clinicalcode in ( a list of codes you assemble )
group by c."status", c."year", c."unique#", b."HospitalNumber",
b."FirstName",
b."LastName", b."BirthDate", b."Sex", m."AdmitDateTime"
having count(*) > ~somenumber

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.