Filtering by Related Record

So I have two tables. I'm going to be very specific and use very general terms to make sure there isn't any confusion, so thanks ahead of time for bearing with me since this description might seem a bit monotonous.

Table A
Name_fk
Item Number

|

Table B
Name_pk
Team

I put a relationship between Name_fk and Name_pk.

Name_pk is a list of names and what team they are. Name_pk is filled with entirely unique values, so you know John Smith is on Team Blue there is only one record with "John Smith" in the Name_pk field and that record has "Team Blue" in the Team field.

Now Table A will have a lot of repeat items under Name_fk. There will be ten rows with John Doe in them, ten Jane Doe rows, ten Joe Doe rows, and ten Jess Doe rows. Joe and Jane are on Team Green according to Table B, and Jess is on Team Blue with John.

In the relationships section of the Database Manager, I have linked Name_pk and Name_fk.

I need to be able to search for Team Blue, and only have John and Jess's item numbers show up, or search Team Green and have only Jane and Joe's Item Numbers show up. So far I have been unable to get this to work in my current solution, although a test solution works fine.

Edited February 2, 20169 yr by mmarino