"Find Unmatched Query" help for Football pool

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lucky Louie - 10 Sep 2005 13:50 GMT

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!)

Reply to this Message

Michel Walsh - 12 Sep 2005 20:03 GMT

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

> 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
[quoted text clipped - 7 lines]
> haven't already used.
> Any help is greatly appreciated. (go dolphins!)

Reply to this Message