Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011
    Posts
    3

    Unanswered: Help regarding Form/Subform records for Many-to-Many relationship databse

    Hi Everybody,

    I have M-T-M relationship for Employee Training Databse.
    I have created the tables and relationship. Please refer to the attached image.
    The requirement for the database.

    Tables in the database: Each table has (PK) Primery Key as Autonumber
    Employees
    Courses
    EmpCou
    CourseYear
    CourseDocs

    Each employee may have more than one courses and Each course can have more than one employees.
    Each course may have more than one years for the course related Documents/Certificates. (i.e. 2011, 2010, 2009, 2008, 2007 etc.)
    Each Year may have more than one document or certificate and related info like issuedate and location where course was completed.

    Please let me know your expert comments if the table structure and relationship can fulfill the requirements listed above.

    The main question: Assuming that tables and relationships are ok.

    I have created one main form "Courses" with a combobox in the header with all the courses as drop down list. I have managed to write the code for combobox selection. Once the course is selected the field from the Courses table shows the coursename in the textbox on a form as well as in the header to a label. Please refer to the attached image.

    Now, I would like to display all the related records for the selected course (from combobox) in the Courses form such as all the employees as a datasheet in subform who completed that course and once user clicks on any one employee name in the subform; it shows in another subform for all the years as a datasheet for that employee for that course and once user clicks on the year; it shows the course related documents in another subform as a datasheet for that course for the selected employee and for the selected course.

    I know very well that it seems to ask too much but even if anybody can tell me how to display records specific to any selection for combobox and selection in the subform datasheet.

    Thanks in advance.
    I hope I have explained everytihng clearly enough.
    Attached Thumbnails Attached Thumbnails Relationship ETR Database.jpg   Form Courses.jpg  
    Last edited by keshukla; 12-27-11 at 14:06. Reason: Attach documents

  2. #2
    Join Date
    Dec 2011
    Posts
    3

    Partial Succcess

    I have managed to solve on my own and by learning from online articles and some key videos. But I have yet to go thousand miles.

    For anybody who wants to show subform in a main form to browse through the records for specific records in subform for a specific record in a main form.
    Simple case: (It can get really complicated if requirements of the database project)
    Create three tables with required fields in each table.
    Employees - with Primary Key (PK) as autonumber
    EmpID (PK)
    EmployeeName

    Courses - with Primary Key (PK) as autonumber
    CouID (PK)
    CourseName

    EmpCou - with Primary Key (PK) as autonumber (Junction Table)
    EmpCouID (PK)
    EmpID (FK)
    CouID (FK)

    Create relationship between tables:
    Drag EmpID (PK) from Employees table to EmpID in EmpCou table
    It will create One-to-Many relationship
    Drag CouID from Courses table to CouID in EmpCou table
    It will create One-to-Many relationship

    So both Employees and Courses table are related to each other as Many-to-many relationship to each other via juntion table.

    Create two Queries:
    QueryEmp

    using Query wizard:
    Select all the fileds from junction table (EmpCou) + Select all the fields from Employees table Except EmpID (PK).

    QueryCou
    Select all the fileds from junction table (EmpCou) + Select all the fields from Courses table Except CouID (PK).

    Create two forms for later use as subforms.
    ListEmp
    Show all the fileds from QueryEmp except the Primary keys and Foreign Keys.
    i.e. (Make these Primary and foreign keys visible to No)
    ListCou
    Show all the fileds from QueryCou except the Primary keys and Foreign Keys.
    i.e. (Make these Primary and foreign keys visible to No)

    Create two forms to use as main forms.
    Employees
    Drag EmployeeName field from Employees Table onto form.
    Drag the subform ListCou from the form list (on the leftside) onto the form.
    Select the subform in design view and enter/type Link Master Fields (in subform properties) as EmpID
    and enter/type Link Child Fields (in subform properties) as EmpID
    Save the form

    Courses
    Drag CourseName field from Courses Table onto form.
    Drag the subform ListEmp from the form list (on the leftside) onto the form.
    Select the subform in design view and enter/type Link Master Fields (in subform properties) as CouID
    and enter/type Link Child Fields (in subform properties) as CouID
    Save the form.

    Now if you open Courses form in form view and browse through all the courses; it will show all employees for that course in subform and similarly Employees form will show all the courses done by that employee in the subform.
    Provided you have already entered the data in respective tables.
    I am right now working on making a single form to enter the data for all respective tables at the same time.


    I am sure lot of people in this forum might already know what I have stated here but since I figured out myself after lot of trial and error I thought I should write it down for people like me if any. This is my first ever experience for creating a database application using any software.

    Hope to finish the tough task with precision and proper way.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •