Substituting dataentry by refering to another table

[Majeed]

Hi.



I have several tables in my sport database. The table "Players" contains the
name of all players. The table "Matches" contains several fields including
the name of "Players". There are 23 fields that contain players name in
"Matches" table, field naming convention is like "P1" , "P2" ..etc



It is cumbersome job to type full players name every time I want to update a
match and of course liable to make a typing error.



I want to create a update form for "Matches" and instead of typing each
players full name , I want to refer to "Players" which has an index. Simply
, instead of typing "Beckham" , I want to select the index number for
"Bekham" which is 432 in "Players" table.



Any help is appreciated.

 

[Douglas J. Steele]

Hopefully you're using a form to do this. Instead of binding the Player
field to a text box, bind it to a combo box that uses the Player table as
its RowSource.

 

[Ken Sheridan]

There is a fundamental design flaw in your database which, as well as being
an incorrect design in principle, it will make querying it difficult.
Instead of having 23 players columns in the matches table you should have a
third table MatchPlayers with two columns, MatchID and PlayerID as foreign
keys referencing the primary keys of the Players and Matches tables. These
two columns would form the composite primary key of this table. Consequently
you will have multiple rows per match in MatchPlayers, one row for each
player. What this table is really doing is modelling a many-to-many
relationship type between players and matches by resolving it into two
one-to-many relationships. It could contain other columns such as
GoalsScored for instance to record the number of goals scored by each player
in the match (give it a DefaultValue property of 0).

The matches table would have columns representing the attribute types of the
matches entity type, e.g. MatchID, MatchDate, Venue etc. The Players table
would similarly have columns representing the attribute types of the players
entity type, e.g. PlayerID, FirstName, LastName etc.

For data entry you'd have a Matches form in single form view with a
MatchPlayers subform in continuous form view. The LinkMasterFields and
LinkChildFields properties of the subform control would both be MatchID. The
subform would contain a combo box bound to the PlayerID column and set up as
follows:

ControlSource: PlayerID

RowSource: SELECT PlayerID, FirstName & " " & LastName AS FullName FROM
Players ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

The subform might also contain controls bound to any other columns in the
MatchPlayers table such as a text box bound to GoalsScored.

Once you've entered the main match details in the main parent form, date,
venue etc., you can then enter as few or as many players per match as you
wish by entering one row for each in the subform, selecting a name from the
combo box in each case.

Ken Sheridan
Stafford, England