LEAGUE TABLE IN ACCESS 1

[GRUTES]

HOW WOULD I CREATE A LEAGUE TABLE IN MICROSOFT ACCESS? I NEED FIELD NAMES:
PLAYER NAME
FIXTURES PLAYED
FIXTURES WON
FIXTURES DRAWN
FIXTURES LOST
GAMES FOR
GAMES AGAINST
POINTS

I WOULD NEED THE PLAYERS NAMES,FIXTURES PLAYED, WON, DRAWN, LOST,FOR AND AGAINST TO BE COLLECTED FROM ANOTHER TABLE, THEREFORE UPDATING THE INFORMATION IN THE LEAGUE TABLE. THE POINTS COLUMN WOULD NEED TO BE CALCULATED USING SOME SORT OF FORMULA:- 1 POINT FOR A LOSS, 2 FOR A DRAW AND 3 FOR A WIN.

PLEASE HELP!

 

[UncleJack]

See the help topic "Creating Tables."

You should also carefully consider whether you should maintain running totals in this table or calculate them when you need them displayed. I prefer to calculate values whenever possible rather than storing them because I don't want to worry about totals getting out of sync with data.

You should also perhaps consult a textbook or paper on database normalization before starting your project. Time spent planning the data structure is well invested if you can avoid flaws that later make you scrap a design and start over.


Uncle Jack

 

[turtlemaster]

Uncle Jack is right. I would recommend the use of many tables in this instance.

A Players table:

Player ID (PK)
Player Name

A Fixtures table:

Fixture ID {PK}
Description

League Table:

Player ID {PK}(FK)
Fixture ID (PK)(FK)
Play Date
Score


They way you described the solution you want it sound more like an excel spread sheet. With this design you could extract all of the information the you described but you would be required to enter the information for every game.

Example: to find out wins for a specific player

select * from League where playerID="[your player id]" and Score=3


This probably isn't even close to the best solution for your database but you need to look at all of your data elements and define your relationships carefully. This is more important than any code you may write.