I am trying to track attendance of employees at training an work. Maybe think... multiple firefighters can attend training or an emergency...
I have a cascading set (3) combo boxes in a form to identify the "event" along with the start date and time and a note text box.

I generate an event... It ends up being one of six complete events that are stored by EventCompleteID pK in the Events complete table.

Two Categories (Training or EmergencyActivation) break down into

The six events: 1. quarterly training
2. monthly training
3. Attend Class / Education
4. Attend Fire
5. Medical Emergency
6. Mutual aid request

Which all have multiple areas / regions that can be associated with them... So you might end up with 1. Training , 2. Quarterly Training, 3. Main Station on xxDate & Time.

Now I track attendance.with a subform with a lookup to get EmployeeId and present yes/ no with a EventCompleteID (fk)... Now the hard part... I wan to be able to take the employees that attended whatever event that was created and with each record and there will be scores etc on the Training events and on the Emergencies location, notes etc.I attempted to have on main form *(EventComplete) with two nested subforms , one for attendance and the second was supposed to score/evaluate ONLY those employees that attended. I am having trouble getting the relationships set up and I am u near 10 or 15 tables and crazy relationships. (One for each event/ attendance and Employee that attended. I want to enter the data in this form and the have it listed in the Employee data access page by employee ID #. Nested Subforms are making my head spin. I feel like Ihave too many forms eith too many keys?? Thee has to be an easier way>>>Any Suggestions or do you need more detail? PS... Should the forms be based off tables of queries, any hard fast rule on that?Thanks again in advance,

G Wallace=-=======How do you assign multiple employees to multiple events and go back later to see a list of what one has accomplished on what date in a subform in their employee tab area. Over My Head a bit here... learning, but it's tpough

PS my newt goal was to have child forms visible or not depending on what was selected in the CBO box set, Works out really great, but I can't get thr relationship done correcly.

Thanks again

I'm not sure I completely follow what you have done, but I think I know what you need to do.

You start with two data tables; Employees and Events. If I understand you, each event may have several attributes, these are all defined in the Events table. Next you have a Sessions table. This lists the time and location of an event. Finally you have a junction table. A junction table creates a many to many relation. Which is what you have. One employee can attend multiple events and one event can have multiple attendees. The junction table is simple:

TjxEmployeeSessions
EmployeeSessionID (PK Autonumber)
EmployeeID (FK)
SessionID (FK)

From there your subform is bound to the junction table and you can use a variety of methods to display the Event info in the subform.

Sorry for being confusing... I have the many to many jx tbl working fine... I have attached a screenshot of my relationships so maybe I can ask a more understandable question.

1. I have a form to generate an event , recordsource is tblEventComplete. In the form I can select either a "training" or "Activation" by CBO(stored in event field.)

2. Then an "Activation" event can be a "Mutual aid" or "InstitutionalOperation" (Purple tables) , a "Training" event can be "Quarterly" or "Monthly" or "course training". Selected on second CBO on form. The choice is stored in the operation field of the eventcomplete table.

3. The details I need to store for each "operation" type are different. Details of those are in the red and purple highlighted tables respectively.

4. I have an attendance subform RecordSource is the jx table (tbleventattendance)(works fine)... The yellow tables are used for my cascading cbo's for event, operation, location... not sure if they even have to be related... they are just lookup tables.

5. My goal was to have a form to create an event (date time, Event) check off attendance(subform) and then be able to score only those individuals that attended that particular event based on the event type (training, activation) and their subgroups... Quarterly Training, Etc, with a nested subform form based on a query of the linked tables. I almost had it, but the query had outer joins and you cannot edit those... I found that out:) I cannot for the life of me get the relationships correct... I can picture in my head the order of events and how to store them... obviously not with good normalization/relationships. I am storing the information completely incorrectly... I truly thank you in advance for any more time you put into answering this question... Your time and patience is appreciated.
(psI have only woked with access for about three months)

Attached Images