Thanks for the post and welcome to the Forum.
What table is your layout based on? Your search should be done on a table based on Current Players. If your layout is based on Players, you'll be searching All Players. If your Table is based on Current Players...you'll only be looking through the Current Players that have a linked "violin" field.
If you want to find only people who are current, and played violin in 2009, you'll have to do a search for both "Violin" AND "2009" since your current player DOES have a violin relation to the AllPlayers Table, it just happens to be from 2008...they will show up unless you limit the search by adding the second search criteria.
Did I make sense?
Thanks for the reply.
It still seems counter-intuitive to me.
My layout is based on Current Players, and that's where I'm doing the Find. I understand that if I search for "violin" AND "2009" I'll get the right thing, but I don't even have the year displayed in Current Players because it's the same for every record. Yes, I can write a script. But it just seems weird that a search for "violin" gives back records with "flute" as the instrument. How is a more naive user of my database going to cope with that?
Have I designed things poorly? Perhaps I should copy over the Instrument field from Players to Current Players, but that seems to me to defeat the whole point of relational databases.
I am not following your structure too well, but a couple of points stand out:
1. When you search a related child field, you will find all parent records that have at least one child that meets the criteria.
2. A global field cannot be indexed, and therefore cannot be used on the "other" side of a relationship. IOW, the relationship is not valid in the direction towards the global matchfield.
3. I don't see the advantage of having two tables for the same thing.
Ah ha! The problem is using a global field in a relationship. OK, that's easy to change. Thank you.
(The point of the two tables, by the way, is that I want to store extra information for Current Players, but don't want to have all those fields for all Players.)