G
Guest
I have a database that records info re patients that are enrolled in
different medical studies. Each study is denoted by an ‘IRB number’. So in
the table with the patients’ information, each patient’s name is lined with
the IRB number of the study the patient enrolled in. What we’re looking to do
on the data entry form is generate a field which shows the next scheduled
visit for any given patient. Unless otherwise indicated, a patient will be
followed-up at 3,6,9,12,15,18,21,24,
30,36,42,48,54,60,72,84,96,108,120,132,144,156,168,180,192,204,216,228,240
month intervals from the time the patient began treatment, which is recorded
on the patient, level table. I said that there could be exceptions to this
follow-up schedule, so in another table, I have a list/table of IRB numbers
and the follow-up schedule they require, e.g.
IRB Follow-Up
11-22-333 3
11-22-333 6
11-22-333 9
11-22-333 12
11-22-333 24
34-22-444 12
34-22-444 24
34-22-444 36
and so forth as warranted.
I’m not concerned here with the formulae needed to compute the
DateOfNextVisit. What is of concern is how to assemble my query/queries so
that when an IRB number is in the table with the F/Ups (above) the Follow-Up
times will be output from there and in the opposite case, when an IRB number
IS NOT in the above table, the selected Follow-Up times will be selected from
the “master list†comprising 3,6,9,12,15,18,21,24,30,36,42,48,54,60,72,84,96,
108,120,132,144,156,168,180,192,204,216,228,240-month follow-ups?
To give this a little more structure, I have three tables I’ll call
‘Patients’, ‘Schedules’ and ‘Formula’.
Schedules has three columns, a Dummy (each value is ‘1’), an IRB# and a
Visit. It would look like the table above with the addition of a column of
‘1’s. ‘Patients’ has Name, IRB#, “DateBegun†and ‘Dummy’ (each value is ‘1’).
Lastly, ‘Schedules’ has two columns, one is ‘Dummy’ number (each value is
‘1’) and the other contains every possible follow-up interval:
3,6,9,12,15,18,21,24,30,36,42,48,54,60,72,84,96,108,120,132,144,156,168,180,192,204,216,228,240 months.
I have it almost licked. I created a query, which uses the three tables as
follows: every table is linked with the remaining pair of tables. ‘Patients’
is linked with ‘Schedules’ via IRB#. ‘Schedules’ is linked with ‘Master’
table via ‘Visit’. ‘Master’ table is linked with ‘Patients’ via the ‘Dummy’
number.
The resulting product is a ‘short list’ of patients having IRB numbers,
which have restricted follow-up schedules! I have created a miniature
database to test out my logic, and I have deliberately added a patient with
an IRB# in ‘Patients’ which does not occur in the ‘Schedules’ table who
should be assigned every possible Follow-Up visit. I guess that’s what I need
to focus on.
Can you find the Achilles heel in that?
different medical studies. Each study is denoted by an ‘IRB number’. So in
the table with the patients’ information, each patient’s name is lined with
the IRB number of the study the patient enrolled in. What we’re looking to do
on the data entry form is generate a field which shows the next scheduled
visit for any given patient. Unless otherwise indicated, a patient will be
followed-up at 3,6,9,12,15,18,21,24,
30,36,42,48,54,60,72,84,96,108,120,132,144,156,168,180,192,204,216,228,240
month intervals from the time the patient began treatment, which is recorded
on the patient, level table. I said that there could be exceptions to this
follow-up schedule, so in another table, I have a list/table of IRB numbers
and the follow-up schedule they require, e.g.
IRB Follow-Up
11-22-333 3
11-22-333 6
11-22-333 9
11-22-333 12
11-22-333 24
34-22-444 12
34-22-444 24
34-22-444 36
and so forth as warranted.
I’m not concerned here with the formulae needed to compute the
DateOfNextVisit. What is of concern is how to assemble my query/queries so
that when an IRB number is in the table with the F/Ups (above) the Follow-Up
times will be output from there and in the opposite case, when an IRB number
IS NOT in the above table, the selected Follow-Up times will be selected from
the “master list†comprising 3,6,9,12,15,18,21,24,30,36,42,48,54,60,72,84,96,
108,120,132,144,156,168,180,192,204,216,228,240-month follow-ups?
To give this a little more structure, I have three tables I’ll call
‘Patients’, ‘Schedules’ and ‘Formula’.
Schedules has three columns, a Dummy (each value is ‘1’), an IRB# and a
Visit. It would look like the table above with the addition of a column of
‘1’s. ‘Patients’ has Name, IRB#, “DateBegun†and ‘Dummy’ (each value is ‘1’).
Lastly, ‘Schedules’ has two columns, one is ‘Dummy’ number (each value is
‘1’) and the other contains every possible follow-up interval:
3,6,9,12,15,18,21,24,30,36,42,48,54,60,72,84,96,108,120,132,144,156,168,180,192,204,216,228,240 months.
I have it almost licked. I created a query, which uses the three tables as
follows: every table is linked with the remaining pair of tables. ‘Patients’
is linked with ‘Schedules’ via IRB#. ‘Schedules’ is linked with ‘Master’
table via ‘Visit’. ‘Master’ table is linked with ‘Patients’ via the ‘Dummy’
number.
The resulting product is a ‘short list’ of patients having IRB numbers,
which have restricted follow-up schedules! I have created a miniature
database to test out my logic, and I have deliberately added a patient with
an IRB# in ‘Patients’ which does not occur in the ‘Schedules’ table who
should be assigned every possible Follow-Up visit. I guess that’s what I need
to focus on.
Can you find the Achilles heel in that?