Creating a database to track sports events and children

[swellybro]

Hi,

I am a teacher and I would like to create a database in Access 2016 to hold information about children and sporting events. I'm just unsure of how to create the database design.

I'd like the children's table to contain information about the children e.g. name, year group, house colour, male/female etc...

Every year we hold different sporting events like archery, swimming etc... we send letters out to the children and sometimes we are over subscribed. I'd like to track who has applied to an event and who has been successful. So I would like to be able to store the event name, date and whether a child has applied and were successful/not successful. Would this be held in a table called events?

Where could i store the information on whether a child has applied to the event or not? Do I need an extra table?

I'm not 100% sure how I would link the table(s). I'm guessing I could have a unique number for each child and an event number. How do I store whether they have applied for an event?

At the end of the year I would like to run a report that told me how many children took part in an event. How many children were successful in applying for an event.

Any advice would be very gratefully received.

Thank you

Richard

 

[plog]

The process of setting up tables for a database is called normalization (https://en.wikipedia.org/wiki/Database_normalization). Give that link a read, work through a few tutorials, then give it a shot with your data.

My advice is to open up Excel and create one massive table of fake data. Include all possible cases, all fields you will want, etc. Then using that data you apply the normalization process and build the tables you will need in Access.

To simplify your objective as keeping track of what student participated in which event, you would need 3 tables:

tblStudents = this will hold all the information for students. 1 record, 1 student.
tblEvents = this will hold all the information for an event. 1 record, 1 event.
tblParticipation = this will hold the participation of 1 student in 1 event.

You will assign an autonumber primary key to each table (StudentID, EventID, ParticipationID). TblParticipation will use the StudentId and EventID to determine which student participated in which event.

Again, read up on normalization, give it a shot, then in Access set up the Relationship tool and post a screenshot back here and we can help.

 

[isladogs]

Hi

I am an ex-teacher who has been creating databases for schools for many years.
Most are commercial products and therefore aren't free.
I'm not trying to sell something to you here.
However you may find it helpful to download demo versions of one or more programs from my website.
http://www.mendipdatasystems.co.uk/demo-versions/4585524220
You may find these provide useful ideas to get you started.
Note that because they are demos, you can't add your own student data or view the code.

Let me know if I can be of any assistance with specific parts of your project

If you want to use dummy data whilst testing your own database as plog suggested, I recommend trying the Mockaroo website https://www.mockaroo.com/.
Very useful for any project before it goes live

Good luck