One Form - multiple entries

[yi-chan]

Hi all,

I'm trying to creat a Form which will let me just enter all the data once
and create multiple entries into the table.

My database looks like this:

tbl-Players
<PlayerName - PK>
//I've created 8 players. Unlikely to be too many more added. >

tbl-Games
<Game# - PK>
<Date>
<1st Place>
<2nd Place>
<3rd Place>

tbl-Plays // This table maps the relationship between Players and Games.
As Players "plays" Games.
<PlayerName - FK>
<Game# - FK>
<BuyIn>

Here's what I'm trying to do.
I want to create a form which has the following attributes:
1. Game #
2. 1st Place
3. 2nd Place
4. 3rd Place
5. Attendee#1 (basically a Player who attends)
6. Attendee#2
7. Attendee#3 (and so on)

Then, when I hit submit, the "tbl-Plays" should update with all the players
who played at the game.

The idea is this:
We play 4 games a night with 8 players.
If I manual entry the "tbl-Plays" table, it will take 4x8 entries = 32
times.

If I can get a Form which lists all my players, I can simply do a
"check-box" or "a button" to select my players. Much easier than entering
data 32 times.


Any help would be appreciate.

thanks

 

[yi-chan]

Sorry, afterall that, I forgot to ask my question.

Which is, is this possible?

 

[Brian Bastl]

Yes, it is possible. Look into using a multiselect listbox to add players to
games.

Also, you might want to consider reworking your tables.

tblPlayers
PlayerID <PK - autonum>
PlayerName

tblGames
GameID <PK - autonum>
GameName

tblRankings
RankNum <PK - integer> '8 entries

tblPlays
PlayID <PK - autonum>
GameID
GameNumber
PlayDate

tblGameParticipants
PlayID --|
PlayerID --|--- Composite Key
RankNum --|

You could base your main form on tblPlays, and your subform on
tblGameParticipants setting PlayID as the master/child links.Put a
multiselect listbox on your main form showing available players name. Then
use a command button to run an append query to add the player's ID and
PlayID into tblGameParticipants. That way, all you have to input is their
ranking at the end of the game. And since RankNum would be part of the
composite key, it won't allow for duplicate rankings per player per game.

HTH,
Brian