Unanswered: Help regarding Form/Subform records for Many-to-Many relationship databse
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
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.
Last edited by keshukla; 12-27-11 at 14:06.
Reason: Attach documents
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).
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
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.