Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Union on two patient tables (2003)

    I am doing a “temporary” Access database for my hospital while we wait for a web app.
    I met with them for the schema – so that they could easily migrate the data once they are ready. The database collects info on patients that have a cardiac procedure – done either by a EP doctor or CST doctor. They collect a little different info on each – but most of the info is the same.

    The problem I am having is that THEY want TWO patient tables. One for EP and one for CST.
    I have all the tables built, the relationships done, the data entry forms done and all is working well when I test it.

    But – most of the reports don’t care if there are two types of these patients – they should include all patients (EP and CTS) and are most concerned with the “Follow-up” of these patients.

    So, I have to do a Union of the two patient (and multiple detail and list) tables. I am using an AutoNumber for both patient tables [tblEPPatient[, and [tblTHPatient].
    The Report from the query looked great until I tested it and the data was incorrect – because the union has the same ID numbers for some of the patients.

    I saw that a prefix can be added to an Autonumber – but the Union Query will not run if I do that. I saw on a Woody’s post by Hans that suggested a calculated field in a query for someone – but it was not specific. I’m not sure what I could calculate. I have the regular fields in the table – LName, FName, Procedure Date, etc.

    I just need a unique ID field for each patient in the query. Or am I totally missing something simple?
    I like the idea of the prefix - because it also distinguishes the patients

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Union on two patient tables (2003)

    Any reasonable hospital database that I know of uses a single patient table. After all, a patient is a patient, whether she is in the maternity ward or in the intensive care unit or whatever. The patient table stores data that isn't repeated over time.
    Of course, there can (and usually will) be "child" tables of the patient table that store data that are collected repeatedly, for example treatments, medication, follow-up etc. The cardiac procedure might be a good candidate for such a "child" table, since I assume that a patient might undergo more than such procedure. If the data collected for EP and for CPS overlap, it would make sense to use one "child" table for this, with an identifier that distinguishes EPS records from CP records, and with some fields used only for EP and others used only for CPS.

    But it may not be possible to convince "THEM" of that.

    Anyway, you could add a field to the union query to distinguish the procedures. Here is some pseudo-SQL:

    SELECT LName, FName, ProcedureDate, "EP" As ProcedureType FROM tblEP
    UNION
    SELECT LName, FName, ProcedureDate, "CPS" As ProcedureType FROM tblCPS

    or if you prefer to use the AutoNumber fields with a prefix:

    SELECT "EP" & [ID] As UnionID, LName, FName, ProcedureDate FROM tblEP
    UNION
    SELECT "CPS" & [ID], LName, FName, ProcedureDate FROM tblCPS

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Union on two patient tables (2003)

    >>The problem I am having is that THEY want TWO patient tables. One for EP and one for CST.<<

    Who are THEY? And why would they even care about the database structure as long as it provides the reports they want? This is just a strange requirement.

    I think you should point out that creating separate tables will greatly increase development time (and expense) continually cause problems (for the reasons you stated), and will probably impact performance in the future as the database grows.

    This is just not a good idea, and they REALLY need to understand that. And make sure you CYA if you must abide by this requirement; write a letter stating that despite your objections, they have insisted on this database structure.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union on two patient tables (2003)

    Thank you both for your help!

    I agree with you both and started out with one patient table. The THEY that I am referring to is our IT department. They will be doing the web app.
    I do some Access databases and websites for Patient Care Services - not with the IT dept. - so I'm a little lower on the totem pole.
    I believe they are trying to have the database as normalized as possible? But that still does not make sence to me.

    After I received a basic schema drawing from them and saw what they wanted, I posted it in question here before our meeting - Liquorman said one table.
    After hearing from both of you, I will be sure to voice my objections (diplomatically) in writing.

    Thanks goodness it is temporary - I would not want to maintain it.

    Thanks Hans for the solution in the meantime. I will work on this.
    Vicky

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Union on two patient tables (2003)

    Your IT department may have its own reasons for wanting two patient tables, and they may be good reasons (although I doubt it, but after all I have no idea what their reasons are), but it violates normalization. Since ALL patients have a first name, last name, birthdate, gender etc., those data belong in a single table

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Union on two patient tables (2003)

    Is it possible that over time a patient might have cardiac procedures done both by a EP doctor and a CST doctor?

    IF so the design the IT dept wants will force the same patient to be entered twice in different places. That would be an unfortunate side effect.
    Regards
    John



  7. #7
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union on two patient tables (2003)

    Yes, it is possible that the patient return to have the procedure done by CTS or even another EP physician - but they do want to count the procedures not the patient. They specifically want that patient counted again as a whole new procedure.
    Thanks so much for all the help,
    Vicky

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Union on two patient tables (2003)

    If a patient can have either or both procedures, then you need 2 tables: a patient table (for patient name, etc.) and a procedures table (or possibly separate CTS and EP tables if the fields are so differerent).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •