none
Want to create a "Form" to populate multiple tables.

    Question

  • Hi!

    I have a Database with a couple of tables; in particular, I have 3 tables(Client, HRData & ClientEmergencyContact) with are interconnect by means of "Relationships - primarily, one-to-many relationships ", and they are joined by ClientID as a "Primary Key" in one table/tables and a "Foreign Key" in the other.

    I am trying to create one (a single form) "Form" through which I can populate all three of the tables without have to fill out separate form for each table since they are all inter-related. I made the ClientID field the "Primary Key" for the Client Table and also made the "ClientID" field the "Foreign Key" in the "HRData" Table, for example. Since I have the ClientID field Index set as "No Duplicate", and based on that ClientID field, I want to populate the rest of the tables or fields. However, I do not want to create one long and never ending form to accomplish the task.

    Here are things that I tried: I created a form and added a "Tab Control" to the form and on each tab, I created fields for the different tables one each tab. For instance, I created 3 tabs and the first tab had to do with the client table, the second - HRData, the third - ClientEmergencyContact. I guess I am doing something wrong but I am not able to populate the three tables from this form, with reference to the ClientID. I intend on using the ClientID to reference (don't know if that the right terminology) the different records.

    I also created a form similar to the sample "My Wizard" form at the following link: http://blogs.office.com/b/microsoft-access/archive/2010/02/23/access-2010-browseto-docmd-and-macro-action.aspx with the intention of having each step referencing a particular table or record. Where the "Next" should take you to the next set of record. Still can't get that to work too.

    Help please.

    Note. I am a newbie to Access.

     

    Yours,

    Jale01

     

    Friday, March 11, 2011 3:22 PM

All replies

  • Use form/subform with Master/Child on the primary/foreign keys.  Access will automatically add the foreign key as you add records in the subform (many side of relationship).
    Friday, March 11, 2011 4:25 PM
  • Hi Karl,

    Are you saying that I should create a main form and set the primary key to "Master" and create subforms and set each foreign key to Child?

    If so, let's take the case of a "Tab Control", how will I go about doing that? Will I use the tab control as the subform?

    Friday, March 11, 2011 4:51 PM
  • No Tab Control needed.

    Use the primary key field of the table that is the 'one side' of the one-to-many relationship.

    The foreign key is the field in the'many side' table that matches the 'one side' primary key.

     

    Friday, March 11, 2011 7:20 PM
  • Hi Jale,

    Have you resolved your problem yet? Do the suggestions work for you or not? If you still show any concern on your problem, just feel free to follow up.

    Best Regards,

     


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, March 18, 2011 9:42 AM
  • I guess I am doing something very wrong. I did follow Karl's instructions, which is very very helpful, and I managed to populate some of the record's fields. For some reasons, or due to my error, I am not populating all of the record's field in the table although I am filling up all of the necessary forms' fields.

    If possible, can I send my file to be looked at? Maybe someone can tell me my mistakes. It may be due to the way I have my relationships, or the primary keys or something. Just can't figure it out.

    Like I said, I tried to use a client's ID number as my reference field for the record(s), but it's taking me for a ride.

    Friday, March 18, 2011 5:50 PM
  • You wrote a bunch of words but said very little as to the problem.

    What fields are not populating?    Are they bound?

    Post the SQL of your queries that feed the form and subform.

    Friday, March 18, 2011 6:08 PM
  • Is it one particular table that is not being updated or is it some fields in one table not being updated while other are.

    if it is one of the tables that isn't getting an update at all, then check the master/child links for each of your subforms (particularly the form that isn't updating the table)

    if it is just some of the fields that are not being updated, then check the control sources of the controls that should be holding the data that is not being updated.

    Friday, March 18, 2011 6:09 PM
  • Ok. Since Karl has beaten me up. Lol. Like me do me best and go through what I have done or did not do:

    • I have for tables namely: tblClients, tblEmergencyContact, tblClientHRData & tblClientCaseWorker. All of these tables should be inter-related; I separated them because I have learned that it's a good practice to have multiple tables instead of one gigantic table. With that in mind, I did break up the table with the above names.
    • For instance, the tblClients table has the following fields: ClientID, FirstName, LastName, Address, Phone, Fax, & email, with the ClientID field set as the PrimaryKey. tblEmergencyContact has: ContactFirstName, ContactLastName, ContactAddress, ContactPhone, Fax, email & relationship (to client), with an autonumber ID field set as the PrimaryKey and a ClientID field set as a ForeignKey. tblClientHRData has: ClientSocial, ClientDoB, ClientBenefits, ClientDocs, ClientCondition, and ClientID as a ForeignKey while ClientSocial is the PrimaryKey. tblClientCaseWorker also has the following fields: an autonumber ID field set as the PrimaryKey and other contact fields such as FirstName, LastName, Address, PhoneNumber(s), and ClientID set as ForeignKey again.
    • For the different tables, I used a One-to-Many Relationship with the ClientID (which is used as the PrimaryKey) field from the tblClients being the one side the the relationship and the ClientID (ForeignKey) for the other tables, being on the many side of the relationship. I have each of the other table (tblEmergencyContact, tblClientHRData & tblClientCaseWorker) being pointed to tblClients individually.
    • My intention is to link all of the records in the different tables through the ClientID field.
    • In populating the different tables, I created four forms, which I am using as subforms. Since I intend on not have multiple forms for the said tables, I imitated the following "My Wizard" form: http://blogs.office.com/b/microsoft-access/archive/2010/02/23/access-2010-browseto-docmd-and-macro-action.aspx to step through the different forms. For the main form, I created a black form with only the name of the form in the Form Header and the subform, along with the "Next" & "Previous" buttons which are used as the step through buttons to call up the subforms, in the Detail area. I have no other control on the main form other than the subform and the step through buttons.
    • While filling up the form(s), I have the frm/sfrmClients being the initial form and all other forms follow. If I am to create a client ID number on this first form for the ClientID's field, how do I avoid repeating manually inputing this ID number in the rest of the forms, since they all have the ClientID field as a ForeignKey? I really don't know if I really need to have the ClientID field as ForeignKey in each of the other tables; as I said, my intention is to tie record one from table one to record one of the rest of the tables through the ClientID field.
    • Like Karl recommended, I did use the primary key field of the table that is the 'one side' of the one-to-many relationship.
    • One of the reasons why I say that all of the fields are not being populated is because I created a query and included all of the listed tables but I don't have all of the fields in the tables being populated.
    • Am I even doing the right thing or am I completely wasting my time trying to do something that doesn't make any sense?
    Saturday, March 19, 2011 3:15 PM
  • You  have names in too many places.  Have one table for people - clients and contacts are people. 

    I would use these fields for tblClients: autonumber ID field set as the PrimaryKey, ClientID, FirstName, LastName, Address, Phone, Fax, & email, ClientSocial, ClientDoB, ClientBenefits, ClientDocs, ClientCondition, relationship, and Contact & CaseWorker fields set as a ForeignKeys.  

    Use a self join relation by placing the tblClients in the relationalship window three times (Access will add a sufix or '_1' and '_2' {without the quotes}).  Clic on ID of first table and drag to Contact of the second table selecting Referential Integerity.  Do the same from first table to CaseWorker of the third table.  A person can be the Contact for multiple individuals and CaseWorker for many.

    Use form/subform with Master/Child on the primary/foreign keys.  Access will automatically add the foreign key as you add records in the subform (many side of relationship).  Have a form with two subforms (Contact and CaseWorker) OR two form with one subform each.  Use a combo in the subforms to select Contacts for the Client and to select the person's CaseWorker.

    Sunday, March 20, 2011 4:28 AM
  • Am I creating just one table for all of the fields (merging all of the tables into one table)?

    If I should create one table for people, like you said (clients & contacts are people), CaseWorker to is also a people table and you are saying that I should create a subform for "Contact" and "CaseWorker". If I create a create one table for people, how am I still having "Contact" as a subform?

    I don't fully understand.

     

    Sunday, March 20, 2011 7:44 PM
  • One table for people and attributes that all people have regardless of whether or not they are a client, contact, case worker etc (IE all people have a name, all people have a DOB, you don't need to have Name or DOB in a client table and in a caseworker table). You will probably have an autonumber primary key in that table. You could then have a "clients" table that has as its foreign key from the primary key of the people table. It would be a one-to-one relationship with the people table to identify all clients.  In that table you would also have all the ONE OF Attributes for Client IE if a client only has one condition then that would be in the clients table. If they only had one benefit (IE a $ value) then it would be in the clients table, otherwise you would have a one-to-many relation with all the benefits that a client could have.  Similarly with client contacts.

    You would do the same with a caseworkers table as a one-to-one to identify caseworkers and include in that table all the ONE OF Attributes for case workers and then have one or more one to many tables with the caseworker's table for the specific attributes of caseworkers. IE a caseworker will have multiple clients so you would have a caseworker-client table that has two mandatory fields one to identify the caseworker and one to identify the client.  It could have other fields if they are one of specific attributes of a caseworker/client - this would be a one-many relationship from caseworker to caseworker/client tables



    Sunday, March 20, 2011 8:25 PM
  • The one table is used for all three.

    ID ClientID FirstName relationship Contact CaseWorker
    0 Null None None 0 0
    1 X01 Bill Client 7 3
    2 C03 Sam Client 6 4
    3 D91 Joe CaseWorker 0 0
    4 SD3 Jim CaseWorker 0 0
    5 AW5 Tom Client 8 4
    6 ME0 Sally Mother 0 0
    7 FT7 Will Father 0 0
    8 MA3 Mary Mother 0 0

    Bill's contact is Will, who is his father.  His CaseWorker is Joe.

    Sam's contact is Slly, who is his mother.  His CaseWorker is Jim.

    Tom's contact is Mary, who is his mother.  His CaseWorker is Jim.

     

    Sunday, March 20, 2011 8:34 PM
  • I don't know what the OP has in mind but, it might be possible for there to be multiple roles/relationships - which is why I suggested it the way I did.

    Will, who is Bill's father/contact might also be a client.

    A caseworker, may have a child in need of service in which case the caseworker will be a contact in one role/relationship, but a caseworker in another.  That would be difficult to accomplish in the format you suggested.

    Sunday, March 20, 2011 10:39 PM
  • Guys, thanks a lot for all of your patience and help. Like I said earlier, I am a newbie to Access.

    I have gone over the suggestions by both Karl & suzyQ and I guess my first question is: What is a "ONE OF ATTRIBUTE?" I guess my understanding of it is where, for example, a person has a single/unique attribute (eg. John Doe has one DoB, one Social, etc.).

    The second question is, if most of my tables (Clients, CaseWorker & Contact) have to do with people and they are all pretty much the same info, and I am going to create a table called "People", why will I still a table for Contact, Client, or CaseWorker? My understanding is that I am to incorporate those fields that have to do with people into one table. If I am going to use these tables, what fields should I retain in them?

    What happens to clients that have multiple contacts or multiple caseworkers?

    Please have patience with me. I am trying to fully understand.

    Tuesday, March 22, 2011 2:57 PM
  • Yes a "one of" attribute is something that will not occur multiple times- one such attribute is hair color (natural hair color anyway) I would not have a list multiple hair colors for one person.  But if their can be multiple attributes (such as a left arm and a right arm (or multiple items on an order, etc)) than you would have that in a one-to-many table.

     

    For you second question - in the structure that I proposed, your client table will identify which people are clients (foreign key only - not repeat of all people attributes) and any of the "one of attributes" that are specific to a client.  The same for Case Workers.  Your contact table would be on the many side of a one-to-many relationship with the clients table to identify which people are a contact for which clients.

     

    If a client has multiple caseworkers then you need a one-to-many relation clients to caseworkers also.

    Tuesday, March 22, 2011 3:18 PM
  • Will the people table have, for instance, client_name, caseworker_name, contact_name, or just name for all of them? If just name, how will I distinquist who's who?
    Tuesday, March 22, 2011 5:21 PM
  • PEOPLE TABLE

    Lastname

    Firstname

    DOB

    Address

    City

    State

    Zip

    PeopleID_PK (primary key)

    (and any other field that is an attribute to all people that you want to track)

     

    CLIENT TABLE (this table just lists all the people who are clients and gives them a client id - any other attributes that are a "ONE OF" for client only would also be included in this table - This is on a one to one relationship with people

    PeopleID_FK (Foreign key- related from people table)

    ClientID_PK (primary key)

     

    CLIENT CONTACTS TABLE (this is a one to many relationship Client Table is one - contacts table is many

    ClientID_FK (Foreign key - related from client table)

    PeopleID_Contact_FK (foreign key - related from people table)

     

    CASEWORKER TABLE (this is a one-to-one relation with people - same as client table, but for caseworkers)

    PeopleID_FK (Foreign key- related from people table)

    CaseworkerID_PK (primary key)

     

    CASEWORKER CLIENT TABLE (this is a one-to-many with caseworkers table - one caseworker has many clients)

    ClientID_FK

    CaseworkerID_FK

     

    Tuesday, March 22, 2011 6:06 PM
  • The problem with having multiple "name" tables is your "people" can have multiple roles and if they did, their entire information would be duplicated in multiple locations which is what you need to avoid.

    A caseworker might also be a contact.

    A contact might also be a client.

    You want to avoid having to put their names in multiple locations, using a "people" table - call it what you want avoids duplication of data.  Then to identify which people are clients, your client table uses a foreign key that relates back to the people table.  The same with your other tables.

    Tuesday, March 22, 2011 6:12 PM
  • suzyQ, let me tell you what I just did.

    I created a tblPeople (PeopleID - PrimaryKey), as you suggested, but I also included the following fields: "Phone Number" & "Email". I also changed the tblClients with the following fields: ClientID (Primary Key), PeopleID_FK (Foreign Key), FirstName, LastName.

    Access is automatically giving me a One-to-Many Relatioship between the two tables (tblClients & tblPeople). I tried editing the relationship and there's no option to change it to a One-to-One Relationship.

    I decided on doing just those two tables to test the steps out.

    Tuesday, March 22, 2011 7:07 PM
  • Don't put first name and last name in the client id table, their names come from the people table.  Create an index on the PeopleID_FK field that is unique (after you delete the relationship) and then recreate the relationship and it will be one to one.  You are right when you say that Access decides what kind of relationship it should be, but that is based upon the indexes that you create.
    Tuesday, March 22, 2011 7:45 PM
  • Wow! Finally managed to complete "People" & "Clients" tables. And I am able to populate both tables using a form/subform.

    I guess here is another obstacle, I have a main form which has: First Name, Last Name, & Phone Number as client info being pulled from the tblPeople. Want to add a subform to this main form for the client's caseworker which will include the following: First & Last Name, DoB, Address, Phone Numbers & email. All of these fields which are in the tblPeople & Caseworker ID which is the only field in tblCaseWorker.

    I assume this is the same problem I may face with client's contact because I intend on have names, addresses, phone numbers, etc. which are in the tblPeople; relationship which is a field in tblClientContacts. Like you rightfully said, client John Doe may have 2 or more emergency contacts in which I need all of their info. Not just the contact name.

    Wednesday, March 23, 2011 6:31 PM
  • You will link the client's main form to the case worker subform via the clientID key.  The information for client name etc and caseworker name etc will come from the people table via the people key.  You can display this information using a combo box and unbound text boxes, but the table that this information will go into is the people table.  If you are using Access 2010 (and maybe 2007) a new feature was introduced that allows you to open form and enter data when it is not in the list of your combo box.  This is a handy feature to use because you can set up a people form and only use it when a person is not listed when you are trying to select them from your client's form or caseworker's form.  If you are using a previous version, you will need to look at the not in list event to help you populate the people table.  If you have any question post back.  If you would like to email me an empty database to help you out tlsilveus @ hotmail . com  Let me know which version of Access you are using as well. 
    Wednesday, March 23, 2011 7:07 PM
  • I did send you an email to the above address.
    Thursday, March 24, 2011 2:35 PM
  • I never received it.  Try again.
    Thursday, March 24, 2011 3:05 PM
  • I resent it.
    Thursday, March 24, 2011 3:48 PM
  • I looked it over and sent back some feedback specific to your question posted here via email
    Saturday, March 26, 2011 2:55 PM
  • Hi Jale01,

    Thank you for posting.

    Have you got a workaround for your problem? Do the suggestions work for you or not? If you problem still exist, just feel free to follow up.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 30, 2011 5:53 AM