Results 1 to 5 of 5
  1. #1
    Girraffa is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    32

    Question Difficulty with organizing individuals versus groups

    I have been working for several months on creating a database from scratch for tracking a small animal care program. I am struggling with the relational structure as follows:
    We serve several different facilities, each of which have animals. All the animals are related to the facilities table through a foreign key and my forms subset the animals the user can choose to enter information on based on choice of facility in a combo box.
    An animal can be:
    -part of a group (sometimes treated as an individual and sometimes as a group, e.g. an individual dove in a pen breaks a leg vs. a all the doves may be exposed to an infection and get the same dose of an antibiotic)
    -not part of a group (never belongs to or is treated as part of a group)
    Sometimes an animal is initially treated only as an individual and we later find out it is part of a group.
    I am having trouble designing a structure that will accommodate these situations for relatively simple user-entry of data. My current solution (and there have been several that have proven to be ill-conceived) is to have a parent table of facilities and a child table of animals that can also belong to a group: the groups are a separate table related to the animal table. I am planning on writing some code that will allow a user to press a button to batch-apply things like exams or updates to multiple animals using that group table. I will also have entries in the animal table that are "cohorts" of animals that only ever get treated as a group (tank of fish).
    Is there a better way to do this? Am I thinking about this the wrong way?


    Thank you for any help!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    youd have a GroupID in tGroups,
    each animal would have a GoupID in the tAnimal record.

    tAnimal tbl
    AniID, Name, Species, Breed, GroupID, CohortID
    123, Bob, Dove, Morning, 55, 2

    tGroups tbl
    GroupID, GrpName, Descr
    55 , Dove Cage123, doves from New Zeland

    maybe even a CohortID for sub groups.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may get some ideas from links in this post.


    I recommend that you write a description of your "business" (that is, what you do) in plain English.
    And work through 1 or 2 of the tutorials mentioned in the link provided. You will learn a process and create a structure that you can test.

    Good luck.

  4. #4
    Girraffa is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    32
    Thank you ranman256! That is the format I ended up going with.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    girraffa,

    Can you provide a description (overview) of what you do (the animal care business) in plain English. No data base jargon re tables etc? Perhaps you could lead us through a typical day at the Animal Care facility, or even a few days or, if it makes more sense, some typical tasks like treatment of animals, or treatment of a group.
    Just some description of the things involved to help readers understand your environment.

    You might try watching some free video tutorials by Dr.Daniel Soper that will help you with the concepts.

    Intro to Database
    The Relational Model
    Data Modelling and the ER Model




    Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Organizations v. Individuals how to set up
    By Lena Olson in forum Database Design
    Replies: 1
    Last Post: 04-10-2015, 01:38 PM
  2. Re-organizing records in a Table
    By swb1 in forum Access
    Replies: 1
    Last Post: 07-28-2014, 05:03 PM
  3. Replies: 21
    Last Post: 07-14-2014, 12:00 PM
  4. Replies: 2
    Last Post: 12-17-2012, 03:08 PM
  5. Need help re-organizing data
    By saccolicious in forum Access
    Replies: 2
    Last Post: 10-13-2012, 07:15 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums