Newb Question

[Calluna]

Hi, so I am very new to Access (as in decided last week I was going to create my own database). I run a mentoring program and need a database to keep track of our participants (our excel sheet was out of control). So far I've been able to figure most things out on my own. I have two tables "Mentor_Data" and "Youth_Data" both with corresponding forms "Mentor_App" and "Youth_App". On those tables/forms I have a field for "Mentor_Name" and "Mentee_Name". What I would like is to be able to enter a name in the "Mentor_Name" field on the "Youth_App" form and have the corresponding "Mentee_Name" fill in automatically on the "Mentor_App" form. Is this possible?

Thanks!

[June7]

Don't understand what you want. Why would you need to select mentor on youth form and have that feed over to mentor form?

[pbaldy]

I understand what you want, but I'm not sure the structure is right. Can a mentor only ever have one mentee, and vice-versa? I would lean more towards a junction table that would accommodate multiples, perhaps with dates to hold the history.

[Calluna]

I figured out what I wanted. I have a tendency to over complicate things. So now I have two tables [Youth_Data] and [Matches]. I would like to make it so whenever I add a new record to [Youth_Data] in the "Youth_Name" field that it will make a record with the same name in the "Mentee_Name" field of the [Matches] table.

[June7]

Try form/subform arrangement.

Main form bound to Youth_Data.

Subform bound to Matches.

The subform container control Master/Child Links properties will synchronize the related records by linking on the youth ID primary/foreign key fields.

Enter new record in main form.

Select mentor in subform form. The mentee ID will automatically populate.

Other options:

1. one form bound to Matches with two comboboxes to select mentor and mentee.

2. main form bound to mentors table, subform bound to Matches with a combobox to select mentee

[Calluna]

Thank you so much! A subform worked perfectly for what I needed. Because all mentees only have one mentor but some mentors have two mentees (we run two different programs). So now I have a nice "snapshot" of the Matches table on each person's form. Thanks!

[June7]

Then I am not sure you need the Matches table.

Could document the assigned mentor with a field in mentee table. Unless you want a history of assignments. If mentor assignments are changed, is that change important to document?