At 11:05 AM 26/08/2005 -0500, you wrote:

><< create generator gen_admit_ordinal;
>
>then ----
>
>create trigger bi_patient_admissions
>active before insert
>as
>begin
> if (new.admit_ordinal is null) then
> new.admit_ordinal = gen_id(gen_admit_ordinal,1);
>end>>
>
>Won't this just increment the Admit_Ordinal lineally, regardless of
>Client_ID?

Yes. At data-migration time, it will be dependent on the arriving data
being in correct date/time order. With this proviso, we are not concerned
about which client_id is involved. Every generator value is unique, so the
ordering for each client's group will be correct if the order of the
incoming set is correct.

When real-time use kicks in, it will be dependent on notes being entered in
chronologically-correct order (which might or might not be the case.)

If you need each CLIENT_ID to have its own, independent "package" of
sequence numbers, i.e. "1" for the first visit..."10" for the 10th, etc.,
see Svein Erling's reply for a way to fix the existing data, *provided*
(again) you are certain that the records arriving in the Firebird table are
in strict date order. It will be safer if ENTRY_DATE has been maintained
as a timestamp (which isn't likely if the source data came from Access):

UPDATE NOTES N1
SET ADMIT_ORDINAL = SELECT COUNT(*) FROM NOTES N2
WHERE N2.CLIENT_ID = N1.CLIENT_ID
AND N2.ENTRY_DATE <= N1.ENTRY_DATE

Do this as the only user connected and *only* if you are certain about the
date order of the data. Otherwise, write a SP to do it. (Myself, I would
do it with a SP, regardless of what I thought I knew about the arrival
order of the data. The SP would loop through a set ordered by CLIENT_ID,
ENTRY_DATE and apply an incrementing counter variable to each row's
ADMIT_ORDINAL, resetting the counter variable when the CLIENT_ID changes.)

After data loading, life gets more complicated if it's essential to
maintain these sequences per CLIENT_ID, rather than using the generator to
record the order in which notes entered the system.

Once you have the data fixed, you would need to place a unique constraint
on (CLIENT_ID, ADMIT_ORDINAL).

Then write a Before Insert trigger to calculate the new value. Include a
handler for the constraint violation to deal with cases where notes are
being entered out of sequence and/or by different users/transactions at the
same time.

If these conditions are likely to occur regularly - new NOTES records being
added outside real time - then this approach is too simplistic to be
reliable: you would still need the ENTRY_DATE (a TIMESTAMP, not a DATE) in
the picture to determine the sequence reliably. If these are clinical
notes we are talking about here, the date is crucial in both the clinical
and legal respects; and if multiple consultations occurred on the same day
it is possible for the progression sequence of the notes to be a matter of
life and death.

But, then, if it's been OK to store the data in Access in the past, it's
probably not a life-and-death thing. BoTox clinic? Poodle pedicures?

./heLen