select team members

D

dennis

i am new to this so please excuse my ignorance but you guys have helped me in
the past
i want to list some 100 names and from these select say 50 wishing to play
on a certain day?once selected i do not want them to appear in the original
list to avoid duplication,but i want that same original list to be available
in full to again be the basis of further selection on another day.
i hope this makes sense to you.perhaps someone has already designed a
programme for this sort of application
may thanks Dennis
 
J

John Spencer

Sounds as if you need a table to store the selectees for a specific date.
Table: Players - information on the players
Field: PKID - autonumber field (PrimaryKey)
Field: LastName
Field: FirstName
Field: DateOfBirth
etc.

table: GamePlayers - Information on who is playing on a specific date

Field: GameDate - DateTimeField
Field: PlayerId - Long and it will contain one of the values in the
primary key of the players table
(Put a unique index of the combination of GameDate and PlayerID and you
won't be able to enter anyone twice for the same date)

You can then build a query that will show you everyone, everyone playing
on a specific date, or everyone not playing on a specific date.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
D

dennis

hi John you are on the right track to solve my query but!!
when and where do i fill in the dates. so far i have created a table
players ID pk primary key
full name text
tag No unique No for each player
a second table game players
game date date/time
player id number(not auto no pk)
full name text
tag No number
i have created a unique index between game date and player id
Sorry to waste your time,but what form do i use to fill in dates and produce
a list of players and non players on that date
again thanks for your help rgds dennis
 
D

dennis

john i know you must be busy !!i have read your post again and need help on
building the query you have suggested thanks again dennis
 
J

John Spencer

Assuming you have the table structure indicated.

A query like the following will show you everyone that played on a specific date.
SELECT Players.*
FROM Players Inner Join GamePlayers
ON Players.PKID = GamePlayers.PlayerID
WHERE GamePlayers.GameDate = #2007-12-22#

In the design view (query grid)
-- Add both tables
-- Drag the PKID field to the PlayerID field to set up the join
-- Select the fields you want to see
-- Select GameDate field and enter the date in the criteria

To see everyone that DOES not play on a specific date
SELECT Players.*
FROM Players
WHERE Players.PK NOT IN
(SELECT GamePlayers.PlayerID
FROM GamePlayers
WHERE GameDate = #2007-12-22#)

In design view (query grid)
-- Add the Players table
-- Select the fields you want to see
-- Under Players.PK type the following in one "cell" of the criteria.
NOT IN ((SELECT GamePlayers.PlayerID FROM GamePlayers WHERE GameDate =
#2007-12-22#)

Hope that will get you started.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Top