Access-starter needs help with a sports database

[radam]

Hi there!
During the last years, I made a bunch of skijumping-related "statistics" in LibreOffice Calc, some smaller ones, but also one that has grown so big that I needed to switch to Excel as I had reached LibreOffice's column limit. As this one is getting quite confusing and uncomfortable to work with, I decided to try to re-do it in Access and quickly ran into problems, which is why I'm here.

This is a tiny part of that table, the current version of the full table has about 1200 rows with skijumpers and about 1300 columns with competitions.

I coloured parts of the table so that I can explain better what I did, what I think I have to do to set this up as an Access database and to explain what I don't understand.

So, the blue area contains every ski jumper that ever scored a world cup point with the information if they're active or retired (column C), Name, Nation and year of birth.
I guess this is going to be my first table in Access, plus a unique ID for each jumper as primary key, am I right?

The yellow area displays every world cup competition with the place (row 4), size of the hill (row 5), competition date and a unique competition number.
These things will probably be my second Access table, with the competition number being the primary key. Right?

The red area contains the FIS world cup points that a jumper has scored in a specific competition (100= first place, 80=2nd, 60=3rd etc).

The green area counts the total number of world cup points in all competitions, and also counts all Top30-finishes (=non-empty cells in the red area), Top10 (=cell value 26 or more), victories etc.
As far as I understand, these informations won't be part of the Access tables, and will only shown in queries using calculated fields. Right?

My problem is, how and where can I put the informations of the red area?
I think I need a table that contains the jumper ID and the competition ID, but I have no idea where to put what.
The only thing that makes sense to me would be something like this:

A table with a resultID or something like that as a primary key, then jumperID and competitionID (as foreign keys, if I don't mix up the terms) and a number field for the scored points.
Could this work like that, or do you know a better way to solve this problem?
Like this, the result table would be pretty long, the current table has about 54.000 results listed, so there would be at least 54.000 "rows" in this table.
Is this a problem? Does Access have a limit for "rows" in a single table?

Edit: Can I actually use names as IDs, e.g. "jannaho" as jumperID for Janne Ahonen, as long as this is really a unique identifier? I think it would make entering new data easier for me.
Also, for the same reason, could the competitionID be a date?


Thank you in advance for your help!

Greetings from Erfurt,
Rada

 

[bob fitz]

Although Spreadsheets and Access tables may look similar they are organized differently.
I would recommend that you read up on database "Normalization".
There will be thousands of posts here on the subject and even more from a Google search.
BTW welcome to the forum

 

[Minty]

I think you have made an excellent first go at defining your tables set up.

I would avoid using a Name for a unique index, as sooner or later a second Hans or Peter will arrive and then you need to work out who is who.
Also if someone changes their name it's easier to just update their surname (or first name) field without it messing up the rest of the relationships.

Your thoughts about the red section look very good to me - it is sometimes known as a Junction table where many keys are stored, along with some other data in your case the scores, They look very alien to a casual user as in your case they have no obvious data in them, just a bunch of foreign keys, but that is the correct way to do it.

A data entry form that has a combo box with your competitor names will easily avoid the problem of not knowing which FK you need for the name or competition etc.

 

[radam]

Thank you a lot for your replies!

A data entry form that has a combo box with your competitor names will easily avoid the problem of not knowing which FK you need for the name or competition etc.

Ah, I din't know that was even possible. I knew that a combo box can have a list of names etc, but I din't know that it is possible to have the names displayed and then actually the ID stored.
For this I would use the BoundColumn property, is this right?
If it works like that, then I think I'd know how to do it if I had one name field like "first name last name", but I'd like to use seperate fields for first name and last name.
Is there mabe a way to have them as seperate fields, but display them together in one combo box?

 

[Minty]

Yes absolutely, you would base the combo on a query something like

SELECT CompetitorID, [FirstName] & " " & [LastName] as FullName
FROM tblCompetitors
Order By FirstName ASC

Would give you what you needed.
You would hide the first column by setting the columns widths to 0;5 in the combo properties.

 

[Steve R.]

Hi there!
Edit: Can I actually use names as IDs, e.g. "jannaho" as jumperID for Janne Ahonen, as long as this is really a unique identifier? I think it would make entering new data easier for me.
Also, for the same reason, could the competitionID be a date?

Although Spreadsheets and Access tables may look similar they are organized differently.
I would recommend that you read up on database "Normalization".

I would avoid using a Name for a unique index, as sooner or later a second Hans or Peter will arrive and then you need to work out who is who.
Also if someone changes their name it's easier to just update their surname (or first name) field without it messing up the rest of the relationships.

My comments are a follow-up to the prior comments. So, I hope that I am not jumping to far ahead. I favor an auto-generated "hidden" primary key as part of "normalization". The reason for a "hidden" primary key is to keep it "protected" from changing. That resolves problems associate with data that can be subject to revision, such as a persons name or even the date of a competition.

The following is a free pdf book that is available online. As a word of warning, this book is written for Linux Base, another type of database, similar to Access. The advice concerning how to design a database and normalization provided in the book can be applied to Access. For a starter, just look-over the first two chapters to focus on designing a database and for normalization. Base Tutorial:From Newbie to Advocate in a one, two... three!