Show Match Venue for Country only

[nutthick]

I have a Football Match dB storing games played in competitions all over the world. Each match takes place at a venue in a country. When the user enters data, they enter a competition (Comp table) specifying the country in which it was held (from the Country table). They then enter a match for the competition (Match table) from where they select a venue, via a valuelist (from the Venue table). What I want to do is only display the venues for the country the game was played in. At the moment I can either display all values from the Venue table or nothing. My relationships are screwed up, but I can't figure out how to do correct it.

Does anyone have any ideas?

Thanks

[transpower]

Link the country field in Venue to the country field in Comp. Make the value list of the venue be based on the fields in Venue but show only related.

[nutthick]

transpower - That's how I have it at the moment, but with no luck. My value list is related to the Country table. The relationships I have at the momment are Match to Competition (CompID), Match to Venue (VenueID) and Venue to Country (CountryID). Competition also gets it's country info from the Country table.

I think this could just be a product of too many hours infront of the screen.

[stanley]

In your Venue table, do you have the country each venue is in? If so, you base a relationship on the country chosen in your Competition (if I have that right) table, and your value list (based on that relationship) will show the venues in that country.

Also, you could base your country list on that same table (show all values from Country field.)

-Stanley

[nutthick]

Thanks for your help, but still no luck. I've attached a file with the same relationships that I've been trying to get working, with the problem on the matches layout. If someone could point out what I'm doing wrong I'd appreciate it.

Relationships.zip

[andygaunt]

Hi -

Your relationships were almost there. Unfortunately you were trying to pull a related set of values through a relationship that did not have any current values (since it was the field you were trying to colect)

I moved the venues relationship to come off of the compettions TO based on country ID. Then your value list was set to pull the vvalues from the ID and Name fields based on the Competitions TO.

I have attached the file back for you changed.

HTH

Relationships.zip

[nutthick]

Thanks andygaunt that got it working. None of my books cover those sort of relationships in any detail. It's nice to get a working example running.