VB Query Scenario Problem (confused me)

Ok can any of you help me to create a query from this scenario

I have 3 Tables

Competitor Table
Event Table
CompetitorEventTable

These comprise of:

Competitor Table:
Competitor Number - Primary Key
lotsa competitor junk

CompetitorEventTable:
EventCode - Foreign key (or primary cant remember)
Competitor Number - Foreign key (or primary cant remember)

EventTable:
EventCode - Primary Key
Lots of other event junk

Ok now here's the problem

on a page i have 2 combo boxes.

One to get competitor number and another to get eventcodes

Now in the events combo, i want it to list all possible events, that the competitor number "hasnt" entered.

And i'm stuck as to how to do this

the solution would possibly even include a gender attribute,
so that the event list, listed all events a competitor could enter excluding those not of his gender.

Cause both Event and Competitor tables have a gender attribute.

I'm sure the code for this could be fit on 1 or two lines....

but i cant figure it out :/

Any help would be appreciated.

These are the nasty queires...
You need a flag field to filter by. Just flag alll those they have entered and list all those not in the second combo. The yes/no field in a table should do it. You may need to run a query to set that flag tho...

Regards

Vince

Sounds complicated

time for some creative programming i think.....

I guess something like this shd work:

Code:

---

SQL = "select distinct(Sport) from EventTable where eventcode NOT in (select eventcode from competitorEventtable where CompetitorNumber = '" & varCompetitorNUmber & "')"

---

About the gender thing - I didnt get u.

:p

Thanks very much, with a bit of creativity, i got it to read a recordset from a recordset from a recordset.

Prolly not that efficient as far as system resources go, but for a uni project , just the ticket :)

Any way ta muchly for the help