Choosing and Event and Employee from Existing DATA

[cbayardo]

Hi, this is my scenario: tblClient, tblContract, tblEvent and tblEmployees.
Clients have Contract and Contracts have Events. Example1:
Client=Weddings.com, Contract=December, Event1=Xmas Wed. Example2:
Client=Weddings.com, Contract=December, Event2=New Years Wed. Example3:
Client=Weddings.com, Contract=July, EventJ1=SummerWed, etc, etc. This part I
think I have it solved. I have a form with a subform and that subform with
another subform. What I can't figure out is how to link the Events table with
the Employees table but I want to choose the Employees for each Event. For
Example, Event1(Xmas Wed) needs 3 employees. So I want to be in the Events
Form or Table and choose John, Paul and Ringo, from a list of Employees. How
can I choose those employees while I am in the Events table? This would be
one-to-many relationship. One Event to many Employees. Hope this is not
confusing, that is why I am adding all those examples.

Thanks a lot for your time.

 

[tina]

well, you don't send an employee to one event and then fire him/her, right?
(at least, i hope not!) so employees work more than one event. that's a
many-to-many relationship: one event may have many employees assigned to
it, and one employee may be assigned to many events.

so you need a junction (or linking) table, as

tblEventEmployees
EvtEmpID (autonumber, primary key)
EventID (foreign key field from tblEvent)
EmployeeID (foreign key field from tblEmployees)
(if you want, you can eliminate the EvtEmpID field, and use the two foreign
key fields as a combination primary key for this table.)

remember to create the relationships in the Relationships window. if you're
wanting to add employees using the form bound to tblEvent, then create
another subform on that form, and bind the subform to tblEmployeeEvents.

hth