Our system allows for the scheduling of procedures. The user has an interface where they select 1 or more procedures to be scheduled, they then order them in the order they should be scheduled and set "gap" times between the procedures. I'm looking to build a "Sequence Table" that would keep them from having to set the order and gap time.

Example:

ORDER  PROCEDURE      GAP
  1    UGI SERIES      0      <= No Gap on first procedure
  2    LAP CHOLE       90     <= 90 Minute gap here

Currently the user might not know they need a UGI, until they select the LAP CHOLE and the system comes back and tell them they must schedule a UGI. So now they add the UGI, but it is the second thing, so they change the order and set the gap time. The user interface is based on a listbox (bblistview.ocx) and an Add Button to select the procedure.

What I'd like to be able to do, is automaticly check a sequence table, and look for where a sequence has a UGI and Lap Chole, and then automaticly reorder the user's selection and apply the appropriate gap. If there is not sequence, I could create a sequence automaticly if ok'd by the user.

Here is what I was thinking in terms of the table

seqID          Int 
seqSetID       Int
Order          Int
ProcID         Int
GapMinutes     Int

I think this would work, but I'm not sure what the query would look like to actually implement the sequence check.

I'm looking for any help.

Thanks
Kirk