comparing dates in two tables

rbuser

Hi,

I have two tables:
Table 1 lists account information for hospital patients. The fields in
question are admit and discharge dates. There is one record per hospital
admission per patient.
Table 2 has multiple records for each patient, showing eligibility for
various social services programs. The date field I'm interested in is the
month of eligibility. The date is always the first date of the month -
01/01/2003, etc.

The user wants to show patients eligible for social services during the
month(s) the patient is hospitalized. For example, a patient hospitalized
from Jan. 1 through Feb.3 would use eligibility dates 1/1/2003 and 2/1/2003.
The user selects the beginning and ending admit dates via a pass-thru
function. The problem is that I don't know the discharge date until the
first table is read, and then how to access the correct records in the
second table.

Any suggestions?

Thanks,

Kathy Stone
Compliance Data Systems

digitalmetaphors

First, I'd create a calculated field on month in the dataset. Some of our
crosstab demos show how to do this on the DBDemos/BDE database queries. It
sounds as if you want to create a master detail relationship between the
tables. Order by date and use the calculated month field in order to be able
to create a logical group in the detail subreport to break up the individual
data for each month if you want, or if you have other detail data to link in
for each month. The master table is probalby going to be the patient table
(Table 2) and the detail is the admit and discharge table (Table 1). This
way you can list the patients in a master report and show the admit and
discharge info in a detail subreport. I don't know what database you are
using, so if you were using the BDE/Paradox for example, you will need to
use Delphi TTable linking or use Delphi TQuery parameter linking. If you
want to use DADE, then you can very quickly use visual drag and drop query
dataview linking between Table 1(detail) to Table 2(master).




Cheers,

Jim Bennett
Digital Metaphors