limiting table values pulled from one table and put into another

[egiblock]

access 2002.

ok here's my situtation..

two tables (- and variables)..
tbl_courses
- course_name
_ tees_used

tbl_tournament
- tourn_id
- course_name (pulled by tbl_courses, and lists courses from that table
- tees_used (pulled by tbl_courses, but only want to show tees for the course selected above.

lets say that (tbl_courses) looks like this:
course_name tees_used
course1 red
course1 blue
course1 green
course2 yellow
course2 brown
course2 black


what i want is: when someone selects a course_name in the tbl_tournament, i want the tees_used in the tbl_tournament to only show colors available for that selection..
right now, no matter what, i get the list 'red,blue,green,yellow,brown,black' when all i really want for selecting course1 is red,blue,green..
anyone have any sugguestions.

thanks

[asterix]

Your database is not normalized, giving you headaches. If its still possible, you should rearrange your schema now to something like this:

Table_Courses
CourseID (autoid, long integer), course_name (text, 255)
1 course1
2 course2
3 course3
and so on...

In a second table, you put all avalaible tees:

Table_Tees
TeeID(autoid, long integer, primary key), tee_name (text, 255)
1 red
2 blue
3 green
and so on...

in the 3rd table, you create associations based on combinations from the first table, like this:
Table_Course_Tees
CourseID, TeeID
1 1
1 2
1 3
2 1
2 2
and so on.

So here you have your relationship between what tees are available on what courses. Similarly for tournaments, create a Table_Tournaments with an autoid (primary key) named TournamentID and the course name. Then create a 5th table containing only the fields CourseID and TournamentID. If some Tees are only used in some tournaments you can add the field TeeID here too.

Selecting the acurate data should be easy now

- Richard