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

    Relationship Design (2002)

    I am doing an Access Database for "Crash Carts" for our hospital ** Possible SPAM post - please alert a Moderator (2)** department.
    Every "Location" in the hospital has a "Crash Cart" that contains two "Trays" of emergency "Medications".
    When a Crash Cart is used, it is replaced by one of three that reside in the ** Possible SPAM post - please alert a Moderator (2)** department.
    There are two situations where the cart needs to be restocked:
    1. The cart (Medication) is "used" for an emergency -OR- 2. One or more medications in the cart has "expired".
    Currently, if a cart is "used" - it is brought to ** Possible SPAM post - please alert a Moderator (2)** to be restocked and a replacement is taken to the floor.
    Currently for an "expired" drug, the pharmacist physically walks around the hospital to check each of 44 drugs in each of 48 carts.
    They need reports to reflect what drugs (Trays) are going to expire in a timeframe, where that tray and cart are at any given time and what employees are servicing the Trays/Carts.
    There are 38 locations, 48 crash carts, 96 trays and 44 medications per tray, some of which are duplicates. There are 2 trays on each crash cart. And some locations have 2 crash carts.
    I need (I think) to be able to add/remove medications form a tray, add/remove trays from a cart and add/remove a cart from a location.

    A location can have more than one cart and any cart can go to any one location.
    A cart has two trays and the trays could end up in any cart as they are generic.
    A tray has many medications

    So far, I have:
    tblLocation
    LocationID
    LocationDescription

    tblCart
    CartID

    tblTray
    TrayID

    tblMedication
    MedicationID
    Description

    tblMedicatioTrayDetail
    MedicationTrayID
    MedicationID
    TrayID
    MedicationExpirationDate
    Remove y/n
    Add y/n

    tblCartTrayDetail
    TrayCartID
    CartID
    TrayID
    Remove y/n
    Add y/n

    tblEmployee
    EmployeeID
    LName
    FName

    My relationships so far are not letting me do what I need to

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

    Re: Relationship Design (2002)

    A cart can be in only one location at a time, but some locations have two carts, so there is a one-to-many relationship between locations and carts. This can be implemented by putting LocationID in the carts table. (You may want to store history information in a separate table)

    A tray can be in only one cart at a time, but a cart contains two trays, so there is a one-to-many relationship between carts and trays. So you don't need an intermediary table tblCartTrayDetail. One way to implement the relationship is to put CartID in the trays table, probably with an indication whether it's the upper or lower tray in that cart. Since the maximum number of trays in a cart is 2, another possibility is to have UpperTrayID and LowerTrayID fields in the carts table.

    One tray can contain different medications and one medication can be on many trays, so this is a many-to-many relationship, implemented in the tblMedicatio(n)TrayDetail table.

    The central form would be based on the carts table. Location can be selected from a combo box whose row source is (a query based on) the locations table.
    I would place a tab control on this form with two pages. The first tab page contains a (probably continuous) subform with medications for the upper tray, the second one a similar subform for the lower tray.

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

    Re: Relationship Design (2002)

    Thank you so much Hans, this makes much more sense now.
    You mentioned a history table, how would you go about taking a medication out of a tray (or a tray form a cart, or a cart from a location.) I do want history, so I do not want to delete and re-add it.
    I've never done a history table and am not sure how to get the data out of one table and into another....

    Right now, on the form, I have a Y/N field that says Remove and Add, then a command button that runs a query that shows the updated information - without the removed items. I tried to use a "Refresh" command button, but couldn't get it to work right, it would refresh the removed items, but not the added items. I'll keep working on it.

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

    Re: Relationship Design (2002)

    Does each individual medication item (bottle, box, strip, ...) have some kind of unique ID or not?

    What information do you want to keep for history purposes? E.g. do you want to know on which day medication was used, where it was used, in which tray it was before use, etc.

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

    Re: Relationship Design (2002)

    Yes, each medication does have a unique ID.
    What they would like to have history of is which employee filled tray 10 in cart 52 in January and did not include a critical drug.
    They would also like to know productivity. Which employees are taking the initiative to check on and re-stock the carts.

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

    Re: Relationship Design (2002)

    I would create a separate tblTransactions table, with fields:
    TransactionID (AutoNumber, Primary Key)
    MedicationID
    TrayID
    CartID
    LocationID
    EmployeeID
    ActionID
    Date + time
    plus other details, if needed.

    ActionID is linked to the primary key ActionID in a tblActions table that lists the possible actions, such as "add to tray", "removed for use" and "removed because expired", perhaps more.

    Users shouldn't edit, add and remove records directly, but use command buttons or custom nenu options/toolbar buttons. The code behind these perform the necessary action and add a new record to the tblTransactions table.

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

    Re: Relationship Design (2002)

    Once again, thank you.
    This is a new concept for me but I'm anxious to learn it.
    I will work on it.
    Vicky

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

    Re: Relationship Design (2002)

    I know this never happens, new info was added after I supposedly had it all. I have a working version but I know it is not a good design especially with the changes. I wondered if you had time, if you could take a look and offer suggestions.
    The added info is that there are different medications in Tray A and Tray B that are not interchangeable. Tray A medications are not in Tray B. And, the trays have specific slots that the medications are assigned to in the Tray.

    It seems that I should have a static table that defines what medication goes into Tray A and Tray B? And what slots the medications are assigned to in the tray?

    I tried a static table but when I try to do the relationships, I get an "intermediate" relationship and an error that says "no unique index found for the reference field of the primary table". One of my many versions is a working version but I know it is not a good design.
    Here are the specs once again with the new info added in.

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

    Re: Relationship Design (2002)

    Your tblCartLocationDetail table is problematic: it allows you to place one tray in different carts, and to place more than two trays in one cart. It also allows you to assign one cart to several locations, and to assign several carts to one location. Instead of using this table, I would put a CartID field in the tblLocation table, and two fields TrayA_ID and TrayB_ID in the tblCart table. I'm not sure whether you should have two separate tables for the "A" trays and the "B" trays.
    Whichever way you organize the tables, you'll have to do a lot of checking in VBA code, for I don't think that you can "map" your requirements exactly into a relational design.

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relationship Design (2002)

    Vicky

    I have found this site very useful in the past to get some ideas on data models, I note there is one on pharmacies that may be of use to you to get some ideas
    Jerry

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

    Re: Relationship Design (2002)

    Thank you Jerry,
    This has been a tough one for me. I will check the web site today.

    You and Hans have been very helpful.
    This web site has taught me more than any books.

  12. #12
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relationship Design (2002)

    Vicky

    No probs, stick with Woody's, I've learnt more here than at College, good practical fixes from practitioners
    Jerry

Posting Permissions

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