First, I apologise in advance as I am new to ACCESS.
I am currently building a form from tables already created - NOTE: the tables were merged from another database, I have no control over the table primary keys, data types or field properties of those tables... I can not build proper relationships due to the Data Types being inconsistant.
Goal:
I have a single table where I need the information entered from a form w/subforms to reside.
The fields in this table are: Date - Group Name - Group Leader - Area - Employee - Work Description
I want to be able to enter the date once, the group name once, the group leader once, the area once.
I want to be able to select multiple employees where the above applies (creating multiple records)
I want to be able to select multiple work descriptions to each of the multiple records above (creating additional records)
Example:
Main Form (Group Setup)
Date: 07/31/2014
Group Name: First Crew
Group Leader: John Doe
Area: North
SubForm (Group Members)
Employee Name: Sarah
Employee Name: Mark
Employee Name: George
Second SubForm (Work Description)
Work: Cleaning
Work: Mowing
Work: Window Washing
The table should show one record for each employee, showing each work description Example:
Date - Group Name - Group Leader - Area - Employee - Work Description
7/31/2014 - First Crew - John Doe - North - Sarah - Cleaning
7/31/2014 - First Crew - John Doe - North - Sarah - Mowing
7/31/2014 - First Crew - John Doe - North - Sarah - Window Washing
7/31/2014 - First Crew - John Doe - North - Mark - Cleaning
7/31/2014 - First Crew - John Doe - North - Mark - Mowing
7/31/2014 - First Crew - John Doe - North - Mark - Window Washing
7/31/2014 - First Crew - John Doe - North - George - Cleaning
7/31/2014 - First Crew - John Doe - North - George - Mowing
7/31/2014 - First Crew - John Doe - North - George - Window Washing
Issue:
I have used the form wizard to select the table I want the information to go to.
I have used combo-boxes to list only values I want in those fields.
I end up with records containing information from the Main Form only
Additional records containing information from the first SubForm only
Additional records containing information from the second SubForm only
- it also wants to duplicate the primary key for each record (number/auto number - doesn't matter)
HOW do I get the form to recognize the main form information should repeat for each subform - and each subform information should repeat for each second subform?
Note: All control sources are set to the field I want the information to reside.
Also, This information feeds to another database which creates a template for each of the employees to enter a finished job comment for each work description - shown by crew.
Thanks in advance!!