linking a string in a textbox to another field

[shanice]

Hello!

I have two tables that I want to link together. One table lists staff and the trainings that they've taken. The other lists projects and the roles that each staff person played. I want to be able to query a person's name and see their trainings, projects, and roles. The only problem is that the roles fields have multiple staff listed: i.e., the sr. analysts field shows "Bob Smith; Tracy Lee", etc. Is there any way to code this so that it matches the roles fields (a total of 6) with the staff names from the training list?

Thanks in advance...

 

[Galaxiom]

You need three tables. Staff, Projects and Roles.
Each staff member has a separate records in the Roles table for each role and Project.

Roles will have three fields, StaffID, ProjectID and RoleID.

 

[shanice]

Thanks! Do you know how I can count the number of projects each staff person works on? Some staff play more than one role on each project, so I wouldn't want it to count the duplicates.

 

[Galaxiom]

Create a query on the Roles table and include only the StaffID and ProjectID fields.

Turn on Totals in the query and set StaffID to GroupBy and ProjectID to Count.

 

[shanice]

Thanks! This works but it's counting the duplicates. I tried using SELECT DISTINCT coding in the criteria field but it keeps saying there's an error.

Create a query on the Roles table and include only the StaffID and ProjectID fields.

Turn on Totals in the query and set StaffID to GroupBy and ProjectID to Count.