Results 1 to 15 of 15
  1. #1
    mjf8563 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    8

    One to many Relationship

    I have a homework assignment though all together relationship confuses me. I am given two tables and have to make a one to many relationship between the two tables though they don't have anything in common.



    One is an Account table with 4 fields Account_Number, Account_Open_Data, Account_Type, and Account_Balance. The other is Customer table with 10 fields Customer ID, Name, FName, LName, Street Address, State Address, City, Zip code, Email, and Phone number. Do they have to have a field in common to have a one to many relationship or can they use the fields they have to make a relationship?

    It's a homework assignment though I am looking at it more as a I need to know this because I enjoy databases and would like to work with them more. I could care less about the grade the knowledge is a lot better than any grade I can get.

    Thank you in advance

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    difficult to advise without any context. Do your tables have any data? perhaps account number in the accounts table is the same as the customerID in the customer table - just been given different names. If so, then one customer can have many accounts

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd wonder if account number and customer ID were related.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    mjf8563 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    8
    There is no data in the fields just two tables with field names. The first question is.. Create a one-to-many relationship between the Customers and Accounts tables. Ensure the data is protected by enforcing Referential Integrity.
    I have taken a database class before and the relationship part of the class is what stumped me because of when I look at another database made they have the same field name that is the relationship. Thanks again for the quick replies.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I think often the field names would be the same, but not necessarily. To me the only fields that make sense are those two.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    mjf8563 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    8
    Thank you for the fast answers. I will try those two fields tomorrow.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem, good luck!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    check the data types, that might provide a clue. If accountnum is an autonumber then it is a primary key to the accounts table. The alternative is to add another field to the accounts table - CustomerFK long. then ensure your CustomerID in the other table is a primary key, link the two and enforce referential integrity

  9. #9
    mjf8563 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    8
    Customer_ID and Account_Number are the primary keys for the two tables. Customer_ID is in the Customer table and Account_Number is in the Account table. When I set them in a relationship and Enforce Referential Integrity it just does One-to-One relationship. How would I make them a One-to-Many relationship?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It's either a trick question or you're expected to add a customer ID field to the accounts table as Ajax suggested. If a customer can have multiple accounts, I would think that would be required. Primary keys can't duplicate, so there would be no way those fields could have a one-to-many relationship.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    mjf8563 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    8
    Thanks Ajax for saying that and you pbaldy for the quick responses. Also I was trying that though if I highlighted both the Account_ID and the Customer_ID in the Design View could I mark Primary key for both? It marked it but I wasn't sure if it really made them both Primary keys.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Is there more info in the assignment? Can a Customer have many accounts (or account types) eg: Savings , Checking...?? Quite often it is the description(context) of the "business under review" that can lead to rules and relationships.
    Account_Type in your table seems to suggest multiple types to me.

  13. #13
    mjf8563 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    8
    It's a weird assignment I think I figured it out. I had to make the Customer_ID into a FK and then add it to the Account table. Then linked the Customer_ID to the Customer_IDFK in the Account table. The tables has no information in it so there was no way to test the relationship.

  14. #14
    tmd63 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Oct 2016
    Location
    UK
    Posts
    16
    As with any database. You need to think in terms of the real world. Many people have multiple bank accounts but few bank accounts have multiple people (unless you think of joint family accounts but these are current accounts and not customer accounts for a company).
    So the current tables are unsuitable for making a one-to-many relationship and as suggested above, you need to add the customer ID field as a field in the account table and link this field to customerID with a one-to-many link.
    The referential integrity is needed to stop child accounts from being formed when you delete the customer from the customer table.
    You can test the relationship by adding dummy data. Add a customer (you dont need all the fields completed, just enough to make a record) then add the account and see if the accounts are linked by deleting the customer and checking the account disappears also.

  15. #15
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    will be interesting to see what the 'accepted' answer is....

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

Similar Threads

  1. Replies: 3
    Last Post: 03-19-2015, 05:26 PM
  2. Replies: 1
    Last Post: 12-06-2014, 12:49 PM
  3. One to Many relationship
    By ramindya in forum Access
    Replies: 4
    Last Post: 02-10-2012, 01:59 PM
  4. One to one relationship
    By anemoskkk in forum Access
    Replies: 1
    Last Post: 04-13-2011, 12:05 PM
  5. one to many relationship?
    By cowboy in forum Access
    Replies: 3
    Last Post: 06-16-2010, 02:37 PM

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