Table 1 has a field called [Notify To] this is a user list (with the user's email address) multiple users can be selected in one record. This table allows to send email notifications to whoever is chosen in [Notify To]
Table 2 is for when [Notify To] from Table 1 is out of office. Table 2 has a record for every user list from [Notify To] with a corresponding back up person to send emails when he/she is out of office, also this is the primary key used in Table 2.
I have set up the logic when the user is out of office, the back up person will receive the notifications.
How do I created a relationship between the two tables using the field [Notify To] if I have multiple users from a record in Table 1? The relationship only works when Table 1 only has 1 user selected. My problem is I cannot connect table 1 and table 2 when the records has 2 or more users selected.
------------------------------
ammba
------------------------------