How to write this code for an event procedure or query?

[Ben Shaw]

Hello,

I have 3 tables:

tblPerson (PersonID, TeamID & other fields)
tblGames (GameID, Person ID, TeamID & other fields)
tblTeams (TeamID & other fields)

I want to create a query that identifies where teams
(TeamID) have had a Game (GameID) that did not involve a
particular Person (personID) i.e records that will not
exist in the Games table.

So for example, lets say the tables are populated as
follows.

tblPerson: PersonID = 1
PersonID = 2
tblTeams: TeamID = 1
TeamID = 2
tblPersonTeam: PersonID = 1, TeamID = 1
PersonID = 2, TeamID = 2

I want to identify the fact that PersonID = 1 has NOT
been involved with TeamID = 2 and return the TeamID.

How do I do this?

All the ID fields are integers.

I'm using Access 97.

Thanks in advance,

Ben.

 

[Jesse Aviles]

You can create a parameter query. First write a query based on tblPersonTeam. On the Criteria cell
of the PersonID write NOT [Person ID]. When you run the query, you will be promted for the person
id and the query will return the TeamID for which the person was not a part of.

 

[Guest]

I don't quite see the reason for including PersonID in tblGames as well. If
you have TeamID already the persons of a team seem to be redundant.

Regards Michael

 

[John Nurick]

Hi Ben,

In your message you show two incompatible data structures, neither of
which fits the real-world situation you are modelling. You'll certainly
need more than three tables; maybe something more like this (* means
primary key, + means foreign key):

tblPersons - PersonID*, FirstName, LastName etc.

tblGames - GameID*, GameDate etc.

tblTeams - TeamID* etc

tblGamesTeamsPersons - GameID*+, TeamID*+, PersonID*+
(who played for which team in which game)

The query itself is then reasonably simple. If you search
http://groups.google.com for "frustrated outer join" you'll find many
examples.