using a form

[jlwood44]

I am trying to enter results from a chess match. I am trying to enter a team
ID and
then use that from a table to select the correct individual. How should
this be accomplished?

I am guessing that I want a single table for the players from all the teams.

I am looking at this idea:

Match Date Home team Visiting team
1 28 Oct 05 Snider Wayne

Team1 Pl# Player1 PlayPos Team2 Pl# Plr2 Last
First PlayPos
W 17 Douglas, Bryce 2 Sn 43 Smith
Joe 2

Match/Exhib Pl1 Win Pl1 Loss Stalemate Winning team
M 2 1 0
W

I also plan to have a table of matches. How do I set it up so that if I
select Match 1, it automatically shows the other info?

I'm anticipating that my 3rd table would include:
Match #
Player1 #
Player1 Pos
Player2 #
Player2 Pos
Match/Exhib?
Play1 Wins
Play1 Losses
Stalemates
Match winner

Thank you for the help

 

[tina]

suggest you review your tables first, to make sure they are properly
normalized. to start with, you should have

tblTeams
TeamID (primary key)
TeamName

tblPlayers
PlayerID (pk)
FirstName
LastName
TeamID (foreign key from tblTeams)

tblMatches
MatchID (pk)
MatchDate
Host (home team)
Visitor (visiting team)

from there i'm not sure of the format you'll need, because i don't know how
a match is structured. i read the additional details you posted, but didn't
understand the context. if you can explain (in English, rather than database
terms) how a match "works", i'll try to offer further suggestions for proper
table structure. once your tables are built correctly, working with the data
in forms (and reports) will be much simpler.

hth

 

[Ed Robichaud]

Great start from Tina. You'll need to add a Results table to track player
win/loss/draw/etc. record.
-Ed

 

[tina]

btw, and to clarify:

tblMatches
MatchID (pk)
MatchDate
Host (home team) (foreign key, links back to TeamID field in tblTeams)
Visitor (visiting team) (foreign key, links back to TeamID field in
tblTeams)

hth

 

[jlwood44]

I think I basically had that part figured out. BUT, how do I create the form
with subforms?
(2) How do I indicate the team, and only look at the players from that team?
(3) Can I have the same table open in 2 places - for Player1 and Player2?
(4) What happens if my list does not include all the players? Can I add a
player there or do I have to shut down and go to the players table/form?

 

[tina]

well, as i said, the tables list is not complete because i don't know the
structure of a match. you may need one or multiple tables, which will be
pivotal to the success of the database; and how you build the forms will be
to a great extent dependent on that table(s) structure. so until we
establish how the data should be stored, i can't comment on the forms.

hth

 

[jlwood44]

The tables are:
1) Meets
2) Players
3) Schools
4) Matches

The Matches include the fields of 1) PK MatchID, 2) MeetID, 3)
Match/Exhibit, 4) Player1, 5) Player1Pos, 6) Player2, 7) Player2Pos, 8)
Player1Wins, 9) Player1Losses (Player2Wins), 10) Draws and 11)
MatchWinner(school)

The player table has 1) PK PlayerID, 2) School Abbreviation, 3) First Name
and 4) Last Name.

My concern is how to have the Match FORM include the 'subform' of the
players - so that the player is identified by name and PlayerID to be correct.

 

[jlwood44]

Am I going to want to use a ComboBox or a ListBox?

How do I do such a thing - especially based on the school (which I forgot to
include in my original matches table)?

 

[tina]

your matches table appears to be non-normalized; in other words, structured
incorrectly. but again, it's hard to say for sure without an understanding
of how a match "works". perhaps somebody else will be willing to help you
create forms based on a non-normalized structure. good luck with your
project.