Results 1 to 7 of 7
  1. #1
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86

    How would you develop a form for three tables in a relational database?


    I have a relational database, table A is connected to table B through table A's primary key. Table B is then connected to Table C through its own primary key.
    So in other words, in order for me to enter data for a topic/record I must first enter the data into A before putting it into B and then C through the linked keys.

    For example, a Customers Table (A) linked with a Orders Table (B) through a primary customer ID key and the specific Orders are then linked with subsequent "Purchases" (C)

    Any advice? Currently I have three separate forms but I'm not sure what the best way is to combine them into a concise form.

  2. #2
    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
    A form for what? Subject matter? Audience?
    Start with a description of the business the proposed database will support.
    Get your tables designed and tested.

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    You can drop your form for TableC into TableBs form. Just drag and drop from the Objects window. Now you have a subform for TableC on TableB's form. Next Drag and drop your TableB form into your TableA's form.

    Haven't watched it but this will probably clarify
    https://www.youtube.com/watch?v=Yk-OtW4IHNA

  4. #4
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86
    Click image for larger version. 

Name:	form design.png 
Views:	18 
Size:	7.8 KB 
ID:	33226

    So, something like this?

    I would have to input data into A, update it on the form (through a button perhaps?) => which would then unlock the form for B and link the new entry in B with the newly entered data in A. And once again for B => C.

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Yup... I'm sorry, are you asking another question? See my post #3. Research the term "access subform". In your case you'd have a form with a subform with a subform.

  6. #6
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86
    Quote Originally Posted by kd2017 View Post
    Yup... I'm sorry, are you asking another question? See my post #3. Research the term "access subform". In your case you'd have a form with a subform with a subform.
    Not at all. I was just restating your suggestion. I am indeed in the process of researching subforms, I had tried to implement them earlier on but I don't think I was proficient enough to quite pull them off.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by TerraEarth View Post
    I would have to input data into A, update it on the form (through a button perhaps?) => which would then unlock the form for B and link the new entry in B with the newly entered data in A. And once again for B => C.
    No!

    Don't duplicate information. When you entered something into one table using one form, then you can find it always from this table whenever you need. Into linked tables/forms is entered info you can't store in main table.

    An example:

    You have several teams and you need to keep track of people in teams and tasks they have, so you crate a table
    tTeams: TeamID, TeamName, ...
    and create a single form fTeams, where you can select a team from existing ones, add a new team, or delete/mark as obsolete a team.

    You can't enter team members into tTeam, as then you must have a lot of fields like TeamMember1, ... TeamMember999, you have to redesign you application whenever a new team member is added or removed, you can easily run out of available columns etc. So you create a table
    tPersons: PersonID, ForeName, LastName, ...
    and create a form where you can enter new persons and update person info. This form will be somewhere in your application, but for current setup we can ignore it - currently the table tPersons is all we need.
    To link persons to team, you need a table
    tblTeamMembers: TeamMemberID, TeamID, PersonID
    and you need a continuous form fTeamMembers based on this table. Text box controls with TeamMemberID and TeamID will be hidden, and you have the wisible combo box cbbPerson, which is linked to PersonID in tblTeamMembers, and has a query from tblPersons as row source, with columns PersonID and a calculated column with person full name. You add this form as subform into form fTeams, and it will display a list of members for team active in fTeams.

    The same goes for team members tasks. You end with table
    tblTeamMemberTasks: TeamMemberTaskID, TeamMemberID, Task, ...
    and with continuous form fTeamMemberTasks, which you add as subform into form fTeamMembers, or as subform into fTeams (in this case it must be linked to unbound control on fTeams, where active TeamMemberID from Team members subform is written whenever it changes). As result, in fTeamMemberTasks all tasks for currently active team member in form fTeamMembers are displayed.

    The only info which is written from main table into child/linked table at all levels is primary key of main table. And this is done automatically, when forms are set up properly.

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

Similar Threads

  1. Replies: 3
    Last Post: 10-18-2017, 04:01 AM
  2. Replies: 4
    Last Post: 04-16-2015, 05:01 AM
  3. Replies: 1
    Last Post: 07-23-2014, 08:07 AM
  4. Replies: 1
    Last Post: 12-26-2013, 09:41 AM
  5. Replies: 3
    Last Post: 11-23-2013, 06:28 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