Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    LoriH is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2020
    Posts
    17

    Relationships between tables in MS Access

    Hi there.



    I have created a new table to be used for a specific purpose (let's call this Table A). What I would like to do is link this table to our firm contacts table so that, if the information exists in the contacts database, it will populate Table A. The fields I want to use in the contacts table are name (first & last), company name and address.

    However, if in Table A I need to enter a name that does not exist in the contacts database (which will be about half the time), then I would like to be able to add this name anyway and have it be added to the contacts table as a new record.

    Is this possible? If so, how?

    Thank you!

    Lori

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    You would not put the data in TableA, only the PK from the contacts table.?

  3. #3
    LoriH is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2020
    Posts
    17
    If the new name doesn't exist in the contacts database, then I want to add it in Table A and when I do, have Access add the new information to the contacts table with a new record.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    That to me would be the wrong way round?

    I would be adding the data to the contacts table and after it had been added, update TableA with the PK of the new record.?

  5. #5
    LoriH is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2020
    Posts
    17
    So there is no way to do what I want then? I don't want users to have to go into two different tables, first to add the persons name, then over to the second one to add a ton of other information about that person (which doesn't belong in the contacts table).

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by LoriH View Post
    So there is no way to do what I want then? I don't want users to have to go into two different tables, first to add the persons name, then over to the second one to add a ton of other information about that person (which doesn't belong in the contacts table).
    Of course there is, just reverse the flow of the logic.
    Add record to TableA then create a new record in Contacts, then go back to TableA to add the PK from Contacts.?

    What happens when Ms Jones gets married and becomes Mrs Jones-Smith.?

    One table should be considered the master in my thoughts.? That would be the contacts table. If a company employee changed her name in the DB, nothing needs to happen to TableA.

    You can still add a record in Contacts and then it's PK into TableA (with a ton of other information about that person (which doesn't belong in the contacts table) and the user would be none the wiser.?

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Lori
    Gasman is describing the correct way of adding records. Data should only ever be stored once ... not copied to another table.
    Once a record has been entered in tblContacts, it can then used in other tables & in queries by just referencing its primary key field
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also, you keep saying "Table". Are you allowing users to directly access tables to add/edit info, or do you use forms?
    Is the Name field in either/both tables one field? You should have two fields: something like "FName" and "LName" or "FirstName" and "LastName" fields.

  9. #9
    LoriH is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2020
    Posts
    17
    I do not allow users to access the tables, no, I have created forms. The problem is this:

    The new table, Table A, contains a ton of information that staff need to enter for each company or individual. The forms are time consuming to complete and so, I thought, to save them a bit of time, many of those company and individual names already exist in the contacts database, so they could simply enter in the last name (or company name) and it would get the info from the contacts database and populate the field. And that works great if the client's name is already in the contacts database.

    However, some names are new and don't exist in the contacts database.

    If they have to go to the contacts database (or the form for it) and enter in the contact name and address, THEN go to Table A and enter in all the information associated with it, it won't be a time saver at all.

    Best case, in my mind, is that when entering the name/address in Table A, a new record is created automatically in the contacts database adding that information.

    Next best scenario is that they add the information in Table A, and if it's not in the contact database, Table A will allow them to add it anyway, at least in that table. But, after reading the posts to my query, I don't think that either of these scenarios will work.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Given that I don't know your business, allow me to ask some questions.

    Q) How much different (structure wise) is "TableA" from "tblContacts"? (You really shouldn't be storing duplicate data)


    You can be adding data to "FormA" and have "frmContacts" pop up if a person/company is not in the Contacts table. Add the new "Contact" (person/company) and additional data, the new contact is saved, the pop up closes and the new contact is then available to be selected in "FormA". All you really need to save from the contacts table into "TableA" is the Contacts PK field value.

  11. #11
    LoriH is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2020
    Posts
    17
    Yes! That would totally work! Thanks Steve.

    Lori

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to help.
    Post back if you get stuck....

  13. #13
    LoriH is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2020
    Posts
    17
    Hi Steve. I'm stuck. I created a "new contact" form, but don't know how to get it to pop up if the user has to enter a name/company that isn't in the contacts database.

    Thank you.

    Lori

  14. #14
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Would you post your dB so I can see where you are stuck?

  15. #15
    LoriH is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2020
    Posts
    17
    Hi Steve. I can't, it's a client list, so its confidential. But I hope the attached will explain what I'm hoping for...Click image for larger version. 

Name:	TABLE.jpg 
Views:	25 
Size:	82.5 KB 
ID:	43062

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Tables and Relationships
    By egnsantiago in forum Database Design
    Replies: 11
    Last Post: 02-14-2019, 10:04 PM
  2. RE: Relationships between tables
    By gwboolean in forum Access
    Replies: 2
    Last Post: 08-29-2015, 12:25 PM
  3. Replies: 1
    Last Post: 12-15-2014, 07:20 PM
  4. Help on Tables/relationships?
    By mistaken_myst in forum Database Design
    Replies: 3
    Last Post: 04-01-2009, 05:16 PM
  5. Relationships btw tables
    By metaDM in forum Queries
    Replies: 0
    Last Post: 03-05-2009, 12:15 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