I am having trouble implementing some advice given me last week.
First the setup:
A normalized database where patients are admitted to the PICU.
Each admission has a set of unique identifiers for THAT admission and those
are used as the key fields (3 of them)
Each patient has a medical record number which I don't use as a key field
(too unreliable on admission).
The "Bioinfo" table has some demographic info: birthday, name, medical
record number, etc..
The "Medinfo" table has date/time of admission and date/time of discharge
(amongst other data)
I am looking for patients who bounceback to the PICU in less than 24 hours
after discharge
The data is there, I should be able to retrieve it.

First I query the two tables mentioned above and I get a list of all
patients admitted within a specified timeframe (the user chooses this prior
to launching the query).
The resulting table contains the unique identifiers, medicalrecord number,
date/time of admission and date/time of discharge (etc..)
Because this table is the result of a query, it has no key fields or
indexes.

Now, finally, my problem.
I was advised to use setrange on the medical record number so all the
admissions from each patient can be examined and I can find out if any
patients "bounce back" within a specified period of time (also pre-set by
the user).

How can I use setrange on a table which as no index set?

My assumption is that this is probably simple, I just can't figure it out.
Thanks for your help yet again,

Craig Futterman

Craig,

Give your answer table a name like "temp1". Query temp1 for the specific
medical record number you want records for.

Jean

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

Jean,
I did that and it works. It is just very slow when going through thousands
of records having to do thousands of queries.
Craig

"Jean Friedberg" <jfriedberg (AT) mycomcast (DOT) com> wrote

Can you use the INDEX command to quickly create the index?

---------------------------------------------------------
Tony McGuire

Craig wrote:
<snip>
Craig:
What I do is instead of a query use SQL and insert into. This adds some
steps as you have to create/copy a temp table with index.
Mark B.

Craig wrote:

You should be able to find the matching patients in a single query. Aren't
you just looking for all patients admitted between two timeframes who were
subsequently admitted within 1 day?

Loosely based on your info from the 11:06 post:

SELECT <whatever fields you want>
FROM Medinfo admits,
Medinfo readmits,
BioInfo
WHERE admits.admit_dttm BETWEEN '~dtstart' AND '~dtend'
AND admits.patkey = readmits.patkey
AND admits.discharge_dttm > readmits.admit_dttm + 1
AND BioInfo.patkey = readmits.patkey



--
Larry DiGiovanni

Craig,

In general, I believe the advice you received last week included
restructuring your tables to include appropriate indexes and additional
fields. For example, each admission record should also include a discharge
date/time field and an elapsed time (hours) field. That will make it easy
both to calculate the number of hours and to retrieve those records
exceeding your target value.

Can these results also be achieved simply with queries and without adding
indexes or fields? Not clear, because I'm not sure how easy it would be to
match up a new admission record with the correct older discharge record.
Also, whether a query can be used to "calc" the elapsed hours. If the query
alone approach were workable, I'd be the first to say stick with it. But if
you can't get it to do what you want, then go ahead and add the necessary
indexes and fields.

Taking the steps I've described might seem a bit more difficult now, but
won't seem that hard once they're done and will run much more efficiently.

If you want to discuss offline, please feel free to give me a call.

Jean

Ok Guys I figured it out!

Befcause I was having problems because I needed an indexed table, I used an
insert query to take the data from my non-indexed anwer table to an indexed
table.
Then I used setrange and it worked very quickly. It ran through 834 records
and found 23 bouncebacks in less the 3 seconds! Previously when I used a
Scan loop and a query in each loop it took many minutes.
AND it is accurate.
Thanks to everyone who helped me.
Tonight I will post another message about OLE and WinFax (my next project)

Craig Futterman