Counting a parameter of one record to multiple related records...
I have a main table where I have three fields for crew members (Crew1, Crew2, Crew3). I have a table that has all of the crew roster in it. I want to track a certain field in the main table and do Ave (), Count (), Min () and Max () for each crew member. The problem is that the crew members don't always get entered into the same field (Crew1, Crew2 et al). So I thought I'd create a field (crew-search) that combines the crew members separated by commas and relate that to the related field in the Crew table. This works great but it's only doing it for the first crew member. I'm sure I've missed something. I've tried changing the format of the (crew-seach) to use carriage returns instead, and it still only counts the first crew member. I was looking at the relationship table and tried to create multiple relationships but the word AND scared me away. I'm thinking it's more of an OR function. What am I missing?
Thanks in advance