Complex database, hopefully an easy answer.

Discussion in 'Microsoft Access Database Table Design' started by Guest, Dec 24, 2006.

  1. Guest

    Guest Guest

    I'm using a database to track patients enrolled in research protocols at our
    medical center. Any one patient may enroll in several protocols. I have a
    patient table (with PtID as the primary key), a protocol table (with
    ProtocolID as a primary key), and a table that tracks a patient's activities
    relative to a specific protocol; this 3rd table includes PtID & ProtocolID as
    foreign keys. I have combo boxes in this 3rd table so I can recognize which
    patient & which protocol it refers to at a quick glance.

    Within my patient table, I include appointment data. I've built a query & a
    report to display the patient appointment list and a form to input data to my
    patient table. I can enter a new patient (including appointment time) into my
    patient form & it appears in my patient table, but the appointment doesn't
    show up when I run my query. I have to go back & manually enter the patient
    into my 3rd table which includes the 2 foreign keys. I can use the dropdown
    feature in this third table to find the new patient within the combo box, by
    the way.

    Any help you could give me would be greatly appreciated. Because the
    database is huge (870 patients & 155 protocols), I shudder to think about
    starting from scratch. If you need additional info, please tell me exactly
    what you need to know.

    MUCH thanks.

    --
    Thanks for your time!
     
    Guest, Dec 24, 2006
    #1
    1. Advertisements

  2. Guest

    Jeff Boyce Guest

    I may not understand your situation quite well enough yet, so take these
    notions with a grain or two...

    Patients
    PtID
    ...(more patient-only data - e.g., FName, LName, DOB)

    Protocol
    ProtocolID
    Title
    Description
    ...(more protocol-only data -- ? date begun, ...)

    Enrollment
    EnrollmentID
    PtID
    ProtocolID
    DateEnrolled (date this patient enrolled in this protocol)

    EnrollmentDetail
    EnrollmentDetailID
    EnrollmentID (which "Enrollment" does this detail refer to)
    AppointmentDate (for which appointment date for this enrollment)
    ...(whatever details you need to keep that are specific to the
    appointment)

    I don't quite get why you would keep (?)multiple appointment information
    stuffed into the Patient table -- wouldn't you need to just keep adding
    columns for each appointment? That would be a very spreadsheetly thing to
    do, and would require constant re-modeling of your tables, forms, queries,
    reports, etc.


    --
    Regards

    Jeff Boyce
    Microsoft Office/Access MVP
    http://mvp.support.microsoft.com/

    Microsoft IT Academy Program Mentor
    http://microsoftitacademy.com/

    Microsoft Registered Partner
    https://partner.microsoft.com/

    "NC_Sue" <> wrote in message
    news:...
    > I'm using a database to track patients enrolled in research protocols at

    our
    > medical center. Any one patient may enroll in several protocols. I have a
    > patient table (with PtID as the primary key), a protocol table (with
    > ProtocolID as a primary key), and a table that tracks a patient's

    activities
    > relative to a specific protocol; this 3rd table includes PtID & ProtocolID

    as
    > foreign keys. I have combo boxes in this 3rd table so I can recognize

    which
    > patient & which protocol it refers to at a quick glance.
    >
    > Within my patient table, I include appointment data. I've built a query &

    a
    > report to display the patient appointment list and a form to input data to

    my
    > patient table. I can enter a new patient (including appointment time) into

    my
    > patient form & it appears in my patient table, but the appointment doesn't
    > show up when I run my query. I have to go back & manually enter the

    patient
    > into my 3rd table which includes the 2 foreign keys. I can use the

    dropdown
    > feature in this third table to find the new patient within the combo box,

    by
    > the way.
    >
    > Any help you could give me would be greatly appreciated. Because the
    > database is huge (870 patients & 155 protocols), I shudder to think about
    > starting from scratch. If you need additional info, please tell me exactly
    > what you need to know.
    >
    > MUCH thanks.
    >
    > --
    > Thanks for your time!
     
    Jeff Boyce, Dec 24, 2006
    #2
    1. Advertisements

  3. Guest

    John Vinson Guest

    On Sun, 24 Dec 2006 13:41:00 -0800, NC_Sue
    <> wrote:

    >I'm using a database to track patients enrolled in research protocols at our
    >medical center. Any one patient may enroll in several protocols. I have a
    >patient table (with PtID as the primary key), a protocol table (with
    >ProtocolID as a primary key), and a table that tracks a patient's activities
    >relative to a specific protocol; this 3rd table includes PtID & ProtocolID as
    >foreign keys. I have combo boxes in this 3rd table so I can recognize which
    >patient & which protocol it refers to at a quick glance.


    ok... just be sure you're aware of the (rather stringent!) HIPAA
    requirements concerning data privacy and data integrity. These can be
    a pain in the neck (and elsewhere!) for developers but the penalties
    for violation are *not* trivial.

    >Within my patient table, I include appointment data.


    Ummmmm.... Unless each patient gets one and only one appointment,
    that's the wrong place. You really should have a separate table of
    appointments, with a PatientID and other fields about the appointment.

    >I've built a query & a
    >report to display the patient appointment list and a form to input data to my
    >patient table. I can enter a new patient (including appointment time) into my
    >patient form & it appears in my patient table, but the appointment doesn't
    >show up when I run my query.


    Then there's something wrong with the Query or with the Form. Could
    you describe your table structure?

    >I have to go back & manually enter the patient
    >into my 3rd table which includes the 2 foreign keys. I can use the dropdown
    >feature in this third table to find the new patient within the combo box, by
    >the way.


    You can and should use a Subform. Table datasheets are NOT designed
    for data entry or display and should not be used for this purpose. You
    could use a Form based on the Patients table, with a Subform based on
    the Appointments table; this subform would use the PatientID as the
    master/child link field, and have a combo box for the protocol.

    >Any help you could give me would be greatly appreciated. Because the
    >database is huge (870 patients & 155 protocols), I shudder to think about
    >starting from scratch. If you need additional info, please tell me exactly
    >what you need to know.


    870,000 records in a table is getting pretty big. 8,700,000 is
    getting huge. You're still *tiny* in database terms.

    John W. Vinson[MVP]
     
    John Vinson, Dec 24, 2006
    #3
  4. Guest

    Guest Guest

    I'm up to speed on HIPAA & you're right - it can be a pain. Thanks for the
    reminder.

    Each patient DOES have only one appointment I care about (at least at one
    time), so I included this info in the patient table. It is unique to the
    patient and while (s)he will have another appointment once he sees the doc
    this time around, I figured I could change after (s)he comes in.

    My query & report seem to work OK on patients already included within the
    patient table, but it's the new guy that gets added in that doesn't show up.
    So I suspect it's the form that's the problem.

    As to table structure, I'm not sure what all info you need. I have the
    following fields:
    PtID (autonumber, primary key)
    LastName
    FirstName
    MedicalRecordNumber
    MDID (foreign key from MD table, works fine)
    DateOfBirth
    DateOfDeath
    CauseOfDeaht
    ApptDate
    ApptTime
    Room#OfInpatients
    AppointmentNeeded? (yes/no - for pts who don't have a current appt & who
    aren't inpatients but who NEED an appointment set)
    Comments (memo to self - things to do for this patient)

    Those are the biggies.

    I have a seperate Protocol table:
    ProtocolID (autonumber, primary key)
    Protocol name
    A few other tedious details that aren't pertinent.

    The 3rd table may be my problem. It contains info specific to any one
    patient's activities related to any one protocol. Since a protocol may have
    many patients enrolled and any one patient can enroll in several protocols, I
    have the structure as follows:

    PtID (Number... foreign key from tblPt)
    Prot_ID (Number... foreigh key from tblPt)
    ScreeningDate
    Cohort
    Study#
    PtStatus
    Day1Treatment
    LastStudyTreatment

    As to relationships, I joined both the PtID & ProtocolID primary keys from
    the first 2 tables to the PtID & ProtocolID from the 3rd table, enforcing
    referential integrity and cascading updates.

    Please let me know what else you may need to know to help me unmuddle
    myself. I can't thank you enough!

    --
    Thanks for your time!


    "John Vinson" wrote:

    > On Sun, 24 Dec 2006 13:41:00 -0800, NC_Sue
    > <> wrote:
    >
    > >I'm using a database to track patients enrolled in research protocols at our
    > >medical center. Any one patient may enroll in several protocols. I have a
    > >patient table (with PtID as the primary key), a protocol table (with
    > >ProtocolID as a primary key), and a table that tracks a patient's activities
    > >relative to a specific protocol; this 3rd table includes PtID & ProtocolID as
    > >foreign keys. I have combo boxes in this 3rd table so I can recognize which
    > >patient & which protocol it refers to at a quick glance.

    >
    > ok... just be sure you're aware of the (rather stringent!) HIPAA
    > requirements concerning data privacy and data integrity. These can be
    > a pain in the neck (and elsewhere!) for developers but the penalties
    > for violation are *not* trivial.
    >
    > >Within my patient table, I include appointment data.

    >
    > Ummmmm.... Unless each patient gets one and only one appointment,
    > that's the wrong place. You really should have a separate table of
    > appointments, with a PatientID and other fields about the appointment.
    >
    > >I've built a query & a
    > >report to display the patient appointment list and a form to input data to my
    > >patient table. I can enter a new patient (including appointment time) into my
    > >patient form & it appears in my patient table, but the appointment doesn't
    > >show up when I run my query.

    >
    > Then there's something wrong with the Query or with the Form. Could
    > you describe your table structure?
    >
    > >I have to go back & manually enter the patient
    > >into my 3rd table which includes the 2 foreign keys. I can use the dropdown
    > >feature in this third table to find the new patient within the combo box, by
    > >the way.

    >
    > You can and should use a Subform. Table datasheets are NOT designed
    > for data entry or display and should not be used for this purpose. You
    > could use a Form based on the Patients table, with a Subform based on
    > the Appointments table; this subform would use the PatientID as the
    > master/child link field, and have a combo box for the protocol.
    >
    > >Any help you could give me would be greatly appreciated. Because the
    > >database is huge (870 patients & 155 protocols), I shudder to think about
    > >starting from scratch. If you need additional info, please tell me exactly
    > >what you need to know.

    >
    > 870,000 records in a table is getting pretty big. 8,700,000 is
    > getting huge. You're still *tiny* in database terms.
    >
    > John W. Vinson[MVP]
    >
     
    Guest, Dec 24, 2006
    #4
  5. Guest

    Guest Guest

    Hi Jeff - I think you & John Vinson are both addressing my issue in a very
    helpful fashion. I responded to John's reply to my post - can you please read
    over that reply & see if you have other suggestions as well?
    --
    Thanks for your time!


    "Jeff Boyce" wrote:

    > I may not understand your situation quite well enough yet, so take these
    > notions with a grain or two...
    >
    > Patients
    > PtID
    > ...(more patient-only data - e.g., FName, LName, DOB)
    >
    > Protocol
    > ProtocolID
    > Title
    > Description
    > ...(more protocol-only data -- ? date begun, ...)
    >
    > Enrollment
    > EnrollmentID
    > PtID
    > ProtocolID
    > DateEnrolled (date this patient enrolled in this protocol)
    >
    > EnrollmentDetail
    > EnrollmentDetailID
    > EnrollmentID (which "Enrollment" does this detail refer to)
    > AppointmentDate (for which appointment date for this enrollment)
    > ...(whatever details you need to keep that are specific to the
    > appointment)
    >
    > I don't quite get why you would keep (?)multiple appointment information
    > stuffed into the Patient table -- wouldn't you need to just keep adding
    > columns for each appointment? That would be a very spreadsheetly thing to
    > do, and would require constant re-modeling of your tables, forms, queries,
    > reports, etc.
    >
    >
    > --
    > Regards
    >
    > Jeff Boyce
    > Microsoft Office/Access MVP
    > http://mvp.support.microsoft.com/
    >
    > Microsoft IT Academy Program Mentor
    > http://microsoftitacademy.com/
    >
    > Microsoft Registered Partner
    > https://partner.microsoft.com/
    >
    > "NC_Sue" <> wrote in message
    > news:...
    > > I'm using a database to track patients enrolled in research protocols at

    > our
    > > medical center. Any one patient may enroll in several protocols. I have a
    > > patient table (with PtID as the primary key), a protocol table (with
    > > ProtocolID as a primary key), and a table that tracks a patient's

    > activities
    > > relative to a specific protocol; this 3rd table includes PtID & ProtocolID

    > as
    > > foreign keys. I have combo boxes in this 3rd table so I can recognize

    > which
    > > patient & which protocol it refers to at a quick glance.
    > >
    > > Within my patient table, I include appointment data. I've built a query &

    > a
    > > report to display the patient appointment list and a form to input data to

    > my
    > > patient table. I can enter a new patient (including appointment time) into

    > my
    > > patient form & it appears in my patient table, but the appointment doesn't
    > > show up when I run my query. I have to go back & manually enter the

    > patient
    > > into my 3rd table which includes the 2 foreign keys. I can use the

    > dropdown
    > > feature in this third table to find the new patient within the combo box,

    > by
    > > the way.
    > >
    > > Any help you could give me would be greatly appreciated. Because the
    > > database is huge (870 patients & 155 protocols), I shudder to think about
    > > starting from scratch. If you need additional info, please tell me exactly
    > > what you need to know.
    > >
    > > MUCH thanks.
    > >
    > > --
    > > Thanks for your time!

    >
    >
     
    Guest, Dec 24, 2006
    #5
  6. Guest

    John Vinson Guest

    On Sun, 24 Dec 2006 14:33:00 -0800, NC_Sue
    <> wrote:

    >I'm up to speed on HIPAA & you're right - it can be a pain. Thanks for the
    >reminder.


    Thought you might - but if you didn't it would obviously be important
    to let you know!

    >Each patient DOES have only one appointment I care about (at least at one
    >time), so I included this info in the patient table. It is unique to the
    >patient and while (s)he will have another appointment once he sees the doc
    >this time around, I figured I could change after (s)he comes in.


    Well... the real-life reality is one patient:multiple appointments.
    You don't care about a history of who was seen, for what, when, by
    whom? It's EASY to add now; much harder later!

    >My query & report seem to work OK on patients already included within the
    >patient table, but it's the new guy that gets added in that doesn't show up.
    >So I suspect it's the form that's the problem.


    I'm not sure when you're adding and when or where you're trying to
    display; I can hazard a guess that you might just need to Requery one
    form after adding a patient (using another form...?)

    >As to table structure, I'm not sure what all info you need. I have the
    >following fields:
    >PtID (autonumber, primary key)
    >LastName
    >FirstName
    >MedicalRecordNumber
    >MDID (foreign key from MD table, works fine)
    >DateOfBirth
    >DateOfDeath
    >CauseOfDeaht
    >ApptDate
    >ApptTime
    >Room#OfInpatients
    >AppointmentNeeded? (yes/no - for pts who don't have a current appt & who
    >aren't inpatients but who NEED an appointment set)
    >Comments (memo to self - things to do for this patient)
    >
    >Those are the biggies.
    >
    >I have a seperate Protocol table:
    >ProtocolID (autonumber, primary key)
    >Protocol name
    >A few other tedious details that aren't pertinent.


    looks fine, other than the appointment stuff perhaps (but you know
    your business better than I do of course!)

    >The 3rd table may be my problem. It contains info specific to any one
    >patient's activities related to any one protocol. Since a protocol may have
    >many patients enrolled and any one patient can enroll in several protocols, I
    >have the structure as follows:
    >
    >PtID (Number... foreign key from tblPt)
    >Prot_ID (Number... foreigh key from tblPt)
    >ScreeningDate
    >Cohort
    >Study#
    >PtStatus
    >Day1Treatment
    >LastStudyTreatment


    Again... only ONE screening, ever, per patient?
    What's the Study# - is it truly an attribute of a patient's enrollment
    into a protocol, or of the protocol itself, or what?

    >As to relationships, I joined both the PtID & ProtocolID primary keys from
    >the first 2 tables to the PtID & ProtocolID from the 3rd table, enforcing
    >referential integrity and cascading updates.


    Sounds good.

    >Please let me know what else you may need to know to help me unmuddle
    >myself. I can't thank you enough!


    You asked above:

    Within my patient table, I include appointment data. I've built a
    query & a report to display the patient appointment list and a form to
    input data to my patient table. I can enter a new patient (including
    appointment time) into my patient form & it appears in my patient
    table, but the appointment doesn't show up when I run my query.

    How are you "running" the query? Why run it at all, for that matter?
    QUery datasheets are of *very* limited utility other than for
    debugging. Do you have a Report based on the query? Just launch the
    report after adding the patient or appointment data. Do you just want
    to see the information? Use a Form (either as a Subform of the patient
    form, or requery it in the AfterUpdate event of the patient form).

    John W. Vinson[MVP]
     
    John Vinson, Dec 25, 2006
    #6
  7. Guest

    Guest Guest

    Actually, I don’t need to track the history of patient appointments in my
    database – there is an electronic record in the medical center that I can
    call up easily if I need to. As far as the patient’s appointment schedule
    goes, I’m only using the database to prepare for future appointments. I agree
    it would be easiest to add now, but I simply can’t think of a reason to track
    this for my purposes.

    My problem isn’t adding appointments for existing patients – it happens when
    I add a new patient. I’ve had to manually enter him into both tables (the
    patient table and the third table that includes the activity specific to a
    single patient for a single protocol) in order for his data to show up in my
    queries.

    I don’t understand what you mean by “requery one form after adding a patient
    (using another form…?)†Can you explain further – this may be my problem, but
    I haven’t queried forms before.

    Is there only ONE screening, ever, per patient? Well, basically, as regards
    a single protocol, that’s pretty much true. A patient may be screened for
    multiple protocols, but in my original database I added the same patient to
    the patient table each time he was screened for or enrolled to a protocol.
    This meant I had a good deal of duplicated data (demographic info, for
    example) in multiple rows for the same patient. I’m trying to normalize the
    tables now before any more cows get out of the barn. Oh – and the study
    number is unique to a protocol. Each sponsor uses a different numbering
    scheme for a patient, so a patient enrolled in “x†protocol may have number
    502-113 on that protocol and the same patient enrolled in “y†protocol may
    have number 39-10-17-4 on the second protocol.

    Yes, I have an appointment report based on the appointment query, but when I
    enter a brand new patient and enter his appointment data, this data doesn’t
    appear in the query OR the report.

    You also mentioned requerying in the AfterUpdate event of the patient form.
    NOW you’re talking a strange and wondrous language. What the heck is that?

    Thank you, John. If you weren’t an MVP already, I’d be your campaign chairman.

    Merry Christmas!

    --
    Thanks for your time!


    "John Vinson" wrote:

    > On Sun, 24 Dec 2006 14:33:00 -0800, NC_Sue
    > <> wrote:
    >
    > >I'm up to speed on HIPAA & you're right - it can be a pain. Thanks for the
    > >reminder.

    >
    > Thought you might - but if you didn't it would obviously be important
    > to let you know!
    >
    > >Each patient DOES have only one appointment I care about (at least at one
    > >time), so I included this info in the patient table. It is unique to the
    > >patient and while (s)he will have another appointment once he sees the doc
    > >this time around, I figured I could change after (s)he comes in.

    >
    > Well... the real-life reality is one patient:multiple appointments.
    > You don't care about a history of who was seen, for what, when, by
    > whom? It's EASY to add now; much harder later!
    >
    > >My query & report seem to work OK on patients already included within the
    > >patient table, but it's the new guy that gets added in that doesn't show up.
    > >So I suspect it's the form that's the problem.

    >
    > I'm not sure when you're adding and when or where you're trying to
    > display; I can hazard a guess that you might just need to Requery one
    > form after adding a patient (using another form...?)
    >
    > >As to table structure, I'm not sure what all info you need. I have the
    > >following fields:
    > >PtID (autonumber, primary key)
    > >LastName
    > >FirstName
    > >MedicalRecordNumber
    > >MDID (foreign key from MD table, works fine)
    > >DateOfBirth
    > >DateOfDeath
    > >CauseOfDeaht
    > >ApptDate
    > >ApptTime
    > >Room#OfInpatients
    > >AppointmentNeeded? (yes/no - for pts who don't have a current appt & who
    > >aren't inpatients but who NEED an appointment set)
    > >Comments (memo to self - things to do for this patient)
    > >
    > >Those are the biggies.
    > >
    > >I have a seperate Protocol table:
    > >ProtocolID (autonumber, primary key)
    > >Protocol name
    > >A few other tedious details that aren't pertinent.

    >
    > looks fine, other than the appointment stuff perhaps (but you know
    > your business better than I do of course!)
    >
    > >The 3rd table may be my problem. It contains info specific to any one
    > >patient's activities related to any one protocol. Since a protocol may have
    > >many patients enrolled and any one patient can enroll in several protocols, I
    > >have the structure as follows:
    > >
    > >PtID (Number... foreign key from tblPt)
    > >Prot_ID (Number... foreigh key from tblPt)
    > >ScreeningDate
    > >Cohort
    > >Study#
    > >PtStatus
    > >Day1Treatment
    > >LastStudyTreatment

    >
    > Again... only ONE screening, ever, per patient?
    > What's the Study# - is it truly an attribute of a patient's enrollment
    > into a protocol, or of the protocol itself, or what?
    >
    > >As to relationships, I joined both the PtID & ProtocolID primary keys from
    > >the first 2 tables to the PtID & ProtocolID from the 3rd table, enforcing
    > >referential integrity and cascading updates.

    >
    > Sounds good.
    >
    > >Please let me know what else you may need to know to help me unmuddle
    > >myself. I can't thank you enough!

    >
    > You asked above:
    >
    > Within my patient table, I include appointment data. I've built a
    > query & a report to display the patient appointment list and a form to
    > input data to my patient table. I can enter a new patient (including
    > appointment time) into my patient form & it appears in my patient
    > table, but the appointment doesn't show up when I run my query.
    >
    > How are you "running" the query? Why run it at all, for that matter?
    > QUery datasheets are of *very* limited utility other than for
    > debugging. Do you have a Report based on the query? Just launch the
    > report after adding the patient or appointment data. Do you just want
    > to see the information? Use a Form (either as a Subform of the patient
    > form, or requery it in the AfterUpdate event of the patient form).
    >
    > John W. Vinson[MVP]
    >
     
    Guest, Dec 25, 2006
    #7
  8. Guest

    Jeff Boyce Guest

    I'll jump back in briefly, as it looks like John's leads are working for
    you...

    If each protocol X Patient needs to uniquely identify the patient (by some
    code/number), that is a fact about the protocol X Patient record, not the
    patient alone. This number belongs in the "resolver" table you use to show
    valid patient/protocol combinations.

    --
    Regards

    Jeff Boyce
    Microsoft Office/Access MVP
    http://mvp.support.microsoft.com/

    Microsoft IT Academy Program Mentor
    http://microsoftitacademy.com/

    Microsoft Registered Partner
    https://partner.microsoft.com/

    "NC_Sue" <> wrote in message
    news:...
    > Hi Jeff - I think you & John Vinson are both addressing my issue in a very
    > helpful fashion. I responded to John's reply to my post - can you please

    read
    > over that reply & see if you have other suggestions as well?
    > --
    > Thanks for your time!
    >
    >
    > "Jeff Boyce" wrote:
    >
    > > I may not understand your situation quite well enough yet, so take these
    > > notions with a grain or two...
    > >
    > > Patients
    > > PtID
    > > ...(more patient-only data - e.g., FName, LName, DOB)
    > >
    > > Protocol
    > > ProtocolID
    > > Title
    > > Description
    > > ...(more protocol-only data -- ? date begun, ...)
    > >
    > > Enrollment
    > > EnrollmentID
    > > PtID
    > > ProtocolID
    > > DateEnrolled (date this patient enrolled in this protocol)
    > >
    > > EnrollmentDetail
    > > EnrollmentDetailID
    > > EnrollmentID (which "Enrollment" does this detail refer to)
    > > AppointmentDate (for which appointment date for this enrollment)
    > > ...(whatever details you need to keep that are specific to the
    > > appointment)
    > >
    > > I don't quite get why you would keep (?)multiple appointment information
    > > stuffed into the Patient table -- wouldn't you need to just keep adding
    > > columns for each appointment? That would be a very spreadsheetly thing

    to
    > > do, and would require constant re-modeling of your tables, forms,

    queries,
    > > reports, etc.
    > >
    > >
    > > --
    > > Regards
    > >
    > > Jeff Boyce
    > > Microsoft Office/Access MVP
    > > http://mvp.support.microsoft.com/
    > >
    > > Microsoft IT Academy Program Mentor
    > > http://microsoftitacademy.com/
    > >
    > > Microsoft Registered Partner
    > > https://partner.microsoft.com/
    > >
    > > "NC_Sue" <> wrote in message
    > > news:...
    > > > I'm using a database to track patients enrolled in research protocols

    at
    > > our
    > > > medical center. Any one patient may enroll in several protocols. I

    have a
    > > > patient table (with PtID as the primary key), a protocol table (with
    > > > ProtocolID as a primary key), and a table that tracks a patient's

    > > activities
    > > > relative to a specific protocol; this 3rd table includes PtID &

    ProtocolID
    > > as
    > > > foreign keys. I have combo boxes in this 3rd table so I can recognize

    > > which
    > > > patient & which protocol it refers to at a quick glance.
    > > >
    > > > Within my patient table, I include appointment data. I've built a

    query &
    > > a
    > > > report to display the patient appointment list and a form to input

    data to
    > > my
    > > > patient table. I can enter a new patient (including appointment time)

    into
    > > my
    > > > patient form & it appears in my patient table, but the appointment

    doesn't
    > > > show up when I run my query. I have to go back & manually enter the

    > > patient
    > > > into my 3rd table which includes the 2 foreign keys. I can use the

    > > dropdown
    > > > feature in this third table to find the new patient within the combo

    box,
    > > by
    > > > the way.
    > > >
    > > > Any help you could give me would be greatly appreciated. Because the
    > > > database is huge (870 patients & 155 protocols), I shudder to think

    about
    > > > starting from scratch. If you need additional info, please tell me

    exactly
    > > > what you need to know.
    > > >
    > > > MUCH thanks.
    > > >
    > > > --
    > > > Thanks for your time!

    > >
    > >
     
    Jeff Boyce, Dec 25, 2006
    #8
  9. Guest

    Guest Guest

    Keep jumping in, please - this puppy ain't fixed yet.

    I suppose by "resolver table" you are referring to the table I've got which
    has PtID & Protocol_ID as foreign keys from tblPatient & tbl Protoocols. This
    is the only location where I include info specific to a given patient's
    activity relative to a given protocol. In the protocol table I include info
    that relates only to the protocol - not to the patient - & I don't have a
    foreign key "PtID" in the protocol table. In the patient table I include info
    relating only to the patient - not to the protocol - and I don't have a
    foreign key "Protocol_ID" in the patient table. I do have upcoming patient
    appointment data in tblPatient, but i haven't figured out how to enter data
    relative to a new patient and have it populate in existing queries. At this
    point I'm entering data into tblPt, going to tblPatientProtocolActivity (the
    "resolver table" you referred to"), and seeing no new patient there... so I
    then head to a new row, use the combo box derived from PtID (the foreign key
    from tblPt) to find the new patient, and enter it that way. THEN - and only
    then - does the new patient appear in the queries I've built.

    How awkward.

    Again, thanks so much for your help... both of you!
    --
    Thanks for your time!


    "Jeff Boyce" wrote:

    > I'll jump back in briefly, as it looks like John's leads are working for
    > you...
    >
    > If each protocol X Patient needs to uniquely identify the patient (by some
    > code/number), that is a fact about the protocol X Patient record, not the
    > patient alone. This number belongs in the "resolver" table you use to show
    > valid patient/protocol combinations.
    >
    > --
    > Regards
    >
    > Jeff Boyce
    > Microsoft Office/Access MVP
    > http://mvp.support.microsoft.com/
    >
    > Microsoft IT Academy Program Mentor
    > http://microsoftitacademy.com/
    >
    > Microsoft Registered Partner
    > https://partner.microsoft.com/
    >
    > "NC_Sue" <> wrote in message
    > news:...
    > > Hi Jeff - I think you & John Vinson are both addressing my issue in a very
    > > helpful fashion. I responded to John's reply to my post - can you please

    > read
    > > over that reply & see if you have other suggestions as well?
    > > --
    > > Thanks for your time!
    > >
    > >
    > > "Jeff Boyce" wrote:
    > >
    > > > I may not understand your situation quite well enough yet, so take these
    > > > notions with a grain or two...
    > > >
    > > > Patients
    > > > PtID
    > > > ...(more patient-only data - e.g., FName, LName, DOB)
    > > >
    > > > Protocol
    > > > ProtocolID
    > > > Title
    > > > Description
    > > > ...(more protocol-only data -- ? date begun, ...)
    > > >
    > > > Enrollment
    > > > EnrollmentID
    > > > PtID
    > > > ProtocolID
    > > > DateEnrolled (date this patient enrolled in this protocol)
    > > >
    > > > EnrollmentDetail
    > > > EnrollmentDetailID
    > > > EnrollmentID (which "Enrollment" does this detail refer to)
    > > > AppointmentDate (for which appointment date for this enrollment)
    > > > ...(whatever details you need to keep that are specific to the
    > > > appointment)
    > > >
    > > > I don't quite get why you would keep (?)multiple appointment information
    > > > stuffed into the Patient table -- wouldn't you need to just keep adding
    > > > columns for each appointment? That would be a very spreadsheetly thing

    > to
    > > > do, and would require constant re-modeling of your tables, forms,

    > queries,
    > > > reports, etc.
    > > >
    > > >
    > > > --
    > > > Regards
    > > >
    > > > Jeff Boyce
    > > > Microsoft Office/Access MVP
    > > > http://mvp.support.microsoft.com/
    > > >
    > > > Microsoft IT Academy Program Mentor
    > > > http://microsoftitacademy.com/
    > > >
    > > > Microsoft Registered Partner
    > > > https://partner.microsoft.com/
    > > >
    > > > "NC_Sue" <> wrote in message
    > > > news:...
    > > > > I'm using a database to track patients enrolled in research protocols

    > at
    > > > our
    > > > > medical center. Any one patient may enroll in several protocols. I

    > have a
    > > > > patient table (with PtID as the primary key), a protocol table (with
    > > > > ProtocolID as a primary key), and a table that tracks a patient's
    > > > activities
    > > > > relative to a specific protocol; this 3rd table includes PtID &

    > ProtocolID
    > > > as
    > > > > foreign keys. I have combo boxes in this 3rd table so I can recognize
    > > > which
    > > > > patient & which protocol it refers to at a quick glance.
    > > > >
    > > > > Within my patient table, I include appointment data. I've built a

    > query &
    > > > a
    > > > > report to display the patient appointment list and a form to input

    > data to
    > > > my
    > > > > patient table. I can enter a new patient (including appointment time)

    > into
    > > > my
    > > > > patient form & it appears in my patient table, but the appointment

    > doesn't
    > > > > show up when I run my query. I have to go back & manually enter the
    > > > patient
    > > > > into my 3rd table which includes the 2 foreign keys. I can use the
    > > > dropdown
    > > > > feature in this third table to find the new patient within the combo

    > box,
    > > > by
    > > > > the way.
    > > > >
    > > > > Any help you could give me would be greatly appreciated. Because the
    > > > > database is huge (870 patients & 155 protocols), I shudder to think

    > about
    > > > > starting from scratch. If you need additional info, please tell me

    > exactly
    > > > > what you need to know.
    > > > >
    > > > > MUCH thanks.
    > > > >
    > > > > --
    > > > > Thanks for your time!
    > > >
    > > >

    >
    >
     
    Guest, Dec 25, 2006
    #9
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Easy Question, Can't see Answer

    Guest, Jun 10, 2005, in forum: Microsoft Access Database Table Design
    Replies:
    4
    Views:
    218
    Guest
    Jun 13, 2005
  2. stripedfrog via AccessMonster.com

    designing complex database

    stripedfrog via AccessMonster.com, Jan 23, 2006, in forum: Microsoft Access Database Table Design
    Replies:
    2
    Views:
    199
    stripedfrog via AccessMonster.com
    Jan 24, 2006
  3. Guest

    Quick yes/no answer - an easy one for all you gurus out there

    Guest, Mar 2, 2006, in forum: Microsoft Access Database Table Design
    Replies:
    1
    Views:
    145
    John Vinson
    Mar 2, 2006
  4. Guest

    Need help setting up a complex project database

    Guest, Sep 14, 2007, in forum: Microsoft Access Database Table Design
    Replies:
    21
    Views:
    442
    Gina Whipp
    Nov 1, 2007
  5. MStadnik

    Complex Database - How do I accomplish the Goal?

    MStadnik, Apr 16, 2008, in forum: Microsoft Access Database Table Design
    Replies:
    8
    Views:
    160
    John W. Vinson/MVP
    Apr 17, 2008
Loading...

Share This Page