Cricket Database

[Guest]

I am working on a cricket database with a series of tables each showing
different categories of information.

So, Player One has personal data in Table 1, batting data in Table 2 and
bowling data in Table 3.

Now, am I right in thinking I have to establish 1-1 relationships between
these tables? I propose including the players full name in each table.

Also - how can I go about showing all three categories of data on one form?

Thanks for your help.

 

[JH]

Matthew,

Very topical!

No you do not want one to one relationships between the tables - Player one
presumably has many entries in batting data and many entries in bowling
data. So you want a 1 to many relationship between Player and Batting and
also between Player and Bowling.

TO get all three bits of info on one form you need to set up subforms for
the "many" side of the relationship. So one subform showing Batting data,
and one subform showing Bowling data. Then construct a main form for the
player data and add the subforms to this main form. Access will
automagically link the two forms via playerID or similar.

Take a look at the sample database "Northwind" - go to the orders form and
you will see a form with information about Suppliers (equivalent to Players)
and a subform with Orders (quivalent to Batting or Bowling stats).

Hope this helps,
John

 

[Rick B]

You would link the tables. You should NOT include a full name in each
table, just a "player number". The name would live in Table1.

To include all on one form, just base your form on a query that includes all
the tables.

Post back if you need more details.

 

[Guest]

Having a 'name' as a primary key is always tricky. Issues like hyphens,
spaces, middle names, change in name complicate things in database.

I suggest assigning each player an id "PlayerID". This can be the Primary
Key in each table and the issue's above will be much less of a problem.
You're right to assume a one-to-one relationship.

Make the PlayerID field a primary key in each table.
In the relationship window, define the relationship
Design a query with all three tables (the relationship you've defined in the
relationship window sould automatically appear) and include every field you
wish in this query.
Create a form based on the query above

HTH
Table 1 - Personal Info
Table 2 - Batting Info
Table 3 - Bowling Info