Hi,


Change the table design into:

UserID, WeekNumber, TeamID (fields Name)


instead of

UserID, Week1, Week2, ..., Week17



and build an index, unique, over the couple UserID and TeamID. That would
forbid a UserID to select the same team twice.

Since the "lookup" are evaluated when you open the table, there is no
practical way to get the team "not used for a given user", DYNAMICALLY,
unless you use a form for that, and evaluate the combo box RowSource
appropriately, in the GotFocus event, as example:


Me.ComboBox.RowSource="SELECT team FROM allTeams WHERE team NOT
IN( SELECT team FROM UsersSelections WHERE User=" & Me.User & ")"


where allTeams is a table listing all the possible teams, and
UsersSelections the table I suggested at the beginning.



Hoping it may help,
Vanderghast, Access MVP


"Lucky Louie" wrote in message
...
Quick background...2 tables. 1 with all user information including Week1,
week2, week3 etc. (up to week 17). Every week, i have to enter a value
into
that specific week #, but I cannot use the same team twice throughout the
17
week period. The teams come from another table, using a dropdown list
(there
are 32 teams in table 2).
I am trying to get a query that only shows the teams (from table 2) that I
haven't already used.
Any help is greatly appreciated. (go dolphins!)