Results 1 to 6 of 6
  1. #1
    booismycat is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    12

    Table Design

    I have an Access 2016 table that contains the following:

    PriID - key field
    PriFirstName
    PriLastName
    SecFirstName
    SecLastName

    This contains Name & spouse or partner

    I have another table:

    AddressID - key field
    Address
    City
    State
    ZipCode

    The two have a one-to-many relationship.
    No problems here.

    I need to create another table that will allow me to:
    Have a one-to-many relationship with the Names table (above).


    This new table needs to properly identify Associations to either the primary name or the spouse/partner name.
    Both names could have associations assigned but, they would not necessarily be the same.
    Associations could be something like Volunteer, Officer, Board Member.
    Each name COULD have one or more or NO associations.

    How can I design the Names and Associations tables to allow the proper relationship for this?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I think it really should be...
    tPersons table:
    PersID (autonum)
    FirstN
    LastN
    etc...

    tCouples table:
    CoupleID (autonum)
    PartID1 (from tPersons.PersID)
    PartID2 (from tPersons.PersID)
    Addr
    City
    st
    Married (y/n)
    Divorced (y/n)
    etc...

    tAssoc table:
    AssocID (autonum)
    PersID
    AssociateID (from tPerson.PersonID
    How
    When
    which
    etc...



  3. #3
    booismycat is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    12
    Thanks for responding but, how do you link PartID1 and PartID2 to tPersons table? Access doesn't allow this type of relastionship.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Just spitballing here.

    Maybe try
    Click image for larger version. 

Name:	booismycat1.png 
Views:	17 
Size:	70.1 KB 
ID:	38139

    Any field name ending in "ID_PK" is an Autonumber PK field
    Any field name ending in "ID_FK" is a Long Integer FK field
    Table name prefix of "jnct" is a junction table.

    There is only 1 "tblPeople" table, but added twice in the relationship window.

    "tblAssociations" contains entries like Volunteer, Officer, Board Member, etc.

    Add/change/delete fields as necessary.....

  5. #5
    booismycat is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    12
    I see what I think is a couple of issues in your relationship design.
    In your tblPeople, how do you identify that Mickey Mouse and Minnie Mouse live at the same address?
    I resolve this by having two people tables - primary and secondary with a one-to-one relationship.

    I tried pasting a view of my relationship design here but I'm doing something wrong as it doesn't work. I use Snip-it, save to my clipboard and try to paste here - doesn't work.

    I essentially have "close" to what you laid out but, find I cannot set my join to enforce referential integrity on the junction table. Its' something I would prefer to do but I can deal without it.

    Appreciate your response.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Like I said, I was just spitballing - don't know all of the data you need and I didn't try putting any data into the tables. But it is where I would start......

    Quote Originally Posted by booismycat View Post
    In your tblPeople, how do you identify that Mickey Mouse and Minnie Mouse live at the same address?
    You want each person to be able to have 0, 1 or more addresses. You could have the address table linked to the junction table "jnctPerson2Person" instead. Then the couple could have 1 or more addresses.
    Your choice.....

    Quote Originally Posted by booismycat View Post
    I resolve this by having two people tables - primary and secondary with a one-to-one relationship.
    If you have two tables in a one-to-one relationship, why would you have 2 tables?


    In table "jnctPerson2Person", you could add a field to determine which person is the Primary.
    With the junction table "jnctPerson2Person", you are not limited to just 2 people in a parternership. Maybe, in the future, you have a group of people, with a primary person and many addresses.


    Good luck with your project.......

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

Similar Threads

  1. Replies: 1
    Last Post: 09-19-2016, 12:03 PM
  2. Replies: 3
    Last Post: 04-27-2014, 08:08 PM
  3. Table Design -- want to avoid a design that creates blank fields
    By snowboarder234 in forum Database Design
    Replies: 4
    Last Post: 10-08-2012, 10:13 AM
  4. Replies: 8
    Last Post: 03-24-2012, 11:03 AM
  5. table design: one big table vs. multiple small tables
    By lstairs in forum Database Design
    Replies: 3
    Last Post: 12-31-2009, 08:46 AM

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